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

Try this:

my $result = $model->search({ id => { -not_in => [12,13,17] } });
[download]

hth, phay

Ordinary morality is for ordinary people. -- Aleister Crowley

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

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