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

[Jesse Vincent]

I believe the issue is as I described to someone (gugod?) on IRC
yesterday. The ::Handle::Pg code todo disitinctness should be looking to
see if the sord column is listed in the SELECT's column specification,
not just in main.*. It should be a pretty easy  fix.

-j


On Wed, Dec 13, 2006 at 12:21:59PM +0800, Agent Zhang wrote:
> 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
> _______________________________________________
> jifty-devel mailing list
> jifty-devel at lists.jifty.org
> http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
> 

--