[jifty-devel] Table join and "order by" a column of an external table

[Agent Zhang]

Hello,

I've been trying very hard to translate the following SQL query to
Jifty::DBI's terms:

    select *
    from messages, sessions
    where messages.msg_session = sessions.id and
          messages.content like "%FOO%"
    order by sessions.begin_time desc, messages.session_offset asc;

I had been expecting the following Perl code to work, but it didn't:

    # $self is a Qooqle::Model::MessageCollection object
    my $alias = $self->join(
        column1 => 'msg_session',
        table2 => 'sessions', column2 => 'id');
    $self->limit(
        column => 'content', value => "%FOO%", operator => 'LIKE',
    );
    $self->order_by(
        {alias => $alias, column => 'begin_time', order => 'DESC'}
        {column => 'session_offset', order => 'ASC'},
    );

Jifty::DBI::Collection generated something very weird for the "order by" clause:

    ORDER BY main.session_offset ASC, sessions_1.min(begin_time) DESC

And my PostgreSQL reported: 'ERROR:  schema "sessions_1" does not
exist'. Oh well...I'm guessing the problem is that begin_time is not a
column of the current table.

Yeah, I know table joining has been discussed several times on this
list before, but...any solutions to this problem? ;-)

Cheers,
Agent