Gossamer Forum
Quote Reply
GROUP BY
Im trying to use grouping in the mylinks plugin and either I'm doing it wrong or there is a bug. Basically I kept getting errors so I tried printing $GT::SQL::error and the group clause had no table prefix causing the error. eg....

Failed to execute query: '
SELECT *
FROM lsql_CatLinks, lsql_Links
WHERE lsql_CatLinks.LinkID = lsql_Links.ID AND (lsql_Links.ID == '1'))
GROUP BY CatLinks.CategoryID
' Reason: Unknown table 'CatLinks' in group statement at e:/apache/cgi-bin/admin/Plugins/MyLinks_DB.pm line 67.

...as you can see the GROUP BY clause doesn't have the table prefix lsql_

Looking at it, I assume it is because the select_options method requires you to manually enter the clause rather than it being auto-generated....so how can I make it use the correct prefix?

Last edited by:

Paul: Mar 17, 2002, 5:01 AM
Quote Reply
Re: [Paul] GROUP BY In reply to
Im guessing that isn't right anyway as it only selects one link with the code I have and doesn't group. Time to visit mysql.com :(

Code:
require GT::SQL::Condition;

my $cond = GT::SQL::Condition->new('Links.ID', 'IN', \@list);
my $tab = $DB->table('Links','CatLinks');

$tab->select_options("GROUP BY lsql_CatLinks.CategoryID");

my $sth = $tab->select( $cond );
while (my $rec = $sth->fetchrow_hashref) {
$tags->{saved_links} .= Links::SiteHTML::display('mylinks_link', $rec );
}

Last edited by:

Paul: Mar 17, 2002, 5:20 AM
Quote Reply
Re: [Paul] GROUP BY In reply to
Did you try grouping by the field name alone, ie: CategoryID ?


PUGDOG´┐Ż Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [Paul] GROUP BY In reply to
Hi,

Yes, currently select_options() are not prefixed. So you can either do:

my $prefix = $DB->prefix;
$tab->select_options ("GROUP BY " . $prefix . "CatLinks.CategoryID");

or:

$tab->select_options ("GROUP BY CategoryID");

Since the column is not in both tables, you don't need to prefix it with table name.

Cheers,

Alex
--
Gossamer Threads Inc.

Last edited by:

Alex: Mar 17, 2002, 1:53 PM
Quote Reply
Re: [Alex] GROUP BY In reply to
Ah yes...the second option looks good thanks.
Quote Reply
Re: [Paul] GROUP BY In reply to
By the way, what are you trying to select?

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] GROUP BY In reply to
Well Im hoping you can help solve the select problem as it is only selecting one link currently.

I'm not sure I have this right. I have a new table...mylinks:

UserID LinkID

.....so first I select all "favorites" for the logged in user and store all the link id's in @list, then Im trying to select the links from the links table using IN but group them by the category they reside in, in the directory.

Last edited by:

Paul: Mar 17, 2002, 2:02 PM
Quote Reply
Re: [Paul] GROUP BY In reply to
Hi,

Try:

Code:
my $sth = $DB->table('Links', 'CatLinks', 'mylinks')->select( UserID => $USER->{Username} );
my %grouped_results;
while (my $link = $sth->fetchrow_hashref) {
push @{$grouped_results{$link->{CategoryID}}}, $link;
}


and now you have a hash of array refs containing the links:

Code:
(
5 => [$link1, $link2, $link3],
3 => [$link6, $link4, $link8],
...
)


Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] GROUP BY In reply to
It's so obvious when someone does it for you Blush

Thanks thats just what I need!
Quote Reply
Re: [Alex] GROUP BY In reply to
Hi,

Oops that doesn't work. It selects everything from the links table owned by my username rather than everything from the mylinks table.
Quote Reply
Re: [Alex] GROUP BY In reply to
The following seems to work:

Code:
my %grp;
my $tab = $DB->table('MyLinks');
my @list = $tab->select( { UserID => $USER->{Username} } )->fetchall_list;
my $table = $DB->table('Links','CatLinks');

$table->select_options("LIMIT $low,$mh");

my $sth = $table->select( { ID => \@list } );
while (my $rec = $sth->fetchrow_hashref) {
push @{$grp{$rec->{CategoryID}}}, Links::SiteHTML::display('mylinks_link', $rec );
}

for (keys %grp) {
$tags->{saved_links} .= join '', @{$grp{$_}};
}

Last edited by:

Paul: Mar 19, 2002, 4:33 AM