sorting different in postgres than in sqlite

[J]

Hello,

I developed an app for an online contest.

In this app, I requested a queryset with totals from a related table, and wanted to display the top ten participants. So, I sorted the queryset on the totals field, descending. Some participants didn't have any items, so the total for these was "None".

When using sqlite, this appeared as I wanted, with the objects having "None" at the bottom, and thus not included in the top ten.

However, after uploading this app to the production site, which has a postgres db, the None participants appeared at the top of the list (!), and THEN came the largest items successively decreasing as expected. My top-ten program failed.

Any ideas?

Thanks,
J


PS: Examples:

Developement, sqlite, working:

AR003	147
CH005	69
CO001	50
CO031	50
CH029	49
AR004	None
AR029	None

Production, postgres, not working:

CO217	None
CO024	3000
PE017	1400
VE025	988
CO013	930
PE203	628
CH016	523

[Jeff FW]

Any reason not to make the total 0 instead of None? Null in database
(or None in Python) has a special meaning, and it doesn't always make
sense to sort a list of (mostly) integers with some null values.

-Jeff

[J]

That's a good point. Thanks Jeff.

The reason they are none, or have a null value, is because there are
actually no related records in the related table.

Two Tables:
Participants (contains all potential participants)
Data (contains actual participant activity)

If there is a way to convert those nulls to zeroes, that would work
perfectly.

Here's my query, (I'm using the stable release version of django,
otherwise I'd use "annotate" which I found is present in the
development version).

contest = Participant.objects.extra(select={
'contest_stats': """
SELECT SUM(amt)
FROM contest_data
WHERE contest_data.participant_id = region_participant.id
"""
}).order_by('-contest_stats')


Thanks,
J

[Tim Chase]

> Here's my query, (I'm using the stable release version of django,
> otherwise I'd use "annotate" which I found is present in the
> development version).
>
> contest = Participant.objects.extra(select={
> 'contest_stats': """
> SELECT SUM(amt)
> FROM contest_data
> WHERE contest_data.participant_id = region_participant.id
> """
> }).order_by('-contest_stats')

I'm not sure if both postgresql and sqlite support the coalesce
function, but you might try

contest = Participant.objects.extra(select={
'contest_stats': """
Coalesce((

SELECT SUM(amt)
FROM contest_data
WHERE contest_data.participant_id =
region_participant.id

), 0)
"""
}).order_by('-contest_stats')

(for clarity, that's

Coalesce((SELECT...), 0)

which, if the SELECT returns a null result, should then fall to
the next value passed, i.e. 0.

-tim

[J]

Thank you Tim, that worked perfectly in sqlite, and I imagine it will
work well in postgres as well. My server is down right now, so I can't
say for sure, but I will definitely try this out.

Thanks again,
J

[J]

Coalesce() worked in postgres as well. Thank you.

On Jun 30, 10:34 pm, J <jobce...@gmail.com> wrote:
> Thank you Tim, that worked perfectly in sqlite, and I imagine it will

> work well inpostgresas well. My server is down right now, so I can't