Whew, the title is really a mouthful. Once more I've found myself being unsure of just how to request this and so i uses a good example. Let us say you are making a game title which has products. Products have effects, bonuses and needs.

So each record within the products table has multiple child records within the effects table, the bonuses table and also the needs table.

And you need to choose, let us say, the 100 newest products, including all their effects, bonuses and needs to show in the overall game.

What's the most enhanced way to do this. Will it be completed in one query? And it is that even practical? Thanks.

It may be accomplished in a single query, but it might be quite large there will be a large amount of doubling up. The only real really ideal time doing something similar to this in a single totally if there's a "has one" relationship. i.e. A product has one effect, which may be completed with an easy join, as well as an item only returns one show.

Have a simple situation of the items you've given. You've 2 products, each with 3 effects having a many to a lot of relationship.

SELECT item.*, effect.* 
FROM item 
JOIN item_effect ON item.id = item_effect.item_id 
JOIN effect ON effect.id = item_effect.effect_id

The return might be:

item1   effect1
item1   effect2
item1   effect3
item2   effect2
item2   effect3
item2   effect4

Then you would need to loop through and group all the products together again. With associations to needs and modifiers the query could be getting bigger, but nonetheless fairly organized.

You could utilize some form of ORM (Object Relational Mapping). That could build your code more readable, e.g. Using syntax from Kohana's ORM.

$items = ORM::factory('item')->find_all();
foreach($items as $item) {
    $effects = $item->effects->find_all();
    $bonuses = $item->bonuses->find_all();
    $requirements = $item->requirement->find_all();
}

As well as the 100 item example you recommended that'll be 301 queries.

If you're exhibiting this on the web site, then pagination (showing 1-20 of 100) will lower time.

The technique you utilize really is dependent in your situation. Points to consider:

  • How frequently will this be utilized
  • Do what is required see 100 products at the same time
  • Do they have to see all of the associations at one (click a product to see its effects, etc.)

You ought to be able todo something similar to this...

 SELECT  `users`.`nickname` ,  `events`.`nickname` 
 FROM  `users` ,  `events` 
 WHERE  `events`.`aid` =  `users`.`aid` 
 GROUP BY  `events`.`nickname` 

To explain, occasions.aid may be the unique ID from the user. Then when I fetch each one of these records and group them by event, I recieve a listing of unique event nicknames and also the customers that produced them. enter image description here