Combined SQL Queries (e.g., EXCEPT) and DBIx::Class

jgallagher has asked for the wisdom of the Perl Monks concerning the following question:

I hesitated posting this question, because it actually seems to be two poor questions. First, is there any way to use a combined query (PostgreSQL docs) through DBIx::Class? I've looked through SQL::Abstract and the DBIx cookbook to no avail. At first attempt, I can't even use search_literal because the string is stuffed into a WHERE clause:

$schema->resultset('foo')->search_literal('EXCEPT SELECT * FROM bar')
[download]

becomes something like

SELECT * FROM foo WHERE ( EXCEPT SELECT * FROM BAR )
[download]

which obviously isn't right (or even valid SQL).

The alternate/base question is really more SQL than Perl, but I'll go ahead and describe it anyway; I'm sure someone here has done something like this before.

Suppose I have two tables (say containers and items, with just ID columns) with a many-to-many mapping table (mapping, with two columns: container_id and item_id). Any given container can hold zero or more items, and any given item can be contained in zero or more containers. Finding out what items are in a given container (say '1') is easy:

$schema->resultset('items')->search({'mapping.container_id' => 1}, {jo
+in => 'mapping'});
[download]

or the roughly equivalent SQL

SELECT * FROM items LEFT JOIN mapping ON (items.item_id = mapping.item
+_id) WHERE mapping.container_id = 1
[download]

However, the "inverse" query is not as obvious. How do I get a list of all items that are not in a given container? Simply modifying the above to say "container id != 1" doesn't work since an item could be in both container 1 and container 2. The following SQL will do it:

SELECT items.item_id FROM items LEFT JOIN mapping ON (items.item_id = 
+mapping.item_id) 
  EXCEPT SELECT items.item_id FROM items LEFT JOIN mapping ON (items.i
+tem_id = mapping.item_id) WHERE mapping.container_id = 1
[download]

but that feels wasteful, with the added complication of translating it into DBIx::Class. Anyone have any suggestions? (Obviously the above tables are somewhat contrived; I tried to give enough information, but let me know if I need to elaborate some point.)

Comment on Combined SQL Queries (e.g., EXCEPT) and DBIx::Class Select or Download Code

Replies are listed 'Best First'.
Re: Combined SQL Queries (e.g., EXCEPT) and DBIx::Class by Ieronim (Friar) on Jul 26, 2006 at 21:54 UTC
Another variant of SQL to answer your second question—reliable, fast and does not use subqueries at all :) `SELECT i.* FROM items AS i LEFT JOIN mapping AS m ON (m.item_id = i.item_id AND m.container_id= +1) WHERE m.container_id IS NULL` [download] 'Fast' means that it is definitely not slower than than the variant with subqueries, and it can be better optimized by the server. Generally, any EXCEPT statements can be rewritten using subqueries and often even using simple JOINs. Try this way :) `s;;Just-me-not-h-Ni-m-P-Ni-lm-I-ar-O-Ni;;tr?IerONim-?HAcker ?d;print`	[reply] [d/l] [select]
Re^2: Combined SQL Queries (e.g., EXCEPT) and DBIx::Class by jgallagher (Pilgrim) on Jul 26, 2006 at 22:08 UTC
I've got something very similar to that working now that doesn't break the DBIx::Class style too much. The SQL is (roughly) `SELECT i.item_id FROM items AS i LEFT JOIN mapping AS m ON (m.item_id += i.item_id) GROUP BY i.item_id HAVING EVERY(m.container_id != 1) OR EVERY(m.contai +ner_id != 1) IS NULL` [download] I'm not sure of a couple things: whether this is better performance-wise than what you posted or whether `EVERY` (or equivalent `BOOL_AND`) is portable beyond PostgreSQL. However, it does translate easily into DBIx::Class: `my $rs = $schema->resultset('items'); my $items = $rs->search(undef, { join => 'mapping', group_by => [ map { "me.$_" } $rs->result_source->columns ], having => "EVERY(m.container_id != 1) OR EVERY(m.container_id != 1) +IS NULL", });` [download] The `map` feels a little hackish, and I don't get to use a placeholder for the container_id, but overall, I'm not too unhappy with it. Update: Added the additional "IS NULL" clause, necessary for items which are not contained in any container.	[reply] [d/l] [select]
Re^3: Combined SQL Queries (e.g., EXCEPT) and DBIx::Class by Ieronim (Friar) on Jul 26, 2006 at 22:32 UTC
My SQL is faster, as it fetches less rows and does not need grouping, but i did not find a way to translate it to DBIx::Class structures :) That's why i dislike DBIx::Class and other database wrappers—they always limit my abilities :)) `s;;Just-me-not-h-Ni-m-P-Ni-lm-I-ar-O-Ni;;tr?IerONim-?HAcker ?d;print`	[reply] [d/l]
Re: Combined SQL Queries (e.g., EXCEPT) and DBIx::Class by dirving (Friar) on Jul 26, 2006 at 20:38 UTC
I don't know a lot about DBIx::Class, but you can write the query using something like: `SELECT * FROM items WHERE item_id NOT IN (SELECT item_id FROM mapping WHERE container_id = ?)` [download] It looks like you can shoehorn that WHERE clause into the search_literal method the way you need to.	[reply] [d/l]
Re: Combined SQL Queries (e.g., EXCEPT) and DBIx::Class by davidrw (Prior) on Jul 26, 2006 at 20:52 UTC
At first attempt, I can't even use search_literal because the string is stuffed into a WHERE clause: `$schema->resultset('foo')->search_literal('EXCEPT SELECT * FROM bar')` [download] becomes something like `SELECT * FROM foo WHERE ( EXCEPT SELECT * FROM BAR )` [download] What about: `$schema->resultset('foo')->search_literal( '1=1) EXCEPT (SELECT * FROM + bar' )` [download] Just _might_ fall into the category of "Hack" though :)	[reply] [d/l] [select]
Re: Combined SQL Queries (e.g., EXCEPT) and DBIx::Class by Khen1950fx (Canon) on Jul 26, 2006 at 21:19 UTC
In regards to your question about doing a combined query, there is a module that might help you. It's called Genezzo. You can directly create or manipulate database tables with it. It's simple and can be done from the command-line. See: Genezzo	[reply]