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

[Ieronim]

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

'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 :)

[jgallagher]

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

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",
});

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.

[Ieronim]

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 :))