Do I really need multiple queries?

sneakyimp

I have some pretty complex queries that I have to run on a large database table containing events. I have several tables but these questions relate to 3 or 4 of them. :bemused:

events - the big table full of events
 id
 name
 start_time
 end_time
 other fields...

subcategories - each event fits up to five of these 50 different subcats
 id
 name
 cat_id - connects to the categories table id field

categories - each subcategory fits in one of these 10 or so cats
 id
 name

event_subcat_assoc
 id
 event_id - connects to the events table id field
 subcat_id - connects to the subcategories table id field

I have two questions.

1) My first query problem is that I have is that I am supposed to design my page to select 3 events at random from each category. Given the size of my database and the complexity of my query, I am trying to use ORDER BY RAND() to retrieve these random events in the hope that it will be faster.

But this seems to require that I run what is essentially the same query once for each category.

$cat_id = 1; // id 1 is category 'music'
// this might be more efficient as SELECT DISTINCT...
$sql = "SELECT e.id, e.name, e.start_time COUNT(esa.id) FROM events e, subcategories s, categories c, event_subcat_assoc esa WHERE esa.event_d=e.id AND esa.subcat_id=s.id AND s.cat_id=$cat_id GROUP BY e.id ORDER BY RAND()";

QUESTION: Can anyone recommend a way to get 3 random events for each category with only one query rather than running this query over for each category?

2) The page is also supposed to display all the subcategories associated with any given event which need not all belong to the same category. Once I have retrieved my random events, I must run another query to grab the subcategory names for each event.

QUESTION: Can I somehow grab all the subcategory names when I grab the event in the first place? There are fifty subcategories and I just can't think of any way to summarize 5 records from the event_subcat_assoc (and their name from the subcats table) as a single record in my primary random events query.

bogu

First of all, why did u create this table?

event_subcat_assoc
 id
 event_id - connects to the events table id field
 subcat_id - connects to the subcategories table id field

why don't u just add subcat_id field in the events table?

This should extract 3 random events from 3 different category and their subcategory name:

SELECT
	esa.id as esa_id, esa.event_id, esa.subcat_id,
	s.id as sid, s.name as sname, s.cat_id as scat_id,
	c.id as cid, c.name as cname
FROM event_subcat_assoc esa
LEFT JOIN subcategories s ON esa.subcat_id = s.id
LEFT JOIN categories c ON s.cat_id = c.id
GROUP BY c.id
ORDER BY RAND()
LIMIT 3

of course I extract only the events id, u need to link the events table to that query in order to extract the event name, start_time and end_time u should get the idea ...

BTW, answering to your thread title: No, u can do it with one query ...

Piranha

Before answering your question I wonder why you have "categories c" in you FROM clause, you are not using it in the rest of the query. I recommend that you remove it since it only slow down the query.

For the first question you could use LEFT JOIN to join the table events 2 more times, in a way simular to this post. I don't know if the query will be faster, but it is worth a try at least.

For the second question it depends on how you want to get the result. If you want it as one row per result it should be much better to do it the way you do it now. But if you want it in a single string it should be possible to use CONCAT in some way, maybe CONCAT(subquery to get the names). But I don't know for sure about this since I haven't used it myself.

sneakyimp

Thanks for your help guys!

bogu wrote:
First of all, why did u create this table?
event_subcat_assoc
 id
 event_id - connects to the events table id field
 subcat_id - connects to the subcategories table id field
why don't u just add subcat_id field in the events table?

because up to five subcategories can be associated with any given event. for instance, an event can be 'rock music' but also 'alternative music' and be 'venue=club'

bogu wrote:

This should extract 3 random events from 3 different category and their subcategory name:

SELECT
	esa.id as esa_id, esa.event_id, esa.subcat_id,
	s.id as sid, s.name as sname, s.cat_id as scat_id,
	c.id as cid, c.name as cname
FROM event_subcat_assoc esa
LEFT JOIN subcategories s ON esa.subcat_id = s.id
LEFT JOIN categories c ON s.cat_id = c.id
GROUP BY c.id
ORDER BY RAND()
LIMIT 3

I'm not certain, but i think that query will return only 3 items from all categories, not 3 items from each category.

Piranha:
You're totally right about 'categories c.' i'll take that out.

As for the LEFT JOIN suggestion...I had considered that. The problem is that these events are supposed to be chosen randomly (ORDER BY RAND()). I am not sure, but I tend to think that the results might be pretty consistent for a given database. Come to think of it, i'm not even that certain that ORDER BY RAND() is sufficiently random...but that's another discussion.

I'm liking your suggestion for problem two pretty much, but I want to combine it with the query from problem 1 and I'm having a bit of trouble figuring out how to keep all the joins and fields straight.

bogu

sneakyimp wrote:
because up to five subcategories can be associated with any given event. for instance, an event can be 'rock music' but also 'alternative music' and be 'venue=club'

So when u add an event u append to it multiple subcategories or only one subcategory?

sneakyimp wrote:
I'm not certain, but i think that query will return only 3 items from all categories, not 3 items from each category.

Ups I didn't read carefully (my mistake :p ), I don't know if that can be done using only one query I think u will need to extract 3 random events for each category...

sneakyimp

bogu wrote:
So when u add an event u append to it multiple subcategories or only one subcategory?

Multiple! You click 'post event' and are presented with all the subcategories, each with a checkbox. You can choose up to five.