1

Whew, the title is a mouthful. Once again I find myself not knowing exactly how to ask this question so I will use an example. Let's say you're making a game that has items. Items have effects, bonuses and requirements.

So each record in the items table has multiple child records in the effects table, the bonuses table and the requirements table.

And you want to select, let's say, the 100 most recent items, including all of their effects, bonuses and requirements to display in the game.

What is the most optimized way to accomplish this. Can it be done in one query? And is that even practical? Thanks.

Ryan
  • 11
  • 1
  • Why you don't want to use JOINS? Are those tables too big? `SELECT * FROM items JOIN effects ON items.ID JOIN bonuses ON items.ID JOIN requirements ON items.ID ORDER BY items.date DESC LIMIT 100` – criticus Feb 16 '11 at 04:33
  • I need several effects, bonuses and reqs PER item. My understanding is that this will return 1 effect, 1 bonus and 1 req for every item. Am I incorrect? – Ryan Feb 16 '11 at 04:42
  • it will return all effects, bonuses, requirements for the selected items. And if you have items with no effects, bonuses or requirements you should use LEFT JOIN – criticus Feb 16 '11 at 04:51
  • how many fields in each table, and is there any commonality between effects and bonuses (ie: field types, lengths, etc)? If similar, perhaps `UNIONING` the child tables, or maybe look at MySQL's `GROUP_CONCAT()` and then `implode` the joined fields in PHP. – bob-the-destroyer Feb 16 '11 at 07:19

2 Answers2

1

It could be achieved in one query, but it would be quite large and there would be a lot of doubling up. The only really ideal time doing something like this in one query is if there is a "has one" relationship. i.e. An item has one effect, which can be done with a simple join, and an item only returns one show.

Take a simple case of what you've given. You have 2 items, each with 3 effects with a many to many 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 could be:

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

Then you would have to loop through and group all of the items back together. With relationships to requirements and modifiers the query would be getting larger, but still fairly organized.

You could use some type of ORM (Object Relational Mapping). Which could make 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();
}

But for the 100 item example you suggested that will be 301 queries.

If you are displaying this on a web page, then pagination (showing 1-20 of 100) will lower that number.

The method you use really depends on your situation. Things to consider:

  • How often will this be used
  • Do they really need to see 100 items at once
  • Do they need to see all the relationships at one (click an item to view its effects, etc.)
Jacob
  • 8,278
  • 1
  • 23
  • 29
  • Thanks Jacob. The method with the foreach loop is actually the solution im using right now, but as is certainly obvious, it's a killer on my DB server. You've pointed out some interesting things to consider that I need to think about. Unfortunately I use this data a lot, at least once every time someone loads the game. It may be time to implement some type of caching... – Ryan Feb 16 '11 at 03:50
  • I would really consider if they need to know all that information on load. Maybe they only need to know the information about the items they are using? Otherwise caching is a really good idea. (Hard to say without knowing the game). – Jacob Feb 16 '11 at 03:56
  • Because of how the items are viewed and used, I think it would hurt the user experience if they had to load the additional item data on an as needed basis. But, I certainly wont rule it out. I may very well come to that. – Ryan Feb 16 '11 at 04:22
  • If it is a bottleneck in your application, then there may be no harm doing it all in one query, and processing the result. As SQL queries to tend to be one of the biggest bottlenecks. – Jacob Feb 16 '11 at 04:31
-1

You should be able todo something like this... 

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

To clarify, events.aid is the unique ID of the user. So when I fetch all these records and group them by event, I get a list of all unique event nicknames and the users that created them. enter image description here

Peter
  • 2,276
  • 4
  • 32
  • 40
  • That query does address the question. The asker does not require anything unique, he needs all the relationships of an item. Also see http://stackoverflow.com/questions/128965/is-there-something-wrong-with-joins-that-dont-use-the-join-keyword-in-sql-or-mys on joins. – Jacob Feb 16 '11 at 04:33