Per-row dynamic sql

Question

I have a database representing something like a bookstore. There's a table containing the categories that books can be in. Some categories are defined simply using another table that contains the category-item relationships. But there are also some categories that can be defined programmatically -- a category for a specific author can be defined using a query (SELECT item_id FROM items WHERE author = "John Smith"). So my categories table has a "query" column; if it's not null, I use this to get the items in the category, otherwise I use the category_items table.

Currently, I have the application (PHP code) make this decision, but this means lots of separate queries when we iterate over all the categories. Is there some way to incorporate this dynamic SQL into a join? Something like:

SELECT c.category, IF(c.query IS NULL, count(i.items), count(EXECUTE c.query)
FROM categories c
LEFT OUTER JOIN category_items i
ON c.category = i.category

EXECUTE requires a prepared statement, but I need to prepare a different statement for each row. Also, EXECUTE can't be used in expressions, it's just a toplevel statement. Suggestions?

Answer 1

What happens when you want to list books by publisher? Country? Language? You'd have to throw them all into a single "category_items" table. How would you pick which dynamic query to execute? The query-within-a-query method is not going to work.

I think your concept of "category" is too broad, which is resulting in overly complicated SQL. I would replace "category" to represent only "genre" (for books). Genres are defined in their own table, and item_genres connects them to the items table. Books-by-author and books-by-genre should just be separate queries at the application level, rather than trying to do them both with the same (sort of) query at the database/SQL level. (If you have music as well as books, they probably shouldn't all be stored in a single "items" table because they're different concepts ... have different genres, author vs. artist, etc.)

I know this does not really solve your problem in the way you'd like, but I think you'll be happier not trying to do it that way.

Answer 2

Here's how I finally ended up solving this in the PHP client.

I decided to just keep the membership in the category_items table, and use the dynamic queries during submission to update this table.

This is the function in my script that's called to update an item's categories during submission or updating. It takes a list of user-selected categories (which can only be chosen from categories that don't have dynamic queries), and using this and the dynamic queries it figures out the difference between the categories that an item is currently in and the ones it should be in, and inserts/deletes as necessary to get them in sync. (Note that the actual table names in my DB are not the same as in my question, I was using somewhat generic terms.)

function update_item_categories($dbh, $id, $requested_cats) {
    $data = mysql_check($dbh, mysqli_query($dbh, "select id, query from t_ld_categories where query is not null"), 'getting dynamic categories');
    $clauses = array();
    while ($row = mysqli_fetch_object($data))
        $clauses[] = sprintf('select %d cat_id, (%d in (%s)) should_be_in',
            $row->id, $id, $row->query);
    if (!$requested_cats) $requested_cats[] = -1; // Dummy entry that never matches cat_id
    $requested_cat_string = implode(', ', $requested_cats);
    $clauses[] = "select c.id cat_id, (c.id in ($requested_cat_string)) should_be_in
                  from t_ld_categories c
                  where member_type = 'lessons' and query is null";
    $subquery = implode("\nunion all\n", $clauses);
    $query = "select c.cat_id cat_id, should_be_in, (member_id is not null) is_in
              from ($subquery) c
              left outer join t_ld_cat_members m
              on c.cat_id = m.cat_id
              and m.member_id = $id";
    // printf("<pre>$query</pre>");
    $data = mysql_check($dbh, mysqli_query($dbh, $query), 'getting current category membership');
    $adds = array();
    $deletes = array();
    while ($row = mysqli_fetch_object($data)) {
        if ($row->should_be_in && !$row->is_in) $adds[] = "({$row->cat_id}, $id)";
        elseif (!$row->should_be_in && $row->is_in) $deletes[] = "(cat_id = {$row->cat_id} and member_id = $id)";
    }
    if ($deletes) {
        $delete_string = implode(' or ', $deletes);
        mysql_check($dbh, mysqli_query($dbh, "delete from t_ld_cat_members where $delete_string"), 'deleting old categories');
    }
    if ($adds) {
        $add_string = implode(', ', $adds);
        mysql_check($dbh, mysqli_query($dbh, "insert into t_ld_cat_members (cat_id, member_id) values $add_string"),
            "adding new categories");
    }
}