'to_field' breaks lookups that span relationships

[shwag]

class Website(models.Model):
    id = models.IntegerField(primary_key=True)
    dfp_id = models.IntegerField()
    name = models.CharField(maxlength=300)
    url = models.CharField(maxlength=150)

class Report(models.Model):
    id = models.IntegerField(primary_key=True)
    site = models.ForeignKey(Website, to_field='dfp_id')

444549 is a valid dfp_id in the Website model.

Report.objects.filter(site=444549)  <-- Returns results. good.
Report.objects.filter(site__id=444549)  <-- Returns results, but it shouldn't.
Report.objects.filter(site__dfp_id=444549)  <-- No results, but it should.

130 is the valid id for a Website.

Report.objects.filter(site__id=130) <- No results, no work around.
Report.objects.filter(site__name='Validname') <- Also no results.
Report.objects.filter(site__url='validurl.com') <- Also no results

[Steven Wagner <stevenwagner@…>]

class Website(models.Model):
    id = models.IntegerField(primary_key=True)
    dfp_id = models.IntegerField()
    name = models.CharField(maxlength=300)
    url = models.CharField(maxlength=150)

class Report(models.Model):
    id = models.IntegerField(primary_key=True)
    site = models.ForeignKey(Website, to_field='dfp_id')

444549 is a valid dfp_id in the Website model.

Report.objects.filter(site=444549)  <-- Returns results. good.
Report.objects.filter(site__id=444549)  <-- Returns results, but it shouldn't.
Report.objects.filter(site__dfp_id=444549)  <-- No results, but it should.

130 is the valid id for a Website.
Report.objects.filter(site__id=130) <- No results, no work around.
Report.objects.filter(site__name='Validname') <- Also no results.
Report.objects.filter(site__url='validurl.com') <- Also no results

[Steven Wagner <stevenwagner@…>]

I applied the patch from bug 4088 (​http://code.djangoproject.com/attachment/ticket/4088/newcolumn.diff), and that fixed one case.
Report.objects.filter(sitedfp_id=444549). It fixes the inner join.

FROM reports
INNER JOIN websites AS reports__site ON reports.site_id = reports__site.id
WHERE (reports__site.dfp_id = 444549)

FROM reports
INNER JOIN websites AS reports__site ON reports.site_id = reports__site.dfp_id
WHERE (reports__site.dfp_id = 444549)

It fixed all cases I defined above except for one.

Report.objects.filter(siteid=444549) <-- Returns results, but it shouldn't.

when I look in my mysql.log, I find the query it tried does not use a join at all even though it should.
Select ..
FROM reports WHERE (reports.site_id = 130)

[Steven Wagner <stevenwagner@…>]

Argh, formatting. 
I applied the patch from bug 4088 (http://code.djangoproject.com/attachment/ticket/4088/newcolumn.diff), and that fixed ALMOST ALL cases.
This is fixed, Report.objects.filter(site__dfp_id=444549). The inner join is correct now.

FROM `reports` 
INNER JOIN `websites` AS `reports__site` ON `reports`.`site_id` = `reports__site`.`id` 
WHERE (`reports__site`.`dfp_id` = 444549)

FROM `reports` 
INNER JOIN `websites` AS `reports__site` ON `reports`.`site_id` = `reports__site`.`dfp_id` 
WHERE (`reports__site`.`dfp_id` = 444549)

This only case that is still broken is if trying to access the related column when the column name is 'id'

Report.objects.filter(site__id=444549)  <-- Returns results, but it shouldn't.

when I look in my mysql.log, I find the query it tried does not use a join at all even though it should.
Select ..
FROM `reports` WHERE (`reports`.`site_id` = 130)

[Steven Wagner <stevenwagner@…>]

This comment will describe the only remaining problem about the patch from bug 4088 is applied.

Here is the case that is still broken in Django.

class Website(models.Model):
    id = models.IntegerField(primary_key=True)
    dfp_id = models.IntegerField()
    name = models.CharField(maxlength=300)
    url = models.CharField(maxlength=150)

class Report(models.Model):
    id = models.IntegerField(primary_key=True)
    site = models.ForeignKey(Website, to_field='dfp_id')



Report.objects.filter(site=444549)
  SELECT ...
  FROM `reports` WHERE (`reports`.`site_id` = 444549)   (Correct)

Report.objects.filter(site__id=444549)
  SELECT ...
  FROM `reports` WHERE (`reports`.`site_id` = 444549)   (Incorrect)

Report.objects.filter(site__dfp_id=444549)
  SELECT ...
  FROM `reports` INNER JOIN `websites` AS `reports__site`
    ON `reports`.`site_id` = `reports__site`.`dfp_id` 
  WHERE (`reports__site`.`dfp_id` = 444549)       (Correct)

[Steven Wagner <stevenwagner@…>]

The first query looks fine.

The second query generated is the same as the first, which is incorrect. It should be generating a join to the website table on the id column similar to query 3.

The third query looks fine, and achieves the same result as the first but also uses a join.

[Malcolm Tredinnick]

Fixed description formatting.