Dynamic sql row in row

Question

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

I currently have an application (PHP code) that makes this decision, but that means that we have several separate queries when we iterate over all categories. Is there a way to incorporate this dynamic SQL into the connection? 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. In addition, EXECUTE cannot be used in expressions; it is just a toplevel statement. Suggestions?

Answer 1

This is how I finally decided to solve this in the PHP client.

I decided to just keep the membership in the category_items table and use dynamic queries at the time of submission to update this table.

This is a function in my script that is called to update the categories of items during submit or update. It accepts a list of categories selected by the user (which can be selected only from categories that do not have dynamic queries), and using this and dynamic queries, he determines the difference between the categories in which the element is located and those it should be included and insert / delete if necessary to synchronize them. (Please note that the actual table names in my database do not match, as in my question, I used several common 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"); } } 

Answer 2

What happens when you want to list books by a publisher? A country? Tongue? You would have to drop them all into a single table, category_items. How would you choose which dynamic query to execute? The query-in-query method will not work.

I think your concept of "category" is too broad, which leads to overly complex SQL. I would replace the "category" to represent only the "genre" (for books). Genres are defined in their own table, and item_genres associates them with the table of elements. Books by author and books by genre should be just separate queries at the application level, and not try to make them as with the same (sorted) query at the database / SQL level. (If you have music, as well as books, they probably shouldn't all be stored in the same “items” table, because they are different concepts ... have different genres, author and artist, etc.).

I know that this really does not solve your problem the way you would like, but I think you will be happier without trying to do it this way.