[SOLVED] Laravel Eloquent: how to implement INNER JOIN with condition

Laravel Eloquent: how to implement INNER JOIN with condition

I need to get INNER JOIN with condition by Laravel Eloquent (no Database Query!).

with with condition works as LEFT JOIN:

Project::with(['tasks' => condition_for_tasks])

Here I got ALL project records with tasks field equal to null or real task records. Typical LEFT JOIN behaviour.

So, when I try to use where at top level, it's working just for main table (projects):

Project::with(['tasks' => condition_for_tasks])->where(condition_for_projects) - works well, but it's not my case.
Project::with(['tasks' => condition_for_tasks])->where(condition_for_tasks) - it's what I want, but ends with error - tasks table not present in from clause.

P.S. Yes, I can use intermediate array like results. Then iterate over projects and related tasks. Analyze tasks condition for each task record and then include (or not) iterated projects record into intermediate array. But I'd like to find more efficient Eloquent-way for this.

Solution

I'm adding another answer using the with function.

Since I'm not sure what your columns are, I'm going to filter using the IDs. Hopefully it works with your expected output.

$projects = Project::with(['tasks' => function ($q) {
        $q->where('id', 10); // get tasks with id = 10.
    }])
    ->whereHas('tasks', function ($q) { // get projects that has tasks with id = 10.
        $q->where('id', 10);
    })
    ->get();