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

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

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

Replies are listed 'Best First'.
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]