Gossamer Forum
(Advanced Search)
for
Home : Products : Gossamer Links : Development, Plugins and Globals :

No NOT IN option for GT::SQL?

Oct 2, 2013, 7:44 AM
Andy
Veteran / Moderator (18436 posts)
Andy's avatar
Oct 2, 2013, 7:44 AM
Post #1 of 8
Views: 4971
Shortcut
Quote Reply
No NOT IN option for GT::SQL?
Hi,

I'm just doing a job, and I needed a NOT IN statement. For example, with an IN statement, we just do:

Code:
$DB->table("Foo")->select( GT::SQL::Condition->new("Field",'IN",\@array_of_ids) )

However, it doesn't seem like there is a NOT IN option?

Code:
$DB->table("Foo")->select( GT::SQL::Condition->new("Field",'NOT IN",\@array_of_ids) )

I ended up having to do a bit of a dirty hack with:

Code:
my $ids = join(",",@ids);
$DB->table("Links")->do_query(qq|DELETE FROM glinks_Links WHERE HotelID NOT IN ($ids)|) || die $GT::SQL::error;

Am I missing the NOT IN feature, or does it just not exist? Any chance of adding it? Angelic

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Oct 11, 2013, 3:34 AM
el noe
User (429 posts)
Oct 11, 2013, 3:34 AM
Post #2 of 8
Views: 4853
Shortcut
Quote Reply
Re: [Andy] No NOT IN option for GT::SQL? In reply to
Hi Andy,

I just looked at ./admin/GT/SQL/Condition.pm

and there is:
Code:
elsif ($op eq '!=' || $op eq '<>' and ref $val eq 'ARRAY') {
my $output;
if (@$val > 1) {
$output = "NOT ($col IN ";
$output .= '('
. join(',' => map !length || /\D/ ? quote($_) : $_, @$val)
. ')';
$output .= ')';
}
elsif (@$val == 0) {
$output = '1 = 1';
}
else {
$output = "$col $op " . quote($val->[0]);
}
push @output, $output;
}

I am sorry but I did not test it so probably it does not really what you want but in case it is the right piece of code it would be more like:

Code:
$DB->table("Foo")->select( GT::SQL::Condition->new("Field","<>",\@array_of_ids) )
or
Code:
$DB->table("Foo")->select( GT::SQL::Condition->new("Field","!=",\@array_of_ids) )

otherwise it would be really nice to get a small code update from GT.

Regards

Niko

Last edited by:

el noe: Oct 11, 2013, 3:36 AM
Oct 11, 2013, 3:37 AM
Andy
Veteran / Moderator (18436 posts)
Andy's avatar
Oct 11, 2013, 3:37 AM
Post #3 of 8
Views: 4854
Shortcut
Quote Reply
Re: [el noe] No NOT IN option for GT::SQL? In reply to
Ah cool - so looks like there is a way to do it then - but as you said, it would still be nice to have a "NOT IN" option when passing it along via count/select etc. GT? Angelic

Cheers
Oct 11, 2013, 6:08 AM
el noe
User (429 posts)
Oct 11, 2013, 6:08 AM
Post #4 of 8
Views: 4870
Shortcut
Quote Reply
Re: [Andy] No NOT IN option for GT::SQL? In reply to
Hi Andy,

just for my information: Does the above do what you expect or is it something else?
I did not get it.

Still hoping for a GT comment Smile

Regards

Niko
Oct 11, 2013, 7:02 AM
Andy
Veteran / Moderator (18436 posts)
Andy's avatar
Oct 11, 2013, 7:02 AM
Post #5 of 8
Views: 4837
Shortcut
Quote Reply
Re: [el noe] No NOT IN option for GT::SQL? In reply to
It seems to work:

Code:
my @id = (1,2,3,4,5);
my $sth = $DB->table('Users')->select( ['UserID'], GT::SQL::Condition->new('UserID','<>',\@id) ) || die $GT::SQL::error;
print $sth->query . "\n\n";

SELECT UserID FROM glinks_Users WHERE (NOT (UserID IN (1,2,3,4,5)))

It should probably be NOT IN instead of NOT (IN (xxxx))

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Oct 11, 2013, 7:10 AM
el noe
User (429 posts)
Oct 11, 2013, 7:10 AM
Post #6 of 8
Views: 4850
Shortcut
Quote Reply
Re: [Andy] No NOT IN option for GT::SQL? In reply to
Hi Andy,

guess you are right but this redundant brace does probably no harm at all.

looked at mysql.com:

Code:
SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

So it seems fine to me.
I still like the core of Links and hope there will be some updates of bugfixes/improvements GT already did.

Regards

Niko
Oct 11, 2013, 7:21 AM
Andy
Veteran / Moderator (18436 posts)
Andy's avatar
Oct 11, 2013, 7:21 AM
Post #7 of 8
Views: 4842
Shortcut
Quote Reply
Re: [el noe] No NOT IN option for GT::SQL? In reply to
Yeah. I doubt its that much hard to to add in the NOT IN option as part of the main condition options (I'm amazed it hasn't been asked for already to be honest!)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Oct 11, 2013, 8:24 AM
el noe
User (429 posts)
Oct 11, 2013, 8:24 AM
Post #8 of 8
Views: 4852
Shortcut
Quote Reply
Re: [Andy] No NOT IN option for GT::SQL? In reply to
So if Adrian or someone else from GT is going to read this we are suggesting to change in Condition.pm:

Code:
# Perl: column => '!=' => [1,2,3]
# SQL: NOT(column IN (1,2,3))
elsif ($op eq '!=' || $op eq '<>' and ref $val eq 'ARRAY') {

to:

Code:
# Perl: column => '!=' => [1,2,3]
# SQL: NOT(column IN (1,2,3))
elsif (uc $op eq 'NOT IN' || $op eq '!=' || $op eq '<>' and ref $val eq 'ARRAY') {

My Perl is too weak to know if it has to be:
Code:
# Perl: column => '!=' => [1,2,3]
# SQL: NOT(column IN (1,2,3))
elsif ((uc $op eq 'NOT IN' || $op eq '!=' || $op eq '<>') and ref $val eq 'ARRAY') {

with three parameters, but I guess that or will be ok for two "||" without a bracket as well.

Regards

Niko

Last edited by:

el noe: Oct 11, 2013, 8:29 AM