CODEWITHSUNDEEP
pythondjango
coding
coding

Reputation: 181

One to many queryset in django

I have 2 tables, Item and UserDetail. In table Item, I have item_id and item_name like this: enter image description here

My UserDetail table like this: enter image description here

I need to get user's username, and their most recent updated(e.g, Tina's education level was BSc on 05/01/2015 and changed to MSc on 05/01/2017. So I have to pick MSc for her education level), email, home phone, cellphone and education information. For any missing info, I leave it as blank. So my result would be:

[{'username':John, 'email':'[email protected]', 'homePhone':'', 'cellPhone':'', 'education':''},
 {'username':Jade, 'email': [email protected], 'homePhone':111 456-7890, 'cellPhone':'','education':''},
{'username':Tina, 'email':'', 'homePhone':'', 'cellPhone':'211 234-5678', 'education':'Msc'},]

Currently, I have queryset like this in my view.py:

items=Item.objects.filter(Q(item_id__in=(2, 3, 4, 6))).values('item_name').order_by('item_id')

users=[username1, username2, ...]
for user in users:
    users_data = UserDetail.objects.filter(username=username)\
          .filter(Q(id__in=(2,3, 4, 6)).values('id','record')\
          .order_by('id').latest(DateRecorded)
    qs1=[user]
    for item in items:
         for value in users_data:
             user_data=''
             if item['item_id']== value['id']:
                  user_data=value['record']
                  break
         qs1.append(data)
    qs2.append(qs1)

This works OK. However, the format of qs2 is not exactly the one I expected. I am getting qs2 like this:

[[u'John', u'[email protected]', u'', u'', u''],
 [u'Jade', u'[email protected]', u'111 456-7890',u'',u''],
[u'Tina', u'', u'', u'211 234-5678', u'mac'],]

Also, it takes more time to get the result back. Does anyone have good suggestion on this?

Add more comments on this:

Right now, I have data like this:

users_data= <QuerySet [{u'username': u'john', u'id': u'2', u'record': u'[email protected]'}, 
{u'username': u'jade', u'id': u'2', u'record': u'[email protected]'},   
{u'username': u'jade', u'id': u'3', u'record': u'111 456-7890'}....]

Would prefer to construct the final data like:

by_username = [{u'username': u'john', u'email': u'[email protected]'},  
{u'username': u'jade', u'homePhone': u'111 456-7890'}]

So when I render the data in templates, I can access data as i.username, i.email, i.homePhone, i.cellPhone, i.education. This way, if user has any item missing, it will display as blank, like this: enter image description here

Upvotes: 0

Views: 134

Answers (2)

Will Keeling
Will Keeling

Reputation: 23004

It would perhaps make sense to use a formal ForeignKey relationship between Item and UserDetail since that relationship is one to many.

class UserDetail(models.Model):
    item = models.ForeignKey(Item, on_delete=models.CASCADE)
    ...

You could then query the data you want more efficiently:

usernames = [username1, username2, ...]
items = [2, 3, 4, 6]
users_data = UserDetail.objects.filter(username__in=usernames, item__in=items)\
      .select_related('item')\
      .order_by('item_id').latest(DateRecorded)

And then structure the result how you need it:

from collections import defaultdict

by_username = defaultdict(dict)

for user_data in users_data:
    by_username[user_data['username']]['username'] = user_data['username']
    by_username[user_data['username']][user_data.item.item_name] = [user_data.record]

return by_username.values()

Upvotes: 1

urDMG
urDMG

Reputation: 436

I'm not clearly understand your database structure. But if you want the latest records you can get them!

queryset = UserDetail.objects.all().latest('date_recorded').distinct()

also you can add filter for obtaining ids in ids or whatever you want. I've also applied distinct() on the queryset in order to have only unique records

Upvotes: 0

Related Questions