1

I have a table User_Device with fields username, num_of_years and device (foreign key), table Device with fields id, name, type and many to many relationship on field property, which is the third table Property that has fields id, name, location. I know how to get all the fields from the first 2 tables:

User_Device.objects.select_related('device').all()

I also know how to get all the fields of the many to many table:

Device.property.through.objects.all()

or filter using:

Device.property.through.objects.filter(property=12)

which gives me all the fields of the many to many table alone, which looks like Device_Property with fields device_id, property_id

I have 2 questions:

  1. How do I get all fields of the Device table and the property table like the SQL query would give when you inner join it like: select d.*,p.* from device_property dp inner join property p on dp.property_id=p.id inner join device d on dp.device_id=d.id

  2. How to inner join all the three tables and get all fields like the result of the query:

    select ud.*,d.*,p.* from user_device ud inner join device_property dp on ud.device_id = dp.device_id inner join property p on dp.property_id=p.id inner join device d on dp.device_id=d.id

In conclusion table A has a foreign key field on table B and table B has a many to many relationship with table C (NOT foreign key). How do I get ALL fields of table A, B, C

Community
  • 1
  • 1
Hyuga Hinata
  • 47
  • 8
  • Please do *not* think in terms of *tables* but in terms of *models*. The second query is not a good idea, since it *repeats* the data for the `device` and `device_property` recods, and thus will result in a *massive* amount of data hat contains a *huge* amount of duplicates. In order to fetch a one-to-many or many-to-many, you use a `.prefetch_related`, which will make one query to then fetch the related objects, and do the joining at the Python/Django layer. – Willem Van Onsem Mar 20 '20 at 19:20
  • I tried using prefetch related but it wasn't selecting any of the columns of the many_to_many related model – Hyuga Hinata Mar 20 '20 at 19:21
  • @HyungaHInata: exactly, and that is because of the reason stated above: it could simply result in a huge amount of bandwidth used to repeat the same data again and again. `.prefetch_related` makes a second query, but results in limited bandwidth. – Willem Van Onsem Mar 20 '20 at 19:23
  • Thank you, I understand the negatives of doing that, but just for the purpose of understanding Django better, IS there a way I could technically get all columns of all these 3 tables? – Hyuga Hinata Mar 20 '20 at 19:24
  • I'm facing the same problem stated above, please help me also @HyugaHinata – vikash Aug 09 '22 at 09:50
  • @vikash I don't think I got around to finding out how to achieve this the "right" way. I suppose you can always write raw SQL to achieve this or write a nested ORM query. I don't remember what happens if you try to explicitly specify the columns you want using `.values_list()` Maybe you could try that? – Hyuga Hinata Aug 11 '22 at 03:45

0 Answers0