[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