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

Agent Zhang agentzh at gmail.com
Tue Dec 12 23:21:59 EST 2006


On 12/12/06, Henry Baragar <Henry.Baragar at instantiated.ca> wrote:
> >
> 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.
>

Yes, your code works great when using SQLite as the backend (except
for s/->alias/->new_alias/). And I think all your "main"s can be
omitted. :)

However, I'm still getting the same error while using PostgreSQL 8.1:

WARN - DBD::Pg::st execute failed: ERROR:  schema "sessions_1" does not exis

And the SQL statement for PostgreSQL generated by Jifty::DBI is very
very different from that for SQLite:

WARN - Qooqle::Handle=HASH(0x3902ff4) couldn't execute the query 'SELECT main.id
 as main_id, main.sent as main_sent, main.sender as main_sender, main.receiver a
s main_receiver, main.content as main_content, main.msg_session as main_msg_sess
ion, main.session_offset as main_session_offset FROM ( SELECT main.id FROM messa
ges main , sessions sessions_1  WHERE ((LOWER(main.content) LIKE
LOWER('%clover%'))) AND ((main.msg_session = sessions_1.id))   GROUP
BY main.id   ORDER BY sessions_1.min(begin_time) DESC,
main.session_offset ASC  ) distinctquery, messages main WHERE (main.id
= distinctquery.id) LIMIT 25' at E:/Perl/site/lib/Jifty/DBI/
Handle.pm line 501.

It seems to be a Jifty::DBI bug. Any idea?

Agent


More information about the jifty-devel mailing list