laravel ORM关系：left在右表中加入类似查询和可选条件

Question

i have two table, tutorial:id,title and tutorial_tags:id,tutorial_id,title

the relation in tutorial model is defined like this :

function TutorialTag(){
        return $this->hasMany('App\TutorialTag');
    }

i want to left join tutorials with tutorial_tags , like (please ignore syntax errors):

select tutorials.* , tutorial_tags.* from `tutorials` left Join 
`tuotrial_tags` ON tutorials.id = tutorial_tags.tutorial_id 

but i want to be able to use Conditions on tutorial_tags in case user want to search a particular tags:

select tutorials.* , tutorial_tags.* from `tutorials` left Join 
`tuotrial_tags` ON tutorials.id = tutorial_tags.tutorial_id 
where tutorial_tags.title = 'ABC'

if i use whereHas like this :

    $tutorials = Tutorial::whereHas('TutorialTag',function ($query){
        if(isset($_GET['tag']))
            $query->where('title',$_GET['tag']);
    })->get();

i dont get tutorials that are without any tag, basically it works like inner Join.

and if i use with :

    $tutorials = Tutorial::with(['TutorialTag'=>function($query){
        if(isset($_GET['tag']))
            $query->where('title',$_GET['tag']);
    }])->get();

then ill get two separate queries with no effect on eachother, basically the where condition on tutorial_tags has no effect on tutorials and i get all the tutorials even the ones without sreached tag, here is the query log :

Array
(
    [0] => Array
        (
            [query] => select * from `tutorials`
            [bindings] => Array
                (
                )

            [time] => 0
        )

    [1] => Array
        (
            [query] => select * from `tutorial_tags` where `tutorial_tags`.`tutorial_id` in (?, ?, ?) and `title` = ?
            [bindings] => Array
                (
                    [0] => 1
                    [1] => 2
                    [2] => 3
                    [3] => ABC
                )

            [time] => 2
        )

)

how can i get left Join like query with optional condition on the right table

Answer 1

This is not an efficient sql query, but you can solve the issue with laravel collections and an extra query.

Grab all the intersections:

$tutorials1 = Tutorial::whereHas('TutorialTag',function ($query){
    if(isset($_GET['tag']))
        $query->where('title',$_GET['tag']);
})->get();

Grab the rest:

$tutorials2 = Tutorial::doesntHave('TutorialTag')->get();

Merge both collections:

$tutorials = $tutorials1->merge($tutorials2);