search NOT IN

4 messages Options
Embed this post
Permalink
Stuart Dodds

search NOT IN

Reply Threaded More More options
Print post
Permalink
Is it possible to do a search on a field where it is not equal to a list
of values.

For example, the sql I would like to produce is as follows:

WHERE id NOT IN (?,?,?)

however doing:

->search({
  id => { '!=', \@id_list }
});

doesn't do the right thing as you get:

WHERE id != ? OR id != ? OR id != ?

the only other way I can think is to do a search_literal with some
string concatenation.

Any help would be great,

Stuart.




_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Tom Hukins

Re: search NOT IN

Reply Threaded More More options
Print post
Permalink
On Mon, Jul 06, 2009 at 04:26:27PM +0200, Stuart Dodds wrote:
> For example, the sql I would like to produce is as follows:
>
> WHERE id NOT IN (?,?,?)

Assuming you use a recent version of SQL::Abstract, see:
http://search.cpan.org/~ribasushi/SQL-Abstract-1.56/lib/SQL/Abstract.pm#Special_operators_:_IN,_BETWEEN,_etc.

Tom

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Ash Berlin

Re: search NOT IN

Reply Threaded More More options
Print post
Permalink
In reply to this post by Stuart Dodds


"Stuart Dodds" <[hidden email]> wrote:

>Is it possible to do a search on a field where it is not equal to a list
>of values.
>
>For example, the sql I would like to produce is as follows:
>
>WHERE id NOT IN (?,?,?)
>
>however doing:
>
>->search({
>  id => { '!=', \@id_list }
>});
>
>doesn't do the right thing as you get:
>
>WHERE id != ? OR id != ? OR id != ?
>
>the only other way I can think is to do a search_literal with some
>string concatenation.
>
>Any help would be great,
>
>Stuart.
Look at the docs for SQL::Abstract.
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Stuart Dodds

Re: Re: search NOT IN

Reply Threaded More More options
Print post
Permalink
In reply to this post by Tom Hukins
> On Mon, Jul 06, 2009 at 04:26:27PM +0200, Stuart Dodds wrote:
> > For example, the sql I would like to produce is as follows:
> >
> > WHERE id NOT IN (?,?,?)
>
> Assuming you use a recent version of SQL::Abstract, see:
> http://search.cpan.org/~ribasushi/SQL-Abstract-1.56/lib/SQL/Abstract.pm#Special_operators_:_IN,_BETWEEN,_etc.
>
> Tom


Ahh that's where it's hiding....thanks for your help.

Stuart.


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...