Eloquent查询搜索两个表

Question

I am struggling with the following query, using Eloquent in Laravel 5.6.

I need to return all issues that have a tag_id of 5 assigned to them, where the project_id and item_id from the issues table matches the project_id and issue_id from my pivot table.

issues table:

issues_tags pivot table:

I have tried the following code, but it returns all issues from the issue table, however the expectation is 3 results.

Expected results

The results returned from the issues table should be ID 1, 4 and 5.

$issues = Issue::join('issues_tags', 'issues_tags.project_id', 'issues_tags.issue_id')->where('issues_tags.tag_id', 5)->select('issues.*')->get();

Answer 1

You need to specify the issues table instead of issues_tags on the join. A left join will also help reduce the results. Since you're joining on two different keys, you have to use a closure.

$issues = Issue::leftJoin('issues_tags', function($join) {
    $join->on('issues.project_id', '=', 'issues_tags.project_id');
    $join->on('issues.item_id', '=', 'issues_tags.issue_id');
})
->where('issues_tags.tag_id', 5)->select('issues.*')->get();

If the table is really supposed to match on project_id->project_id and issues.id -> issues_tags.issues_id, you can modify the 2nd join clause.