Re: any way for ORDER BY x to imply NULLS FIRST in 8.3?

> Em Wednesday 07 November 2007 13:54:32 rihad escreveu:
>>
>> May I, as an outsider, comment? :) I really think of ASC NULLS FIRST
>> (and DESC NULLS LAST) as the way to go. Imagine a last_login column that
>> sorts users that have not logged in as the most recently logged in,
>> which is not very intuitive. I vote for sort_nulls_first defaulting to
>> false in order not to break bc.
>
> But then, when ordering by login date, you should use COALESCE and infinity
> for them
> (http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html)

It's not an easy thing to do with for example Propel 1.2 ORM (written in
PHP):

$criteria->addDescendingOrderByColumn(myPeer::LAST_LOGIN); // no place
to shove database-specific attributes in.

which was my main point.

On Friday 9. November 2007, rihad wrote:

>It's not an easy thing to do with for example Propel 1.2 ORM (written
> in PHP):
>
>$criteria->addDescendingOrderByColumn(myPeer::LAST_LOGIN); // no place
>to shove database-specific attributes in.
>
>which was my main point.

Which mainly goes to show that ORM is broken by design :-)
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

On Fri, Nov 09, 2007 at 03:17:53PM +0400, rihad wrote:
> >Em Wednesday 07 November 2007 13:54:32 rihad escreveu:
> >>
> >>May I, as an outsider, comment? :) I really think of ASC NULLS
> >>FIRST (and DESC NULLS LAST) as the way to go. Imagine a last_login
> >>column that sorts users that have not logged in as the most
> >>recently logged in, which is not very intuitive. I vote for
> >>sort_nulls_first defaulting to false in order not to break bc.
> >
> >But then, when ordering by login date, you should use COALESCE and
> >infinity for them
> >(http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html)
>
> It's not an easy thing to do with for example Propel 1.2 ORM
> (written in PHP):
>
> $criteria->addDescendingOrderByColumn(myPeer::LAST_LOGIN); // no
> place to shove database-specific attributes in.
>
> which was my main point.

If your ORM is broken as above, either fix it or do something that
isn't broken.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Nov 9, 2007 5:17 AM, rihad <rihad(at)mail(dot)ru> wrote:
> > Em Wednesday 07 November 2007 13:54:32 rihad escreveu:
> >>
> >> May I, as an outsider, comment? :) I really think of ASC NULLS FIRST
> >> (and DESC NULLS LAST) as the way to go. Imagine a last_login column that
> >> sorts users that have not logged in as the most recently logged in,
> >> which is not very intuitive. I vote for sort_nulls_first defaulting to
> >> false in order not to break bc.
> >
> > But then, when ordering by login date, you should use COALESCE and infinity
> > for them
> > (http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html)
>
> It's not an easy thing to do with for example Propel 1.2 ORM (written in
> PHP):
>
> $criteria->addDescendingOrderByColumn(myPeer::LAST_LOGIN); // no place
> to shove database-specific attributes in.

Why not create an updatable view that orders the way you want it to?

Scott Marlowe wrote:
> On Nov 9, 2007 5:17 AM, rihad <rihad(at)mail(dot)ru> wrote:
>>> Em Wednesday 07 November 2007 13:54:32 rihad escreveu:
>>>> May I, as an outsider, comment? :) I really think of ASC NULLS FIRST
>>>> (and DESC NULLS LAST) as the way to go. Imagine a last_login column that
>>>> sorts users that have not logged in as the most recently logged in,
>>>> which is not very intuitive. I vote for sort_nulls_first defaulting to
>>>> false in order not to break bc.
>>> But then, when ordering by login date, you should use COALESCE and infinity
>>> for them
>>> (http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html)
>> It's not an easy thing to do with for example Propel 1.2 ORM (written in
>> PHP):
>>
>> $criteria->addDescendingOrderByColumn(myPeer::LAST_LOGIN); // no place
>> to shove database-specific attributes in.
>
> Why not create an updatable view that orders the way you want it to?
>
>

I've already thought about this, but... there aren't yet truly updatable
views in PostgreSQL, but rather query rewriting a.k.a.
text-substitution; 1) it isn't of paramount importance in my current
case. It just wouldn't be bad to set sort_nulls_first=true, if it existed.

If you wan't to know the full story, prepare for some OT :-)
Because of the way Symfony/Propel does its "object hydration" has
already forced me to write views in postgres to minimize the amount of
data fetched: otherwise Propel is happy to fetch full-records from db,
and all its FK-related objects, too (the lazyLoad misfeature is a
two-sided gun: it pretends it fetches only this many columns for each
row, but if you later access further columns each one will cost you a
separate database hit), all of which is unacceptable for e.g. displaying
a HTML table of N items with pagination etc. Symfony/Propel is also
quite happy to hydrate the full object from db just for save()'ing it
back to db right away (on a form POST for a record update, for example),
which makes my brain hurt, so I went to the trouble of avoiding the
pre-hydration, too. This all resulted in much effort not directly
related to the business logic of my app, but rather on overriding
Symfony's way of doing everyday web-programming tasks (like form
validation, results listing, editing). Now I don't really want to work
around further design inefficiencies of Symfony/Propel by trying
updatable views. Really frustrating. Easier to just forgo any
web-framework and write quality code yourself instead, like
phpclasses.org's Manuel Lemos once said in his article... That said,
Symfony 1.1-DEV/Doctrine begin to look promising.

Em Sunday 11 November 2007 04:51:20 rihad escreveu:
> pre-hydration, too. This all resulted in much effort not directly
> related to the business logic of my app, but rather on overriding
> Symfony's way of doing everyday web-programming tasks (like form
> validation, results listing, editing). Now I don't really want to work
> around further design inefficiencies of Symfony/Propel by trying
> updatable views. Really frustrating. Easier to just forgo any
> web-framework and write quality code yourself instead, like
> phpclasses.org's Manuel Lemos once said in his article... That said,
> Symfony 1.1-DEV/Doctrine begin to look promising.

I hope it works in the next release then... It looks like this ORM is so
broken that ... I can't say how it was chosen for your project.

Anyway, you are trying to solve problems from your ORM inside your database
that has no problems. This makes things hard. And they get even harder when
you refuse to use advanced features of the database that would help solving
the problems...

I use ORMs in Python and I don't have this problem. I work with views,
functions, big tables, etc. and performance gets better and better every time
I optimize *any* of the two sides: application or database. It is always an
enhancement, as if I was coding things directly by hand.

--
Jorge Godoy <jgodoy(at)gmail(dot)com>