How to get rid of duplicate joines with intersecting ForeignKey in Django?

Asked bystefano

Noticed such a nasty thing. Suppose there is a model that is connected by two others with the same ForeignKey.

class File(models.Model):<br/>
&nbsp;&nbsp;&nbsp;&nbsp;#some stuff<br/>
&nbsp;&nbsp;&nbsp;&nbsp;pass<br/>
 <br/>
class ServerFile(models.Model):<br/>
&nbsp;&nbsp;&nbsp;&nbsp;<b>file = models.ForeignKey('File')</b><br/>
&nbsp;&nbsp;&nbsp;&nbsp;#some stuff<br/>
 <br/>
class UserFile(models.Model):<br/>
&nbsp;&nbsp;&nbsp;&nbsp;<b>file = models.ForeignKey('File')</b><br/>
&nbsp;&nbsp;&nbsp;&nbsp;#some stuff<br/>
<br/>
class Link(models.Model): <br/>
&nbsp;&nbsp;&nbsp;&nbsp;user_file = models.ForeignKey('UserFile')<br/>
&nbsp;&nbsp;&nbsp;&nbsp;server_file = models.ForeignKey('ServerFile')<br/>
&nbsp;&nbsp;&nbsp;&nbsp;#some stuff


Accordingly, when list_select_related is enabled, we get an additional join on File

SELECT

`fff_link`.`id`,

`fff_link`.User_file_id`,

`fff_link`.`server_file_id`,

`fff_userfile`.`id`,

`fff_userfile`.`file_id`,

`fff_file`.`id`,

`fff_serverfile`.`id`,

`fff_serverfile`.`file_id`,

T5.`id`

FROM `fff_link`

INNER JOIN `fff_userfile`

ON (`fff_link`.`user_file_id` =` fff_userfile`id`id`)

INNER JOIN `fff_file`

ON (`fff_userfile`.`file_id` =` fff_file`.`id`)

INNER JOIN `fff_serverfile`

ON (`fff_link`.`server_file_id` =` fff_serverfile`.`id`)

INNER JOIN `fff_file` T5

ON (`fff_serverfile`.`file_id` = T5.`id`)

Can anyone come across? How to treat?

Refusal to intersect is not an option, naturally django

Comments:
Yes, we get. The condition of joins is different. What is the problem? - nolly
Additional communication - & gt; extra load - & gt; extra lead time. In admin.py, do not specify select_related ('bla-bla') - arukiyomi
See EXPLAIN ANALYZE with and without JOIN. In PostgreSQL, the difference is minimal, but in PostgreSQL 9.0, the extra JOINs should be automatically removed altogether (however, I didn’t check, since I haven’t yet managed to install 9.0). - hungergameslover

Answers

carlos aranaga
And why treat? In this case, the JOIN is absolutely adequate.
Replies:
This is a highly simplified example. On one table 2, similar retries in a request for large amounts of data cause wild brakes. - mary severson
Well, how would you write a query with one JOIN on file? You need to pull two different file objects for a single link object.
I'm afraid to scare, but is everything ok with indexes? - angeline fortin
Indices on foreign keys automatically generated, EXPLAIN showed them.
The fact of the matter is that file is the same and there is no other way to group objects. This is so to say a different embodiment of the same object. That as the user sees the file, and that where it is really. - heather goodman
Add one more link to the model. - townsend
How to run your favorite hammerfight under Ubuntu? :: Is it possible to migrate Virtual Box VM through OVF without any problems? :: How to choose a random entry from the MySQL database without using a primary key and order by rand () :: Help me choose a tablet PC on Android OS worth up to 200 $ :: Find IP address of the resource in the domain .RF?
Leave Repply forHow to get rid of duplicate joines with intersecting ForeignKey in Django?
Useful Links