SQL WHERE clauses with DBIx::Class, using NOT and IN

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

Dear Monks:

Now I have been using DBIx::Class for some time and I am very happy with it! I would like to run a SELECT statement such as:

  SELECT * FROM my_table WHERE id NOT IN (12,13,17)
[download]

Possibly, this can be broken down into two simpler problems.

Implement "IN" operator using DBIx::Class. Inspired by the manual describing LIKE expressions, I tried $schema->resultset('my_table')->search({ id => {'in', '(12,13,17)'} }) but this did not work.
Implement "NOT" operator using DBIx::Class. In analogy to "-and" and "-or," I tried $schema->resultset('my_table')->search({ -not => {id => 17} }), again without success.

How can such WHERE clauses be implemented using DBIx::Class?

Thank you,
Hansres

Comment on SQL WHERE clauses with DBIx::Class, using NOT and IN Select or Download Code

Replies are listed 'Best First'.
Re: SQL WHERE clauses with DBIx::Class, using NOT and IN by rhesa (Vicar) on Jul 04, 2006 at 18:32 UTC
Since DBIx::Class uses SQL::Abstract, you should be able to get away with this: `search({ id => { 'not in' => [ 1, 2, 3 ] }, });` [download] My version of SQL::Abstract turns that into `WHERE ( id NOT IN ( ?, ?, ? ) )`, which seems to be exactly what you need.	[reply] [d/l] [select]
Re: SQL WHERE clauses with DBIx::Class, using NOT and IN by Ieronim (Friar) on Jul 04, 2006 at 17:50 UTC
Every `x NOT IN (a, b...)` can be mapped to `(x <> a) AND (x <> b) ...`. In your case it will result in `SELECT * FROM my_table WHERE (id <> 12) AND (id <> 13 AND (id <> 17)` [download] As you are not typing the SQL statements, but generate it (actually using the power of SQL::Abstract), this way can be much easier than implementing `NOT IN`.	[reply] [d/l] [select]
Re: SQL WHERE clauses with DBIx::Class, using NOT and IN by hrr (Monk) on Jul 04, 2006 at 18:48 UTC
Thank you for refering me to SQL::Abstract, this is where the `WHERE` clauses are documented. Furthermore, the suggested `NOT IN` works well, great! Regarding the two cases listed in my question, I found that The proper code is `$schema->resultset('my_table')->search({ id => {-in => [12,13,17]} })`. Note that the r.h.s. of `-in` is an arrayref, not a string as I guessed initially. `NOT` is not yet possible. On SQL::Abstract, it is explained that there is not (yet) any explicit support for SQL compound logic statements like "AND NOT".	[reply] [d/l] [select]
Re: SQL WHERE clauses with DBIx::Class, using NOT and IN by phaylon (Curate) on Jul 05, 2006 at 11:28 UTC
Try this: `my $result = $model->search({ id => { -not_in => [12,13,17] } });` [download] hth, phay Ordinary morality is for ordinary people. -- Aleister Crowley	[reply] [d/l]
Re^2: SQL WHERE clauses with DBIx::Class, using NOT and IN by hrr (Monk) on Jul 05, 2006 at 15:49 UTC
This works well, thank you! Interestingly, I have found that queries with large `-in` lists searching on the primary key, e.g. containing ~5000 elements, actually crash (ActiveState) perl. Where does this limit on the number of elements might come from? Of course, I could break up the query and use a shorter `IN` condition; however, that would come at a severe performance penalty--the broken up queries use a total of 45 seconds. Also, the broken-up query is much slower than direct access to each row using the primary key: `resultset('table')->find($pk)`!	[reply] [d/l] [select]
Re^3: SQL WHERE clauses with DBIx::Class, using NOT and IN by phaylon (Curate) on Jul 11, 2006 at 13:28 UTC
I never did that with that much IDs. I guess a temporary table would be one way for it. For the DBIC parts of it you could also ask on #dbix-class on irc.perl.org, which is rather active and full of people with more experience in this than I have. Ordinary morality is for ordinary people. -- Aleister Crowley	[reply]
Re: SQL WHERE clauses with DBIx::Class, using NOT and IN by hrr (Monk) on Jul 05, 2006 at 22:53 UTC
From SQL::Abstract, I also learned how SQL expressions can be used for `INSERT` and `UPDATE` statements. For example, `$row->update({end => ['CURRENT_TIMESTAMP']});` [download] uses the `CURRENT_TIMESTAMP` register of a DB2 database.	[reply] [d/l] [select]