I am working on a tool at the moment which is built on top of Codebase. I am going to blog later in more detail about this tool. For now, I want to touch on a specific Laravel issue I had yesterday.

We also use TeamWeek to plan on a more top level. TeamWeek enables us to plan on a weekly and monthly basis who will be working on what and when.

One thing that’s been annoying me for a while is having to duplicate information and use two tools. So we have been pulling our own TeamWeek view inside our internal application.

We have users and tickets. What I wanted to do was pull all users along with their tickets which match a given condition. I did not want all users and I did not want all tickets. The condition was that the started_at and deadline_at columns must not be null. I also only wanted tickets that either start or end within a given time frame.

What I eventually came up with was a query that searched for all users which had tickets that matched this condition. Then I added looked up only the tickets that matched the condition.

I am not confident this is the best Eloquent solution, but it works.

$users = User::whereHas(
    'tickets', function ($query) use ($from, $to) {
    $query->where(function ($query) use ($from, $to) {
        $query->whereNotNull('started_at')->whereNotNull('deadline_at')->whereBetween('started_at', [$from->format('Y-m-d'), $to->format('Y-m-d')]);
    })->orWhere(function ($query) use ($from, $to) {
        $query->whereNotNull('started_at')->whereNotNull('deadline_at')->whereBetween('deadline_at', [$from->format('Y-m-d'), $to->format('Y-m-d')]);
    });
}
)->with([
    'tickets' => function ($query) use ($from, $to) {
        $query->where(function ($query) use ($from, $to) {
            $query->whereNotNull('started_at')->whereNotNull('deadline_at')->whereBetween('started_at', [$from->format('Y-m-d'), $to->format('Y-m-d')]);
        })->orWhere(function ($query) use ($from, $to) {
            $query->whereNotNull('started_at')->whereNotNull('deadline_at')->whereBetween('deadline_at', [$from->format('Y-m-d'), $to->format('Y-m-d')]);
        });
    }
])->get();

Comments welcome!