Gossamer Forum
Quote Reply
SQL Relation question
I want to select a number of records from two tables using the GT::SQL library. The condition is that all records with the highest id in one table are selected.

To be more precise: let's say I want to select all posts by the user with the highest user_id (which is unknown to me, but anyway it is the highest value in the database). The problem is that I don't know how to select all records which belong to the highest user_id (i.e. something like MAX(user_id))

What I have now (giving all posts of somebody with a specific user_id = 123):
Code:
my $p = $DB->table ('Post');
my $p_u = $DB->table ('Post','User');
my $sth = $p_u->select ( { user_id => 123 } );

while (my $post = $sth->fetchrow_hashref) {
# do something useful here ....
}

How should I change this code to return the posts of the user with the highest user id?

Thanks a lot for your help.

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [yogi] SQL Relation question In reply to
Hi,

Easiest to do this in two queries:

my $user_id = $DB->table('user')->select('MAX(user_id)')->fetchall_list;

will give you the highest user_id in the table. Then just plug that into your code.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] SQL Relation question In reply to
Alex, thanks for your answer.

I was hoping to be able to do it in one query, because that is probably faster.

Ivan
-----
Iyengar Yoga Resources / GT Plugins
Quote Reply
Re: [yogi] SQL Relation question In reply to
Hi,

Actually, you could try adding:

$p_u->select_options('ORDER BY user_id DESC', 'LIMIT 1');

before the select. That would do it in one query.

Cheers,

Alex


--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] SQL Relation question In reply to
That's what I did first. But that actually only returns only one result (i.e. one post by the user with the highest user_id), whereas I want all posts by the latest user....

Thanks anyway.

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