[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:


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

package Qooqle::Model::MessageCollection;


sub search ($$) {
    my ($self, $keys) = @_;
    my $sessions = $self->new_alias('sessions');
        column1 => 'msg_session',
        alias2  => $sessions, column2 => 'id');
    [...]  # construct the "where clause" here
        {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


for the full version of the search method of my MessageCollection.pm.

Hope this helps :)


P.S. Qooqle has been tested successfully against SQLite, mysql, and
PostgreSQL :)

More information about the jifty-devel mailing list