CODEWITHSUNDEEP
sqldjango
sejje
sejje

Reputation: 99

Django equivalent to SQL statement? Also, working with RawQuerySets?

I'm running into the not-so-well-documented conundrum where you need to GROUP BY and ORDER BY at the same time.

EDIT to include some models:

class Matchup(models.Model):
    player = models.ForeignKey(Player)
    opponent = models.ForeignKey(Player)
    score = models.IntegerField()
    match = models.ForeignKey(Match)

class Match(models.Model):
    game = models.CharField()

I think I've included everything important. I'm trying to make a leaderboard, where each player can only have one entry. Therefore I need to group_by Player, and order_by('-skill'). I messed around with distinct() for quite a while with no good results.

After hours of trial-and-error I've arrived at a 90% solution, by executing raw SQL:

SELECT * FROM (SELECT * FROM scores ORDER BY score DESC) AS temp_table GROUP BY user ORDER BY score DESC

Admittedly I don't wholly understand the SQL (mainly, why is the 2nd ORDER BY necessary?)

1) Is this the best way? I could not return the proper result sets using the Django ORM, but it's very probable I was just doing a poor job.

2) I'd like to filter further by a foreignkey relation (match.game, want to have high scores per game). Best way when working with a rawqueryset?

Upvotes: 1

Views: 258

Answers (1)

Daniel DiPaolo
Daniel DiPaolo

Reputation: 56390

On a side note first: In your example, the first ORDER BY isn't necessary. They never are in subselects, and in fact for a lesser RDBMS it would introduce an unnecessary ordering operation (I imagine most ignore this ORDER BY though). (Edit: Strictly speaking, this isn't true. However, in most usages, the ordering of the tables doesn't matter and only the order of your resultset matters. Your query actually is an interesting exception)

But what you're doing can be accomplished with the Django ORM and doesn't require raw SQL. Your SQL is kind of weird in general. Basically you can rewrite it as:

SELECT
    *
FROM
    scores
GROUP BY
    user
ORDER BY
    score DESC

The subselect isn't really doing anything (ordering isn't important for the source data, only in the output), so it just goes away.

So in this case, for the Django ORM part, you can just use the aggregation features to accomplish what you need.

edit - since you pointed out in the comments that the first ORDER BY actually does sort of have an effect due to the byproduct of using SELECT *[1] without any aggregations while GROUPing, the relevant SQL would be:

SELECT
    user
    ,MAX(score) AS high_score
FROM
    scores
GROUP BY
    user
ORDER BY
    MAX(score) DESC

And again you can use the aggregation features mentioned at the link above to get the equivalent ORM statements. It'd look something like this:

User.objects.annotate(high_score=Max('scores__score')).order_by('high_score')

This assumes you've got a foreign key from Score to User (Django will figure out how to join the two to get the related field score from your Score model).

[1] - This also highlights why it's not a good practice to use SELECT * in actual code!

Upvotes: 3

Related Questions