CODEWITHSUNDEEP
mysqllaravellaravel-5eloquentrelational-database
Michał
Michał

Reputation: 908

Retrieving rows from a third table that match two pivot tables

I have 5 tables in my Laravel app. 3 main tables are books, places, and tags. The books table has a pivot table to relate it to tags: book_tags. The places table also has a pivot table: place_tags. Now, for each place, I'd like to get the books based on tags that are both in place_tags and book_tags.

books
    book_tags
places
    place_tags
tags

Here's an SQLFiddle

I've looked through the laravel docs and the closest this seems to come to is a many to many polymorphic relationship, but with that, there'd be only one pivot table with a tag_type, which is not what I have.

How would I build this query to retrieve the books for each place based on tags in both pivot tables? Answers in MySQL are cool too, I just don't know how this would look like as a query...

Upvotes: 0

Views: 240

Answers (2)

apokryfos
apokryfos

Reputation: 40653

Here's what I'm reading.

Assume a tags model:

class Tag {
      public function books() { 
          $this->belongsToMany(Book::class);
      }
      public function places() {
          $this->belongsToMany(Place::class);
      }
}

Then if you need to get all books which have a common tag with a specific place you can do:

$tagsForPlace = Tag::whereHas("places", function ($query) { $query->where("id", $placeId; })->with("books")->get(); //$PlaceId is the id you are interested in.

Then the books you need are just:

$booksWithTagsForPlace = $tagsForPlace->map(function ($tag) { 
      return $tag->books; 
})->unique("id"); //Unique to filter out duplicate books if any

I may be wrong in my understanding of the question though.

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

How would I build this query to retrieve the books for each place based on tags in both pivot tables? Answers in MySQL are cool too, I just don't know how this would look like as a query...

Query

SELECT 
   books.*
 , places.*
FROM 
 book_tags
INNER JOIN 
 place_tags
ON
 book_tags.tag_id = place_tags.Tag_id

INNER JOIN 
 books
ON
 book_tags.book_id = books.id

INNER JOIN 
 places
ON
 place_tags.place_id = places.id

Result

| id |       title | id | name |
|----|-------------|----|------|
|  1 | Science Boo |  1 | Shop |

see demo http://sqlfiddle.com/#!9/f2674d/13

Or use column aliases like

Query

SELECT 
   books.id AS book_id
 , books.title AS book_title
 , places.id AS place_id 
 , places.name AS place_name
FROM 
 book_tags
INNER JOIN 
 place_tags
ON
 book_tags.tag_id = place_tags.Tag_id

INNER JOIN 
 books
ON
 book_tags.book_id = books.id

INNER JOIN 
 places
ON
 place_tags.place_id = places.id

Result

| book_id |  book_title | place_id | place_name |
|---------|-------------|----------|------------|
|       1 | Science Boo |        1 |       Shop |

see demo http://sqlfiddle.com/#!9/f2674d/15

@RaymondNijland what's unclear? I'd like to get all the books for a given place :P Data added to sql :) sqlfiddle.com/#!9/f2674d/1

Then you need to add 

WHERE
  places.name = 'name'

to the queries above

Upvotes: 1

Related Questions