Many to many relationship

[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?

[eluser]Jelmer[/eluser]
You could retrieve it after the double join and just do some post processing like this:

Code:

$buyers = array();
foreach ($result as $r)
{
    if (empty($buyers[$r->buyer_id]))
    {
        $buyers[$r->buyer_id]->buyer_field1 = $r->buyer_field1;
        $buyers[$r->buyer_id]->buyer_field2 = $r->buyer_field2;
    }
    $place->place_field1 = $r->place_field1;
    $place->place_field2 = $r->place_field2;
    $buyers[$r->buyer_id]->places[$r->place_id] = $place;
}

Also you could take a look at how the various ORM solutions handle this. (like RapidDataMapper, DataMapper Overzealous Edition & Doctrine)

[eluser]sqwk[/eluser]
The post-processing is the simple part. In your example, where would I get the $r->place_field1 from? If I use the double join I can get them by just setting the name in the select part of the query, however, this does not work if there are multiple rows linked to one buyer.

[eluser]Jelmer[/eluser]
It's not as simple as you think apprently, or maybe it's just not as complicated as you think

You get the double joined result (which I assume you know how to get through AR) which will give you a row for each possible combination of buyer and place as you said. And thus will get all the columnnames as properties (like place_field1).

Now take another look at the post-processing code I wrote: it checks whether the buyer info already exists in the array and only adds another buyer to the array if the ID doesn't exist as a key yet. Then it takes the place from the row in the $place variable and adds the place to a property of the new buyer object called "places".

This way you eliminate the doubles from the buyers and get place objects in an array with each buyer. Read the code I wrote again and think carefully about what it does.

EDIT: And you still might want to consider using an ORM instead of inventing the wheel again.

[eluser]sqwk[/eluser]
Hey, that actually makes sense… Thanks for the explanation, I completely missed the if(empty) the first time round and got stuck into trying to let the query to the grouping.

If I were to use an ORM, do you have any recommendations? What are the differences?

[eluser]Jelmer[/eluser]
I'm using RapidDataMapper because I really like the implementation.
Pro: It gives me clean objects from the database (no methods or variables that aren't from the DB) and uses other objects to perform the functionality. Also the way that objects are described and then compiled is something I like.
Con: It's not entirely mature. The most essential functionality works great and reliable, but there are some parts where you can see the intent to expand but hasn't yet been built. But it is reliable enough to use in a production environment.

I have only used DataMapper OverZealous to try it out a little bit. While I like that implementation less, it is a very mature codebase and has an huge userbase.

Doctrine I haven't tried. I took a look into its documentation and it just didn't look that great to me. Having said that, it too has a boatload of users that are very content with it.

In the end all are great and it depends more on personal preference. Take a look at the documentation of each and see which you like best.

[eluser]sqwk[/eluser]
I had a look at all the ORMs, but I am still not sure whether it makes sense to use one for this project. I am missing fulltext search and know that I would also take considerable time to get to know the framework…

Therefore, I think I'll stick with plain old activerecord for now:

I got your code to work but since I the post-processing is effectively skipping some rows, I never reach the limit that I pass to the query. (Pagination becomes fairly useless.) Is there a way to avoid that?

[eluser]Jelmer[/eluser]

I got your code to work but since I the post-processing is effectively skipping some rows, I never reach the limit that I pass to the query. (Pagination becomes fairly useless.) Is there a way to avoid that?

I haven't been able to think of a way. The problem is that you never know how many rows it will return because of the join. Which, indeed, makes it impossible to use limit. If anyone knows a way to solve this I'll be interested as well...