vitorf7
3 years ago

Search on a table with multiple pivot joins

Posted 3 years ago by vitorf7

Hi everyone,

I am doing a project in which I have the following tables:

media_file_downloads
| id | user_id | media_file_id |
|----|---------|---------------|
| 39 | 41      | 791           |
| 38 | 41      | 792           |
| 37 | 41      | 789           |
| 36 | 41      | 791           |

property_brochures
| id | property_id | brochure_id |
|----|-------------|-------------|
| 1  | 50          | 792         |
| 2  | 50          | 791         |

property_documents
| id | property_id | file_id |
|----|-------------|---------|
| 46 | 50          | 790     |
| 45 | 50          | 789     |

properties
| id | title          | slug           | address_line_1 | ... | city       |
|----|----------------|----------------|----------------|-----|------------|
| 50 | Property Title | property-title | Address Line 1 | ... | Manchester |

I have been asked to create a search in the Download Reports section to search by property City and then get all the downloads from properties with that city. I have a MediaFileDownload Model which is the model of a pivot table between a user and media file. When I try to get the media file downloads from just one join everything works but I cannot do it with two joins. Both property_documents and property_brochures (they are different and have other pivot fields hence the need of two different tables) have to join on the media_file_downloads.media_file_id in the media_file_downloads table but I cannot seem to make it work with the properties.city like "%Manchester%".

Here is what I have and works:


    MediaFileDownload::select('media_file_downloads.*')
        ->join('property_brochures', 'property_brochures.brochure_id', '=', 'media_file_downloads.media_file_id')
        ->join('properties as p1', 'p1.id', '=', 'property_brochures.property_id')
        ->where('p1.city', 'like', '%'.$propertyCity.'%');

Thanks for your help.

Please sign in or create an account to participate in this conversation.