how to filter values_list in django

django

Solution

A closer representation of the sql query would be:

from django.db.models import F

qry_name = Person.objects.filter(
    department__department_desc='Executive', 
    office__department=F('department')
).values_list(<..FIELDS..>)

Let know if this works.

Problem

``` class Office(models.Model): person = models.ForeignKey(Person, verbose_name="Person") department = models.ForeignKey(Department, verbose_name="Department") office_desc = models.CharField('Office', max_length=100,unique=True) office_acronym = models.CharField('Office Acronym', max_length=20,blank=True,help_text="Add acronym if any, not required") location = models.CharField('Location',max_length=100,blank=True) trunkline = models.CharField('Trunk Line',max_length=30,blank=True) directline = models.CharField('Direct Line',max_length=30,blank=True) localnumber = models.CharField('Local Number',max_length=30,blank=True) telefax = models.CharField('Telefax',max_length=30,blank=True) active = models.BooleanField('Active',default=True) class Department(models.Model): department_desc = models.CharField('Department', max_length=100,unique=True) department_acronym = models.CharField('Department Acronym', max_length=20,blank=True,help_text="Add acronym if any, not required") active = models.BooleanField('Active',default=True) class Person(models.Model): GENDER = ( ('M','Male'), ('F','Female'), ) first_name = models.CharField("First Name", max_length=100) last_name = models.CharField("Last Name",max_length=100) middle_name = models.CharField("Middle Name", max_length=100, blank=True) salutation = models.ForeignKey(Salutation, verbose_name="Salutation", null=True, blank=True) # suffix_name = models.ManyToManyField(SuffixName, verbose_name="Suffix Name",null=True, blank=True) # job_title = models.ManyToManyField(JobTitle, verbose_name="Job Title",null=True, blank=True) # gender = models.CharField(max_length=1, choices=GENDER,default='Male') birthdate = models.DateField('Birthdate',null=True,blank=True) image = models.ImageField('Image',upload_to='persons',blank=True) email = models.EmailField('Email',blank=True) street = models.CharField('Street',max_length=100, blank=True) brgy = models.CharField('Barangay',max_length=100, blank=True) town_city = models.CharField('Town/City',max_length=100, blank=True) zip_code = models.IntegerField('ZIP Code',null=True, blank=True) department = models.ManyToManyField(Department, verbose_name="Department",null=True, blank=True) # office = models.ManyToManyField(Office, verbose_name="Office", null=True, blank=True) # ``` sql_query ``` select pd.department_desc, pp.last_name, o.office_desc from person_person as pp INNER JOIN person_person_department as ppd on pp.id = ppd.person_id INNER JOIN person_department as pd on pd.id = ppd.id INNER JOIN person_office as o on o.department_id = pd.id where pd.department_desc = 'Executive' ``` views code: ``` per = Person.objects qry_name = per.values_list('salutation__salutation_desc','first_name','middle_name','last_name', 'office__office_desc', 'office__location','office__localnumber','office__trunkline','office__directline','office__telefax').filter(department__department_desc='Executive') ``` Result: query result includes person with different department Expected result: only person with Executive department If I query directly from the database I get the correct result but when I translate the query into django code it's returning different queryset. Query returns ok if certain person has a single office but if a person has multiple office thats where the inconsistency starts .Query returns list with different description other than what was specified. Am I doing the translation of the query to python right?

Original source