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

[Henry Baragar]

On Tuesday, December 12 2006 02:58 am, Agent Zhang wrote:
> 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'},
>     );
>
Untested, but transcripted from working code:

    my $alias = $self->alias("sessions");
    $self->join(
        alias1  => "main",      column1 => "msg_session",
        alias2  => $alias,       column2 => "id",
    );
    $self->limit(
        column => "content", operator => "like", value => '%FOO%',
    );
    $self->order_by(
        {alias => $aliase, column => 'begin_time', order => 'DESC'},
        {alias => 'main', column => 'session', order => 'ASC'},
    );

I am not sure which of the 'main's can be omitted.

Regards,
Henry


> 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
> _______________________________________________
> jifty-devel mailing list
> jifty-devel at lists.jifty.org
> http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel