Join tables and retrieve columns in Django

I have two tables (with corresponding models):

Mapping
id  parent_id  child_id  child_qty
1   1          1         5
2   1          2         3
3   1          4         4
4   2          1         3
5   2          3         2

Child
id  name   property
1   name1  prop1
2   name2  prop2
3   name3  prop3
4   name4  prop4

Note: in Mapping model, 'child' is defined as a ForeignKey on Child model, that automatically creates 'child_id' in Mapping table.

I want to write a join:

SELECT mt.child_id, mt.child_qty, ct.name, ct.property
FROM mapping mt, child ct
WHERE mt.parent_id = 1;

that gives result:

child_id  child_qty  name   property
1         5          name1  prop1
2         3          name2  prop2
4         4          name4  prop4

I wrote this:

my_qs = Mapping.objects.select_related('child').filter(parent_id=1).values()
print(my_qs)
# [{'id':1, 'parent_id': 1, 'child_id': 1, 'child_qty': 5},
#  {'id':2, 'parent_id': 1, 'child_id': 2, 'child_qty': 3},
#  {'id':3, 'parent_id': 1, 'child_id': 4, 'child_qty': 4}]

What do I modify in my ORM query so that:

  1. I don't see 'id' and 'parent_id' in the output
  2. I see 'ct.name and ct.property' in the output (with our without 'ct.' prefix)

try this 

 my_qs = Mapping.objects.filter(parent_id=1).values('child_id', 'child_qty', 'child__name', 'child__property')

print(my_qs)
Back to Top