[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