[jifty-devel] Jifty::DBI order_by unclear
Agent Zhang
agentzh at gmail.com
Mon May 28 19:51:05 EDT 2007
On 5/28/07, Andreas J. Koenig <andreas.koenig.7os6VVqR at franz.ak.mind.de> wrote:
> I wonder if I am the only one who has difficutlties understanding the
> order_by clause or the only one who wants to use it with joined
> tables.
I used to have problems with order_by + join with the PostgreSQL
backend long time ago but I think I've already fixed it :)
>
> I have two tables: sessions and testruns. Testruns happen within a
> session, so there is a 1:N relation of sessions to testruns. Testrun's
> schema contains:
>
It seems you're doing exactly what I did in my Qooqle application,
which is a QQ chatting log search engine based on Jifty:
http://svn.berlios.de/svnroot/repos/unisimu/Qooqle/
In this project, I have Sessions and Messages where Messages happen
within each Session, so there is also a 1:N correspondence. my
Message's schema looks like this:
use Qooqle::Record schema {
column sent =>
type is 'timestamp',
label is 'Sent time',
is mandatory;
[...]
column msg_session =>
refers_to Qooqle::Model::Session by 'id',
#render_as 'Text',
is mandatory;
column session_offset =>
type is 'integer',
label is 'Offset in its group',
validator is sub { $_[0] >= 0; },
is mandatory;
};
And my Session is
use Qooqle::Record schema {
column begin_time =>
type is 'timestamp',
label is 'Begin time',
is mandatory;
[...]
}
>
> This worked as expected intuitively very well until I wanted to sort a
> list of testruns by the session's starttime.
Yes, I also did search messages by Session's begin_time and *then* by
the messages' "offset" within their sessions :) The code looks like
this:
package Qooqle::Model::MessageCollection;
[...]
sub search ($$) {
my ($self, $keys) = @_;
[...]
my $sessions = $self->new_alias('sessions');
$self->join(
column1 => 'msg_session',
alias2 => $sessions, column2 => 'id');
[...] # construct the "where clause" here
$self->order_by(
{alias => $sessions, column => 'begin_time', order => 'DESC'},
{column => 'session_offset', order => 'ASC'},
);
}
I must admit the Jifty::DBI code is not very intuitive and nontrivial
to get right. Keep the pseudo SQL code in mind helped me a lot at the
time of writing the stuff above:
select *
from messages as main, sessions as sessions_1
where [...]
order by sessions_1.begin_time desc, session_offset asc
Of cause, the actual SQL code generated by Jifty::DBI is much much
more complicated and differs among the various DBMS backends.
You may want to look into
http://svn.berlios.de/svnroot/repos/unisimu/Qooqle/lib/Qooqle/Model/MessageCollection.pm
for the full version of the search method of my MessageCollection.pm.
Hope this helps :)
agentz
P.S. Qooqle has been tested successfully against SQLite, mysql, and
PostgreSQL :)
More information about the jifty-devel
mailing list