Deadly Deadly - 8 days ago 5
Python Question

Django ORM SELECT with join

ERR

Models in django:

class Key(models.Model):
id = models.AutoField(primary_key=True, blank=True)
name = models.CharField(max_length=50)


class Record(models.Model):
id = models.AutoField(primary_key=True, blank=True)
project_id = models.IntegerField()
name = models.CharField(max_length=50)


class Value(models.Model):
id = models.AutoField(primary_key=True, blank=True)
record = models.ForeignKey(Record)
key = models.ForeignKey(Key)
value = models.CharField(max_length=255)


I need to select from DB this data:

NAME (from record)
and fields related with this record
[NAME (from key), VALUE (from value)]
[NAME (from key), VALUE (from value)]
[...]


May I use django ORM to make this selection? (for example in SQL select looks like this)

SELECT
`keeper_record`.`id` AS `record_id`,
`keeper_record`.`name` AS `name`,
`keeper_record`.`desc` AS `desc`,
`keeper_key`.`name` AS `key_name`,
`keeper_key`.`desc` AS `key_desc`,
`keeper_value`.`value` AS `value_value`
FROM `keeper_record`
JOIN `keeper_value` ON `keeper_record`.`id` = `keeper_value`.`record_id`
JOIN `keeper_key` ON `keeper_key`.`id` = `keeper_value`.`key_id`
WHERE record_id = id

Answer

The following selects the values related to particular record id. You can then follow the foreign keys to get the related record and key. Using select_related minimises database lookups.

# Select all values related to a record in your view
record = Record.objects.get(pk=record_id)
values = Value.objects.filter(record=record).select_related()

# In your template
{% for value in values %}
{{ value.record.name }} - {{ value.key.name }} - {{ value.value }}
{% endfor %}

Selecting more that one record

In your sql, you had WHERE record_id = 1, so I showed how to get all the values for a particular record. You could also select the values for more that one record in one query.

# filter all records which belong to the project with `project_id=1`
records = Record.objects.filter(project_id=1)
# select all values that belong to these records
values = Value.objects.filter(record__in=records).select_related().order_by('record')