The Return of jQuery Madness: SQL Re-Engineering

In response to my previous jQuery Madness post, Leonard said

I had a similar problem with ajax calls. I had jack dropdowns: building, Floor, room, jack. One would populate the next and then the next etc, just like yours, except I didn't automatically pick the top selection.

When making multiple calls to any API/framework/database I always ask myselfr: What do you know when calling the second call, that you didn't know with the first call. The user hasn't put in more data, so is there a valid reason for more load on the other end of the pipe? Could you have done all the work with the first call?

I would suggest re-engineering your calls just a little bit so that when foo is changed, your backend assumes that you will want the first bar/blee/quuz. (similar assumptions for bar and blee).

This way you only need one ajax call per selection onChange.

This seems logical. Remember that at one point, AJAX meant Asynchronous Javascript And XML, and while lots of people have rejected the XML in favor of JSON, RSS, or plain text, making it AJA (see also: Steely Dan). But until this, I don't know that I had really considered the asynchronous part.

As an experiment, I've created a page that loads everything in one page, logging the URLs at the bottom of the page with .ajaxComplete(), and you can really see everything come together in it's own order, not the order I want it to.

I now reveal that foo, bar, blee and bar are really lab_director, request_id, accession_id and barcode. Each lab_director has one or more request_id, each request_id has one or more accession_id, and each accession_id has zero or more barcode. null counts as a barcode. Here's the code I have so far.

SELECT  re.lab_director , re.request_id  , acc.accession_id , bc.barcode
    FROM    requests re     , accessions acc , accession_barcode bc
    WHERE   re.request_id    = acc.request_id
        AND acc.accession_id = bc.accession_id
    ORDER BY lab_director

The problem is, this only works for things that have a barcode. I could get everything but barcodes and add to the data structure as it comes up, but if I could get something like above, except barcode being filled in as null if there's no barcode for that accession_id in accession_barcode.

Any pointers? I don't even know if I could hammer this down to a googlable question.