Code

Opened 7 years ago

Closed 6 years ago

#4088 closed (fixed)

Not able to filter() on foreign keys.

Reported by: no@… Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords: filter foreignkey, qs-rf-fixed
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: yes Patch needs improvement: no
Easy pickings: UI/UX:

Description

I'm trying to filter by a value that is contained in a foreign key. I've seen this done in the django documentation (the blog example).
Here are my models:

class Students(models.Model):
    ssn = models.CharField(unique=True, maxlength=11)
    name = models.CharField(blank=True, maxlength=50)
    address = models.CharField(blank=True, maxlength=50)
    dob = models.DateField(null=True, blank=True)
    email = models.CharField(blank=True, maxlength=50)
    level = models.CharField(blank=True, maxlength=5)

    def __str__(self):
      return self.ssn

class Takes(models.Model):
    student = models.ForeignKey(Students, to_field='ssn', db_index=True, db_column='ssn')
    course = models.ForeignKey(Courses, to_field='cid', db_index=True, db_column='cid')
    semester = models.ForeignKey(Semesters, to_field='sname', db_index=True, db_column='sname')
    grade = models.CharField(blank=True, maxlength=2)
    
    def __str__(self):
      return "%s - %s" % (self.course.cid, self.semester.sname)

The other models aren't relevant to the question. We are just focusing on the student foreign key inside Takes.
Now, here is an example of me trying to filter by someone's name

>>> Takes.objects.filter(student__name="Kevin Smith")
[]
>>> Takes.objects.get(id=1).student.name
'Kevin Smith'
>>>

I've also tried every combination of icontains. To circumvent this, I've been using the code below (if it helps)

takes = Takes.objects.all()
takes2 = []

for x in takes:
  if (x.student.name).lower().find("kev".lower()) != -1:
    takes2.append(x)

And takes2[0].student.name returns "Kevin Smith".
Maybe I'm missing something totally, which I would love to know. But this feels like a bug to me.

Attachments (1)

newcolumn.diff (576 bytes) - added by no@… 7 years ago.

Download all attachments as: .zip

Change History (11)

comment:1 follow-up: Changed 7 years ago by Gary Wilson <gary.wilson@…>

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Accepted

I wonder if the to_field and db_column are messing things up. Can you tell if the SQL being generated by the Takes.objects.filter(student__name="Kevin Smith") is correct?

comment:2 in reply to: ↑ 1 Changed 7 years ago by no@…

That's a great idea that I didn't think of. This is what I get from the mysql log:

SELECT Takes.id,Takes.ssn,Takes.cid,Takes.sname,Takes.grade FROM Takes INNER JOIN Students AS Takes__student ON Takes.ssn = Takes__student.id WHERE (Takes__student.name = 'Kevin Smith')

The problem I see here is the Takes.ssn = Takesstudent.id

It's trying to compare the social security number to the auto incrementing primary key id.
If I open mysql and type in the same query but change that to Takes.ssn = Takesstudent.ssn it works how I want it to.

So is there something I need to make clear in my models so this works correctly?

comment:3 Changed 7 years ago by no@…

Erm, should have used code blocks.
Here is what Takes.objects.filter(student__name="Kevin Smith") generates:

SELECT `Takes`.`id`,`Takes`.`ssn`,`Takes`.`cid`,`Takes`.`sname`,`Takes`.`grade` FROM `Takes` 
INNER JOIN `Students` AS `Takes__student` ON `Takes`.`ssn` = `Takes__student`.`id` WHERE (`Takes__student`.`name` = 'Kevin Smith')

And this is what I want:

SELECT `Takes`.`id`,`Takes`.`ssn`,`Takes`.`cid`,`Takes`.`sname`,`Takes`.`grade` FROM `Takes` 
INNER JOIN `Students` AS `Takes__student` ON `Takes`.`ssn` = `Takes__student`.`ssn` WHERE (`Takes__student`.`name` = 'Kevin Smith')

Just change that id near the end to ssn.

comment:4 Changed 7 years ago by anonymous

I'm too busy to make a patch, but perhaps you're able to help yourself with a few hints.

The bug is in django/db/models.query.py, function lookup_inner, in this portion:

        # Does the name belong to a one-to-one, many-to-one, or regular field?
        field = find_field(name, current_opts.fields, False)
        if field:
            if field.rel: # One-to-One/Many-to-one field
                new_table = current_table + '__' + name
                new_opts = field.rel.to._meta
-->             new_column = new_opts.pk.column
                join_column = field.column
                raise FieldFound
            elif path:
                # For regular fields, if there are still items on the path,
                # an error has been made. We munge "name" so that the error
                # properly identifies the cause of the problem.
                name += LOOKUP_SEPARATOR + path[0]
            else:
                raise FieldFound

The statement new_column = new_opts.pk.column always chooses the primary key as join target, it should use the right value of field.rel or field.rel.to, no idea. If you play with it a bit in an ipython shell, you'll see what you need to put there.

comment:5 Changed 7 years ago by mir@…

Last comment was me. Sorry, but I set my browser to session only cookies ...

Changed 7 years ago by no@…

comment:6 Changed 7 years ago by no@…

  • Has patch set
  • Needs tests set
  • Version changed from 0.96 to SVN

I found the problem and I attached a patch to fix it. Basically it uses the field you specify in the ForeignKey
using to_field="whatever". Which seems to be the correct way of doing it. If you don't specify this field, django
automatically sets it to the primary key of the model you're relating to, so it works either way.

comment:7 Changed 7 years ago by Steven Wagner <stevenwagner@…>

I tested the patch and it fixed my issues mostly for bug #4306, though there is still one case that it does not fix.
http://code.djangoproject.com/ticket/4306#comment:2

comment:8 Changed 7 years ago by mtredinnick

  • Keywords foreignkey, qs-rf-fixed added; foreignkey removed

comment:9 Changed 7 years ago by ubernostrum

#4957 was a duplicate.

comment:10 Changed 6 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

(In [7477]) Merged the queryset-refactor branch into trunk.

This is a big internal change, but mostly backwards compatible with existing
code. Also adds a couple of new features.

Fixed #245, #1050, #1656, #1801, #2076, #2091, #2150, #2253, #2306, #2400, #2430, #2482, #2496, #2676, #2737, #2874, #2902, #2939, #3037, #3141, #3288, #3440, #3592, #3739, #4088, #4260, #4289, #4306, #4358, #4464, #4510, #4858, #5012, #5020, #5261, #5295, #5321, #5324, #5325, #5555, #5707, #5796, #5817, #5987, #6018, #6074, #6088, #6154, #6177, #6180, #6203, #6658

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.