Translating a SQL query to Django ORM with self joins

n01 Source

I want to grab a list of players with player's id and their player_attribute values (more than one) in a single response like {player_id: 1, first_value: 2, last_value: 3}, I can replicate this in Flask and peewee/sqlalchemy but can't figure it out with Django.

I have a Query like this:

SELECT DISTINCT p.player_id, fn.value, ln.value
FROM player p
INNER JOIN player_attributes fn ON fn.player_id=p.player_id
AND fn.attribute_id=123
and fn.end_time is NULL
INNER JOIN player_attributes ln ON ln.player_id=p.player_id
AND ln.attribute_id=456
and ln.end_time is NULL
WHERE p.player_type_id = 789
ORDER BY ln.value, fn.value

With models:

class PlayerAttributes(models.Model):
    player_attribute_id = models.AutoField(primary_key=True)

    value = models.CharField(max_length=255, blank=True, null=True)
    end_date = models.IntegerField(blank=True, null=True)
    player = models.ForeignKey(
        player,
        models.DO_NOTHING,
        related_name="player_attributes"
    )
    attribute = models.ForeignKey(
        Attribute,
        models.DO_NOTHING,
        related_name="player_attributes"
    )

class Player(models.Model):
    player_id = models.AutoField(primary_key=True)

class Attribute(models.Model):
    attribute_id = models.AutoField(primary_key=True)

I tried to prefetch it with a QuerySet (?) and did not get my JOINs in the resulted query when I printed q.query.

This is my current code:

first_attribute = PlayerAttributes.objects.filter(
    end_time=None, attribute=123
)
last_attribute = PlayerAttributes.objects.filter(
    end_time=None, attribute=456
)

players = (
    Player
    .objects
    .filter(
        player_type=789
    )
    .prefetch_related(
        Prefetch(
            "player_attributes", queryset=first_attribute,           to_attr="first_attribute"
    )
)
.prefetch_related(
    Prefetch(
        "player_attributes", queryset=last_attribute,     to_attr="last_attribute"
        )
    )
    .values("player_id", "first_attribute__value", "last_attribute__value")
)

return players

I tried reading into select_related and prefetch_related but unsure which to try? I'm using Django 2.0 if that matters.

If anyone could point me to the write method(s) to use or the documentation to look at (I got confused at which to look for/at) and this is my first time with Django (usually use Flask)

Thank you in advance!

pythonmysqldjangoormdjango-orm

Answers

comments powered by Disqus