Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: how to convert this into a linkSQL statement?

May 25, 2005, 4:00 AM

ryel01

Veteran (1264 posts)

May 25, 2005, 4:00 AM

Post #1 of 8

Shortcut

how to convert this into a linkSQL statement?

Hi

I need to convert this into some code for linkSQL but am having some trouble. It's actually the mysql answer to what I need to do in another post I made - but I can't edit that post for some reason... who knows.

This is the mysql version that I need to translate into linkSQL code...

Code:
select * from lsql_easy_pol, lsql_easy_relations where lsql_easy_pol.pol_id = lsql_easy_relations.pol_id_fk;

then I also need this too...

Code:
select * from lsql_easy_pol, lsql_easy_relations where lsql_easy_pol.pol_id != lsql_easy_relations.pol_id_fk;

The only difference between them is the "!=" vs "=" when matching the columns.

Can anyone help with this one?

Thanks!

May 25, 2005, 4:21 AM

Andy

Veteran / Moderator (18441 posts)

May 25, 2005, 4:21 AM

Post #2 of 8

Shortcut

Re: [ryel01] how to convert this into a linkSQL statement? In reply to

Hi,

No guarantees, but maybe something like this?

Code:
my $table = $DB->table('easy_pol','easy_relations'); 
my $cond = GT::SQL::Condition->new( 'easy_pol.pol_id','=','easy_relations.pol_id_fk'); 
my $sth = $table->select($cond ) || die $GT::SQL::error; 
while (my $hit = $sth->fetchrow_hashref) { 
   ... 
}

Code:
my $table = $DB->table('easy_pol','easy_relations'); 
my $cond = GT::SQL::Condition->new( 'easy_pol.pol_id','!=','easy_relations.pol_id_fk'); 
my $sth = $table->select($cond ) || die $GT::SQL::error; 
while (my $hit = $sth->fetchrow_hashref) { 
   ... 
}

Hope that helps.

Cheers

Andy (mod)
andy@ultranerds.co.uk

May 25, 2005, 5:36 AM

ryel01

Veteran (1264 posts)

May 25, 2005, 5:36 AM

Post #3 of 8

Shortcut

Re: [Andy] how to convert this into a linkSQL statement? In reply to

hi andy - I've tried it that way but it has a few surprises. Actually I think it may be producing 2 bugs.

See the line of code below which the linkSQL code produces...

Code:
SELECT * FROM lsql_easy_pols,lsql_easy_relations WHERE (lsql_easy_pols.pol_id != 'easy_relations.pol_id_fk')

notice two things...

1. "lsql" is missing off the "easy_relations.pol_id_fk" part. That should be automatically added I thought.

2. The '' marks around 'easy_relations.pol_id_fk' cause the command to fail when run through mysql - it returns no results. If you remove those '' then it works perfectly. Unfortunately they're added automatically by the script.

You don't know of another way by any chance? Or how to get rid/fix those nasties?

Regan

Last edited by:

ryel01: May 25, 2005, 5:37 AM

May 25, 2005, 6:14 AM

Andy

Veteran / Moderator (18441 posts)

May 25, 2005, 6:14 AM

Post #4 of 8

Shortcut

Re: [ryel01] how to convert this into a linkSQL statement? In reply to

Mmm.. interesting. It doesn't help if you change;

my $cond = GT::SQL::Condition->new( 'easy_pol.pol_id','!=',lsql_easy_relations.pol_id_fk);

?

TBH, I've probably given you the wrong method for doing what you need. Hopefully someone at GT can shed some light Smile

Cheers

Andy (mod)
andy@ultranerds.co.uk

May 25, 2005, 7:41 AM

fuzzy logic

Enthusiast (854 posts)

May 25, 2005, 7:41 AM

Post #5 of 8

Shortcut

Re: [Andy] how to convert this into a linkSQL statement? In reply to

from the GT::Condition manpage:

Quote:

By default, all values are quoted, so you don't need to bother using any quote function. If you don't want something quoted (say you want to use a function for example), then you pass in a reference.

For example, to find users who have a last name that sounds like 'krohn', you could use your SQL engines SOUNDEX function:

my $cond = GT::SQL::Condition->new(LastName => '=' => \"SOUNDEX('krohn')");

and the right side wouldn't be quoted.

Philip
------------------
Limecat is not pleased.

May 25, 2005, 3:10 PM

ryel01

Veteran (1264 posts)

May 25, 2005, 3:10 PM

Post #6 of 8

Shortcut

Re: [fuzzy logic] how to convert this into a linkSQL statement? In reply to

hi philip / andy

philip's bit from the manual page solved the quote problem - thanks for that!

do you have any idea why it's not adding the "lsql_" prefix to the table name when it converts the code to sql?

regan

May 25, 2005, 3:39 PM

ryel01

Veteran (1264 posts)

May 25, 2005, 3:39 PM

Post #7 of 8

Shortcut

Re: [Andy] how to convert this into a linkSQL statement? In reply to

I think the script dropping the prefix to the database table might be a bug. I've managed to get around it by manually getting and adding the prefix to the table name like this...

Code:
my $table = $DB->table('easy_pols', 'easy_relations'); 

my $prefix = $DB->prefix ; 

my $cond = GT::SQL::Condition->new( "easy_pols.pol_id" => '!=' => \"${prefix}easy_relations.pol_id_fk");

I'm developing on LinkSQL Version: 2.2.0 so I'm not sure if this may have been addressed in the new version - if you're part of the dev team or can put a word in andy perhaps you could find out?

Regan

May 25, 2005, 11:24 PM

Andy

Veteran / Moderator (18441 posts)

May 25, 2005, 11:24 PM

Post #8 of 8

Shortcut

Re: [ryel01] how to convert this into a linkSQL statement? In reply to

Hi,

May be worth emailing support at gossamer-threads.com, to see if have any suggestions/fixes. Afraid I'm not part of the dev team =)

Cheers

Andy (mod)
andy@ultranerds.co.uk