SQL - has many through many

Poprzednio de facto zrobiliśmy relację has many through many i choć może to dostatecznie nie wybrzmiało, wykonaliśmy konkretną robotę, którą niełatwo jest odtworzyć w czystym SQL. Teraz to zrobimy, poznając lepiej różne relacje Laravela na poziomie tabel.

Ok, przypomnijmy sobie komendę:

Artisan::command('m-tags {id}', function (int $id) {

    $ids_r = DB::table('reviews')
    ->select('id')
    ->where('movie_id', $id)
    ->pluck('id')
    ->toArray(); 

    $ids_t = DB::table('review_tag')
    ->select('tag_id')
    ->whereIn('review_id', $ids_r)
    ->pluck('tag_id')
    ->toArray();

    $tagnames = DB::table('tags')
    ->select('tagname')
    ->whereIn('id', $ids_t)
    ->pluck('tagname')
    ->toArray();

    $movie = Movie::findOrFail($id);

    $tagnamesAsString = implode(", ", $tagnames);
    
    $this->comment("Movie title: {$movie->title}");
    $this->comment("Tags: {$tagnamesAsString}");

    
    
});

Rzućmy okiem na tabele, przypomnę je:

movies, zero kluczy obcych, model hasMany względem reviews
reviews, klucz obcy movie_id, belongsTo względem movies, belongsToMany względem tags
tags, zero kluczy obcych, belongsToMany względem reviews
review_tag – piwotalna, każdy rekord ma id (nieistotne dla nas) oraz klucze obce review_id i tag_id łączące reviews i tags w relację many to many

Ok, najpierw coś prostego, wszystkie tagi:

SELECT t.tagname FROM `tags` as t;

Teraz wszystkie pary w tabeli piwotalnej:

SELECT pivot.tag_id, pivot.review_id FROM `review_tag` as pivot;

Teraz wszystkie pary tag-recenzja, pod warunkiem, że recenzja wskazuje na film o id 1:

SELECT 
pivot.tag_id, 
pivot.review_id 
FROM `review_tag` as pivot 
where 
pivot.review_id in 
(
    select r.id 
    from `reviews` as r 
    where r.movie_id = 1
    ) ;

Teraz do nas dotrze, że review_id nie jest potrzebne w result secie, tylko w warunku:


SELECT 
pivot.tag_id
FROM `review_tag` as pivot 
where 
pivot.review_id in 
(
    select r.id 
    from `reviews` as r 
    where r.movie_id = 1
    ) ;

Nie chcemy, żeby tag_id się powtarzało, jeden tag to jest jeden tag:

SELECT DISTINCT 
pivot.tag_id 
FROM `review_tag` as pivot 
where pivot.review_id in 
( 
    select r.id 
    from `reviews` as r 
    where r.movie_id = 2 
);

To teraz unikatowe nazwy tagów dla takiego filmu, którego recenzje… Po prostu to zróbmy i przeanalizujmy:

SELECT 
t.tagname 
from `tags` as t 
where t.id in 
( 
    SELECT DISTINCT 
    pivot.tag_id 
    FROM `review_tag` as pivot 
    where pivot.review_id in 
    ( 
        select r.id 
        from `reviews` as r 
        where r.movie_id = 1 
    ) 
);

Ok, teraz zaciągamy dwie tabelki, jedna z tagami, druga z informacjami o filmie:

SELECT 
t.tagname 
from `tags` as t 
where t.id in 
( 
    SELECT DISTINCT 
    pivot.tag_id 
    FROM `review_tag` as pivot 
    where pivot.review_id in 
    ( 
        select r.id 
        from `reviews` as r 
        where r.movie_id = 1 
    ) 
);

SELECT * from `movies` WHERE id = 1;

Troszkę oszukujemy, są dwa result sety. Jeżeli mimo wszystko ogarniamy, co tu się dzieje, to już jest dobrze.

To teraz tak – movie o id 2 i tagi z recenzji dla tego filmu, o id 2:

SELECT 
t.tagname 
from `tags` as t 
where t.id in 
( 
    SELECT DISTINCT 
    pivot.tag_id 
    FROM `review_tag` as pivot 
    where pivot.review_id in 
    ( 
        select r.id 
        from `reviews` as r 
        where r.movie_id = 2 
    ) 
);

SELECT * from `movies` WHERE id = 2;

Wyniki porównujemy z naszymi komendami. Ok, teraz pora zrobić jeden result set.

SELECT 
m.*, 
( 
    SELECT GROUP_CONCAT(t.tagname) 
    from `tags` as t 
    where t.id in 
    ( 
        SELECT DISTINCT 
        pivot.tag_id 
        FROM `review_tag` as pivot 
        where pivot.review_id in 
        ( 
            select r.id 
            from `reviews` as r 
            where r.movie_id = 2 
        ) 
    ) 
    GROUP BY 'all'
) as 'tags'  
from `movies` as m 
where m.id = 2;

PS. Chciałem to etapowo wprowadzić, najpierw implode samych tagów, ale blog odmówił posłuszeństwa (JSON error). Można niby gutenberga wyłączyć i w starym dodawać bloki, ale aż tak się dla was poświęcać nie będę.

SQL – has many through many