Gossamer Forum: Products: Gossamer Links: Development, Plugins and Globals: Complex query :(

Sep 20, 2003, 5:09 AM

Andy

Veteran / Moderator (18436 posts)

Sep 20, 2003, 5:09 AM

Post #1 of 9

Shortcut

Complex query :(

I am having real trouble here getting this to work :( I need to use the OR option, so I can select from Multiple categories...BUT, I need the Live_Date and isValidated to also equal their respective values. The code I am using is;

Code:
 # figure out the date for this link... 
  my $date_ = GT::Date::date_get(); 
  my $date1 = GT::Date::date_sub($date_,1); 
  my $date2 = GT::Date::date_sub($date_,2); 
  my $date3 = GT::Date::date_sub($date_,3); 
  my $date4 = GT::Date::date_sub($date_,4); 
  my $date5 = GT::Date::date_sub($date_,5); 
  my $date6 = GT::Date::date_sub($date_,6); 
  my $date7 = GT::Date::date_sub($date_,7); 

 # setup DB connections... 
  my $db_con = $DB->table("CatLinks","Links"); 
  my $cond   = GT::SQL::Condition->new( 
      'CategoryID', '=', '2', 
      'CategoryID', '=', '16', 
      'CategoryID', '=', '23', 
      'CategoryID', '=', '24', 
      'CategoryID', '=', '25', 
      'CategoryID', '=', '26', 
      'CategoryID', '=', '28', 
      'CategoryID', '=', '30', 
      'CategoryID', '=', '31', 
      'CategoryID', '=', '32', 
      'CategoryID', '=', '33', 
      'CategoryID', '=', '34', 
      'CategoryID', '=', '35', 
      'CategoryID', '=', '36', 
      'CategoryID', '=', '37', 
      'Live_Date'    ,'=', $date1, 
      'Live_Date'    ,'=', $date2, 
      'Live_Date'    ,'=', $date3, 
      'Live_Date'    ,'=', $date4, 
      'Live_Date'    ,'=', $date5, 
      'Live_Date'    ,'=', $date6, 
      'Live_Date'    ,'=', $date7, 
      'isValidated'    ,'=', 'Yes' 
  ); 
  $cond->bool('or'); 

  $db_con->select_options ('ORDER BY Rating DESC','LIMIT 1');  
  my $sth    = $db_con->select( $cond ); 

  while (my $hit = $sth->fetchrow_hashref) { 
    print "ID: " . $hit->{ID} . "<BR>"; 
  }

Which translates to (from DEBUG output);

Quote:

SELECT *
FROM lsql_CatLinks, lsql_Links
WHERE lsql_CatLinks.LinkID = lsql_Links.ID AND (lsql_CatLinks.CategoryID = '2' or lsql_CatLinks.CategoryID = '16' or lsql_CatLinks.CategoryID = '23' or lsql_CatLinks.CategoryID = '24' or lsql_CatLinks.CategoryID = '25' or lsql_CatLinks.CategoryID = '26' or lsql_CatLinks.CategoryID = '28' or lsql_CatLinks.CategoryID = '30' or lsql_CatLinks.CategoryID = '31' or lsql_CatLinks.CategoryID = '32' or lsql_CatLinks.CategoryID = '33' or lsql_CatLinks.CategoryID = '34' or lsql_CatLinks.CategoryID = '35' or lsql_CatLinks.CategoryID = '36' or lsql_CatLinks.CategoryID = '37' or
lsql_Links.Live_Date = '2003-09-19' or lsql_Links.Live_Date = '2003-09-18' or lsql_Links.Live_Date = '2003-09-17' or lsql_Links.Live_Date = '2003-09-16' or lsql_Links.Live_Date = '2003-09-15' or lsql_Links.Live_Date = '2003-09-14' or lsql_Links.Live_Date = '2003-09-13' or lsql_Links.isValidated = 'Yes')
ORDER BY Hits DESC LIMIT 1

As you can see, this won't work. it seems the isValidated part is picking up links that don't match the sql_Links.Live_Date or lsql_CatLinks.CategoryID value.

Anyone got an suggestions? Unsure

I'm totally stumped on this one.

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 20, 2003, 5:18 AM

Andy

Veteran / Moderator (18436 posts)

Sep 20, 2003, 5:18 AM

Post #2 of 9

Shortcut

Re: [Andy] Complex query :( In reply to

Mmmm....is this what the outcome should be?

Quote:

SELECT ID,Live_Date,Add_Date
FROM lsql_CatLinks, lsql_Links
WHERE lsql_CatLinks.LinkID = lsql_Links.ID
AND (
lsql_CatLinks.CategoryID = '2' or
lsql_CatLinks.CategoryID = '16' or
lsql_CatLinks.CategoryID = '23' or
lsql_CatLinks.CategoryID = '24' or
lsql_CatLinks.CategoryID = '25' or
lsql_CatLinks.CategoryID = '26' or
lsql_CatLinks.CategoryID = '28' or
lsql_CatLinks.CategoryID = '30' or
lsql_CatLinks.CategoryID = '31' or
lsql_CatLinks.CategoryID = '32' or
lsql_CatLinks.CategoryID = '33' or
lsql_CatLinks.CategoryID = '34' or
lsql_CatLinks.CategoryID = '35' or
lsql_CatLinks.CategoryID = '36' or
lsql_CatLinks.CategoryID = '37')
AND (
lsql_Links.Live_Date = '2003-09-19' or
lsql_Links.Live_Date = '2003-09-18' or
lsql_Links.Live_Date = '2003-09-17' or
lsql_Links.Live_Date = '2003-09-16' or
lsql_Links.Live_Date = '2003-09-15' or
lsql_Links.Live_Date = '2003-09-14' or
lsql_Links.Live_Date = '2003-09-13')
AND
lsql_Links.isValidated = 'Yes'

Trying to enter it manually, so I know what the SQL outcome should be....but that command doesn't give any results (even though I know that links exist that *should* match this criteria)

Anyone? Unsure

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 20, 2003, 5:19 AM

yogi

Veteran / Moderator (2199 posts)

Sep 20, 2003, 5:19 AM

Post #3 of 9

Shortcut

Re: [Andy] Complex query :( In reply to

An excerpt from the documentation of GT::SQL::Condition:

Quote:

Now say we wanted something a bit more complex that would normally involve setting parentheses. We want to find users who have either first name like alex or last name like krohn, and whose employer is Gossamer Threads. We could use:

my $cond = GT::SQL::Condition->new(
'FirstName', 'LIKE', 'Alex%',
'LastName', 'LIKE', 'Krohn%'
);
$cond->bool('or');
my $cond1 = GT::SQL::Condition->new(
$cond,
'Employer', '=', 'Gossamer Threads'
);

Ivan
-----
Iyengar Yoga Resources / GT Plugins

Sep 20, 2003, 5:22 AM

Andy

Veteran / Moderator (18436 posts)

Sep 20, 2003, 5:22 AM

Post #4 of 9

Shortcut

Re: [yogi] Complex query :( In reply to

Thanks. Don't know how I missed that Unsure

Still got the problem of my query notproducing any results though :-/ Does my syntax of the SQL statement look ok?

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 20, 2003, 6:20 AM

Andy

Veteran / Moderator (18436 posts)

Sep 20, 2003, 6:20 AM

Post #5 of 9

Shortcut

Re: [Andy] Complex query :( In reply to

Ok... made these changes, and the SQL output is now;

Code:
      SELECT * 
      FROM lsql_CatLinks, lsql_Links 
      WHERE  lsql_CatLinks.LinkID = lsql_Links.ID AND 
      ( 
        (  
          lsql_CatLinks.CategoryID = '2' or  
          lsql_CatLinks.CategoryID = '16' or  
          lsql_CatLinks.CategoryID = '23' or  
          lsql_CatLinks.CategoryID = '24' or  
          lsql_CatLinks.CategoryID = '25' or  
          lsql_CatLinks.CategoryID = '26' or  
          lsql_CatLinks.CategoryID = '28' or  
          lsql_CatLinks.CategoryID = '30' or  
          lsql_CatLinks.CategoryID = '31' or  
          lsql_CatLinks.CategoryID = '32' or  
          lsql_CatLinks.CategoryID = '33' or  
          lsql_CatLinks.CategoryID = '34' or  
          lsql_CatLinks.CategoryID = '35' or  
          lsql_CatLinks.CategoryID = '36' or  
          lsql_CatLinks.CategoryID = '37'  
        ) AND (  
          lsql_Links.Live_Date = '2003-09-19' or  
          lsql_Links.Live_Date = '2003-09-18' or  
          lsql_Links.Live_Date = '2003-09-17' or  
          lsql_Links.Live_Date = '2003-09-16' or  
          lsql_Links.Live_Date = '2003-09-15' or  
          lsql_Links.Live_Date = '2003-09-14' or  
          lsql_Links.Live_Date = '2003-09-13' 
        )  

        AND lsql_Links.isValidated = 'Yes' 
      ) 

      ORDER BY Rating DESC LIMIT 1

Problem with this though, is that it gives me no output Unsure

I *know* there are results that should be grabbed for this.

What am I doing wrong ?

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 20, 2003, 8:13 AM

Andy

Veteran / Moderator (18436 posts)

Sep 20, 2003, 8:13 AM

Post #6 of 9

Shortcut

Re: [Andy] Complex query :( In reply to

Anyone? This problem is still bugging me :(

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 20, 2003, 9:14 AM

Andy

Veteran / Moderator (18436 posts)

Sep 20, 2003, 9:14 AM

Post #7 of 9

Shortcut

Re: [Andy] Complex query :( In reply to

Ok...managed to kinda get this sorted. Just added some extra codes, and now I get a IS Error. Code is;

Code:
  my $cond   = GT::SQL::Condition->new( 
      'CategoryID', '=', '2', 
      'CategoryID', '=', '7', 
      'CategoryID', '=', '16', 
      'CategoryID', '=', '23', 
      'CategoryID', '=', '24', 
      'CategoryID', '=', '25', 
      'CategoryID', '=', '26', 
      'CategoryID', '=', '28', 
      'CategoryID', '=', '30', 
      'CategoryID', '=', '31', 
      'CategoryID', '=', '32', 
      'CategoryID', '=', '33', 
      'CategoryID', '=', '34', 
      'CategoryID', '=', '35', 
      'CategoryID', '=', '36', 
      'CategoryID', '=', '37' 
  ); 
  $cond->bool('or');

Keep getting this error;

[Sat Sep 20 12:11:22 2003] update_days2.cgi: syntax error at update_days2.cgi line 569, near "'"
[Sat Sep 20 12:11:22 2003] update_days2.cgi: Bad name after CategoryID' at update_days2.cgi line 569.

Can anyone see a problem with this code? Maybe I've been working too long today Frown

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 20, 2003, 2:09 PM

webslicer

User (236 posts)

Sep 20, 2003, 2:09 PM

Post #8 of 9

Shortcut

Re: [Andy] Complex query :( In reply to

Andy;

There is a section covering the GT complex queries in the help as Yogi pointed out. I've read it and it offers a technique or two you are not using. I think you multiple AND's don't work. I had to experiment...
It DOES work to take the result of one and add it to the next , so check that out, as that is what Gossamer suggests.
You also can use the set operators...

the IN command (quoted below), and the LIKE command I think.

******* Gossamer Help ****
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')

Empty lists will get ignored. This is also very useful for list handling list of id numbers. Strings will automatically be quoted, whereas integers will be left unquoted.
***** Gossamer Help: stacking conditions *********
Now say we wanted something a bit more complex that would normally involve setting parentheses. We want to find users who have either first name like alex or last name like krohn, and whose employer is Gossamer Threads. We could use:

my $cond = GT::SQL::Condition->new(
'FirstName', 'LIKE', 'Alex%',
'LastName', 'LIKE', 'Krohn%'
);
$cond->bool('or');
my $cond2 = GT::SQL::Condition->new(
$cond1,
'Employer', '=', 'Gossamer Threads'
);
***************************************
I ended up having to do:
%Variable%

regards!

Sep 20, 2003, 2:12 PM

Andy

Veteran / Moderator (18436 posts)

Sep 20, 2003, 2:12 PM

Post #9 of 9

Shortcut

Re: [webslicer] Complex query :( In reply to

Nope.. my code was working... Blush

Turned out the link I was thinking exists in another category didn't exist as I thought it did :(

Cheers

Andy (mod)
andy@ultranerds.co.uk