[eluser]sqwk[/eluser]
I need some advice for a many to many relationship. I am using Mysql with the ActiveRecord class.
I have three tables: (Slightly simplified)
Code:
table buyers
|| buyer_id || buyer_field1 || buyer_field_2 ||
|| 1 || Lorem || Lorem ||
|| 2 || Bla || Bla ||
table buyers_link_places
|| buyer_id || place_id ||
|| 1 || 2 ||
|| 1 || 5 ||
|| 2 || 2 ||
|| 3 || 6 ||
table places
|| place_id || place_field1 || place_field2 ||
|| 2 || bla || foo ||
|| 5 || lorem || ding ||
|| 6 || dhd || dhdgh ||
How would I design a fast query that allows me to get entries from "buyers" but also get any field passed to the active-record-select from places. This would be simple if there is only one row in places that is linked to buyers, but usually it is at least three.
I have already tried using group_concat, sticking the column name in front of the data, separated by a colon, with multiple fields separated by a comma and multiple rows separated by a semi-colon. This allows me to explode everything into a nested array later, but it is a LOT of explodes.
The alternative is to only join the link-table, get just the place-ids as a comma separated list (group_concat again) and effectively run another query to get any data from table_places. The only problem here is that I will need n+1 queries for n buyers.
The third alternative is built on the second, but instead of running n+1 queries, I would combine all the different place_ids from all rows to then run a single query to get all the affiliated places. The only downside here is that I effectively need to use a lot of whiles/foreachs later…
So, overall, which method is fastest? And which one is more manageable? Does anyone have any experience with this sort of data structure?