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

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

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);

To get the left join query do the following

$tutorials = Tutorial::leftJoin('tutorial_tags', 'tutorials.id', '=', 'tutorial_tags.tutorial_id')
                 ->where(function($query){
                      if(isset($_GET['tag']) {
                          $query->where('tutorial_tags.title' , $_GET['tag'];
                      }
                 });

This will give you the following outputs: Case 1: If tag parameter was provided

SELECT * FROM tutorials LEFT JOIN tutorial_tags ON tutorials.id = tutorial_tags.tutorial_id WHERE tutorial_tags.title = 'ABC';

Case 2: When no tag parameter has been provided

SELECT * FROM tutorials LEFT JOIN tutorial_tags ON tutorials.id = tutorial_tags.tutorial_id;