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

[Agent Zhang]

On 12/13/06, Jesse Vincent <jesse at bestpractical.com> wrote:
>
> 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.
>

After a few hours' work, I've committed a patch to
Jifty::DBI::Handle::Pg, which should have fixed the "order by"
problem:

    http://svn.jifty.org/index.cgi/jifty/revision?rev=2386

The old distinct_query sub generated SQL code like this:

    order by foo.min(bar) asc

which was totally wrong in syntax. Also, for columns in "main", if
they are not listed in the "group by" clause, they should be put into
an aggregate function like "min".

Please look at t/14handle-pg.t for my test cases.

Writing unit tests for animals like Jifty::DBI is nontrivial at all.
:(  But writing tests *is* an excellent way to study Jifty's
internals. ;-)

>From this hack I've seen how ugly the way PostgreSQL programmers deal
with distinct search everyday is. Fortunately, Jifty::DBI is designed
to take care of this issue transparently.

Given my knowledge of Jifty::DBI's inside, I could have underestimated
the side-effects of my changes. Please let me know if I've done
something wrong. :)

Thanks!
Agent