Gossamer Forum: Products: Gossamer Links: Discussions: SQL problem

Apr 6, 2003, 7:29 PM

long327

User (248 posts)

Apr 6, 2003, 7:29 PM

Post #1 of 4

Shortcut

SQL problem

Hi All,

I have some problem with LinkSQL grammar. In linksql211, I have the following code:

Code:
 use Links qw/$CFG $IN $DB/;  
use GT::SQL::Condition; 
$link_db = $DB->table ('Links'); 
$cond = new GT::SQL::Condition; 
$cond->add ('Status', '=', '200'); 
$cond->add('URL', 'NOT LIKE', ['%pdf','%doc','%gif','%jpeg']); 
print $cond->sql;

This will print "Status = '200' AND lsLinks.URL NOT LIKE 'ARRAY(0x1bf55e4)". Note here the program interprets the [] as an array.The second part of the condition won't work and no url with those file extension will be excluded.If the condition is like this:

Code:
$cond->add ('Status', '=', '200', 'URL', 'NOT LIKE', ['%pdf','%doc','%gif','%jpeg']);

This will print ""Status = '200' ". The second part of the condition is totally ignored. I could not figure out what is wrong with my code. Thank you for any clues. Long

Apr 6, 2003, 7:44 PM

pugdog

Veteran / Moderator (6956 posts)

Apr 6, 2003, 7:44 PM

Post #2 of 4

Shortcut

Re: [long327] SQL problem In reply to

In the docs,

a list is converted when there is a flag like "IN" as the operator. I'm not sure this happens with "LIKE".

Code:

You can also easily use lists that get converted using the SQL IN operator. For example, to find anyone with a  

first name of Alex, Scott or Jason, you can do:  

    my $cond = GT::SQL::Condition->new('FirstName', 'IN', ['Alex', 'Scott', 'Jason']); 

which will turn into:  

    FirstName IN ('Alex', 'Scott', 'Jason')

What you are trying to do, doesn't even seem to be valid SQL, but I'm not an expert. I tried to execute the HTML in the SQL query box, and got errors each time.

You might need to use a complext condition with an "and" for the two conditions you have, and "or" for the documents.

I think I posted some examples a few days ago on another issue, nesting the $cond objects for complex queries.

PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.

Apr 6, 2003, 11:13 PM

long327

User (248 posts)

Apr 6, 2003, 11:13 PM

Post #3 of 4

Shortcut

Re: [pugdog] SQL problem In reply to

HI Pugdog,

Thank you for your reply. Yeh, I noticed that I can only use IN with [...] instead of LIKE. I have to write multiple $cond->add like this:

Code:
  	$cond->add ('Links.Status', '=', '200'); 
 	$cond->add('Links.URL', 'NOT LIKE', '%pdf'); 
       $cond->add('Links.URL', 'NOT LIKE', '%doc'); 
       $cond->add('Links.URL', 'NOT LIKE', '%gif'); 
       $cond->add('Links.URL', 'NOT LIKE', '%jpeg');

Fortunately, the boolean for all conditions are 'and', which is exactly what I wanted. But, if I want to retrive all records which have 200 status AND the file extension of pdf OR doc OR gif, etc. I will have to write something like this:

Code:
 $cond->add ('Links.Status', '=', '200'); 
 	$cond->add('Links.URL', 'LIKE', '%pdf'); 
       $cond->add('Links.URL', 'LIKE', '%doc'); 
       $cond->add('Links.URL', 'LIKE', '%gif'); 
       $cond->add('Links.URL', 'LIKE', '%jpeg');

Obviously, this won't work, because the default boolean is AND and no record will meet the requirement. If I add $cond->bool('or), then the OR will apply to all conditions, i.e. all records with status 200 OR extension pdf OR doc or gif or jpeg.

Another SQL problem is the HIT and COUNT method which never work for me if used with left_join. for example:

Code:
 my $sth = $db->select ('left_join', $cond, ['ID', 'Links.URL', 'Title', 'DateCreated']); 

my $total = $db->count ($cond); #this doesn't work, return nothing 
my $total = $db->hits;    # this doesn't work either

In this case, I have to use fetchrow_array and do a looping to get the total hits.

Anybody has the same problem?

Regards,

Long

Apr 7, 2003, 2:27 AM

Paul

Veteran (19537 posts)

Apr 7, 2003, 2:27 AM

Post #4 of 4

Shortcut

Re: [long327] SQL problem In reply to

Regarding your bool problem, you need to create two conditions. You create the first with the OR bool and then when you create the second with AND, you pass the first condition in as an argument along with your conditions...eg..

my $second = GT::SQL::Condition->new($first, 'Foo', '=', 'Bar' ... );