[Wpigdc-cms-development] MySQL doesn't support subqueries

[Liam M. <lm...@WP...>]

OK. So. It's 3:45 AM, so go easy on me.

Here's what I want to do. We want to sort events by their start date.
Unfortunately, events themselves don't have a start date in the db; their
sub-items (or whatever you want to call it) do. (Read the previous email
to understand). This wouldn't be a problem, if MySQL supported complex
joins. But it doesn't. Just the simple stuff.

Here's what I want to do: I want to select the earliest date item from the
cms_event table where the cms_event_group foreign key corresponds to that
particular event group, like so:

select start_date from event_item where event_id = [main_event_id] order
by start_date limit 1;

(if there's a better way, please let me know.)

then, I want to tack that start date onto the event_group table so I can
sort by it. And I'd like to do that in 1 SQL statement. More
complex databases will let you do that. However, it doesn't seem like
MySQL supports something like that (PLEASE correct me if I'm wrong, and
show me the proper syntax).

So. A few choices.
a. throw MySQL out, get a new database ;o) might be best in long term,
   not great in short term.
b. include the start date in the cms_event_group table (redundant!
   theoretically bad! every time you update one, you have to update the
   other!)
c. rework the events section of the schema in some other way that is not
   redundant and also works in MySQL.
d. every time we want to sort by start date for the event group (which is
   the most common field for sorting), create a new temporary table that
   does the same as the join. Then, when finished, delete the table.
   This is expensive, and if something goes wrong and the tables don't get
   deleted, wasteful. People will start DOS'ing our ass simply by hitting
   "Refresh" on the events page. Ok, I don't know if it's that expensive,
   maybe it's worth finding out.
e. Forget sorting using the database: sort in the object model. Certainly
   possible, but it doesn't work with the current setup using DBIterator
   at all. It's also most likely much slower than sorting in the db.

thoughts? favorite option? other ideas?

-------
Liam Morley

"light the deep, and bring silence to the world.
light the world, and bring depth to the silence."