So, if monday was an exercise in frustration, yesterday was a non-stop four hour marine corps workout. This time, the culprit was
MySQL, and it's lack of sub-selects (which they are working on). I have three tables,
shows,
bands, and
band_show_relationship. When a Band is put on a playbill for a show, an entry is put into
band_show_relationship with the id's of both the show and the band, and other metadata about the relationship (ie - is the band headlining?).
Getting a list of bands playing in a particular show is really easy and straightforward SQL Joins.
Getting a list of bands
NOT playing is an entirely different experience. With a sub-select, it would have been easy:
SELECT * FROM bands
WHERE id NOT IN (
SELECT band_id FROM band_show_relationship
WHERE show_id = :show_id
)
So, not having the ability to do sub-selects at this time, I tried doing all sorts of LEFT, RIGHT, LEFT OUTER, RIGHT OUTER variations, and failed. The main reason being that a band that's not on the playbill for a show yet
won't be in the band_show_relationship table. So, the data set needs to include all bands that don't have any shows assigned, as well as ones that do but are NOT assigned to this show. I seriously thought an outer join might work, but every permutation either yielded too many results (the set of data I was trying to exclude was included) or none at all.
So, after exhausting that route on my own, I went after the idea of collecting band id's for a particular show and then passing them into the SQL Method the page for assigning/unassigning bands was collecting that information anyways. And I was hoping the great
sqltest DTML tag would help me here. Nope! I found a little buglet [
Collector Issue 437].
'sqltest' has a cool way of rendering "multiples". When used, the tag renders out
a = b if only one value was passed in, and
a in (b,c,d) if a non-string sequence is passed in. You can also specify SQL operators to use via the
op attribute of the tag, so that
<dtml-sqltest band_id column="id" type=nb multiple op=ne> will render
a <> b if a single value is passed in. However, the 'ne' operator doesn't pass through to the list. If a sequence is passed in, it still renders
a in (b,c,d) instead of
a NOT in (b,c,d). Obviously, this can yield very incorrect results as passing in a single value, 'b', will exclude entries with 'b' from the result set; yet passing in a sequence of values, '(b,c,d)', will
include 'b' (as well as 'c' and 'd') in the results.
Crappy. So, I got to take a trip in my time machine and go back to the earlier Principia/Aqueduct days before the specialized SQL Methods tags and write my own list generator using
dtml-in,
dtml-if sequence-start and
dtml-if sequence-end to get the SQL that I wanted. And basically, I am now doing a sub-select because in order to get the list to place in the "not in show" method, I run my "bands in show" SQL Method and iterate over the band id's returned.
Later, a coworker who's been using MySQL for far longer than I have came up to try to help me with the
do it all in a normal query approach. Even between the two of us, we couldn't get it to work. I'm starting to think that the indexes might be to blame, or my logic is just plain wrong. But, we do have a solution in place.
And it works. Elegant? no. Efficient? probably not. But, there's a big deadline and ultimately this is on a page that's not going to be used by the public-at-large, so we'll let it lie and wait for MySQL to support sub-selects. (Or I'll just sit and wish I had voted for PostgreSQL for this project when I had the chance).