COUNT field incorrect or syntax error in filter __in django

Question

I am trying to fetch data in the below manner:

ts= Tags.objects.filter(tagtype=2)
ls= list(p.entity for p in ts)
print(ls)  
print(len(ls))
users= Users.objects.filter(email__in=ls)
print(users)

When I checked in DB len(ls) is greater than the result count from Users Table. How do I filter data from Users when the list being passed has more values... like list has 4560 values but matching values in users table is only 4199. I am able to get the data on SQL side, but not able to fetch the data using django filter.

User Model(FROM legacy Database)

class Users(models.Model):
    gender_choices= (
        (0, 'Female'), 
        (1, 'Male')
        )

    email = models.EmailField(db_column='Email')
    id = models.CharField(primary_key=True, max_length=128, db_column='Id', default= uuid.uuid4)    
    first_name = models.CharField(db_column='Name', max_length=100, unique=False, blank=True, null=True)    
    dateofbirth = models.DateField(db_column='DateOfBirth', blank=True, null=True, verbose_name='Date of Birth')    
    mobile = models.CharField(db_column='Mobile', max_length=50, verbose_name='Mobile')    
    address = models.TextField(db_column='Address', blank=True, null=True)    
    gender= models.IntegerField(choices=gender_choices, db_column='Gender', default=1)
    username = models.CharField(db_column='UserName', unique=True, max_length=256, blank=True, null=True, verbose_name='Username')    

-- few other fields --

Tags Table(Legacy DB)

class Tags(models.Model):
    """
    This table stores information about Tags
    #### entity id refers to for users, Username is entity id, country then countryid is entity id

    """   
    id = models.BigAutoField(db_column='Id', primary_key=True)
    tagname = models.CharField(db_column='TagName', max_length=200, verbose_name='Tag Name')    
    tagtype = models.ForeignKey('core.Tagtypes', on_delete=models.DO_NOTHING, db_column='TagType', related_name='tagtypes', limit_choices_to={'name':'General'}, verbose_name='Tag Type')    
    entity= models.CharField(db_column='EntityId',max_length=100, blank=True, null=True, verbose_name='Entity')

I am trying to fetch only exisiting data from db where tagtype= 2 and then in Users I need all those matching emails. But the end result is I need to get a list with the user company from user table and their associated tag id from tags table.. select Id, Name, Company, Email from Users where Email in (select EntityId from Tags where TagType = 2) this is working on the sql side where I am getting data.. but not able to fetch data on django front..

Answer 1

You want to get the Users where the email address is in the set of entities for Tags of tagtype = 2.

Users.objects.filter(email__in=Tags.objects.filter(tagtype=2).values('entity'))

You can use __in with a query. Django will create a subquery for you. The .values call makes sure Django only selects one column from the subquery. You will get SQL like

SELECT *
FROM users
WHERE email IN (SELECT entity FROM tags WHERE tagtype=2)