Filter results based on foreign table

Question

Suppose I have 4 tables: collections, venues, locations and cities.

A Collection belongs to many Venues and a Venue belong to many Collections.

There is a one to one relationship between a Venue and a Location (through id field)

A Location Belongs to a City and a City has many Locations.

What I want to achieve is this scenario:

if I have a record's id on cities table, I want to get all collections and venues in each collection associated with id on cities table.

How would I achieve this? I tried to be as specific as possible,but if something is still vague mention.
I'm using Laravel. so if you provide in Eloquent I would be grateful but SQL query is ok too.

Answer 1

Synchronize the keys of the table you gave reference

SELECT co.anycolumn, v.anycolumn FROM collections co, venues v, cities ci, locations lo WHERE co.id = v.refid AND ci.refid = lo.refid AND lo = 'WantedLoc';

Simple Logic: SELECT --> desired column values FROM --> referenced the tables WHERE --> condition

Answer 2

You could write a standard SQL query like below however I believe more information regarding your table structure is required to give a full answer to your question, especially the many to many join between collections and venues.

Select ci.Id, ci.Header, co.Header,* from Cities ci
inner join Locations l on l.id = ci.LocationId 
inner join Venues v on v.id = l.VenueId 
inner join Collections co on co.Id = v.CollectionId -- this is going to be a problem

A Collection belongs to many Venues and a Venue belong to many Collections

Many to many is not a good database relationship design pattern I would recommend a link table to break these into many to one connections, this would also simplify the query required if possible.

^Edit Laravel/Eloquent might handle this better but in my experience just because you can do something doesn't mean you should, see SQL JOIN many-to-many for a nice way to handle many to many in a sql query or Many to Many join Laravel Eloquent might help.