CODEWITHSUNDEEP
pythondjangodjango-orm
Ranvijay sachan
Ranvijay sachan

Reputation: 2444

Union query from Django ORM

Need to fetch aggregated data from 2 difrent tables.

Elements 
element_type   tempcolumn
   xyz            test1
   pqr            test2
   xyz            test3

Users: 
  User_names           
   auser
   buser
   cuser

need output in following format

element_type    count
  xyz             2 
  pqr             1 
  users           3

SQL Query Example:

SELECT element_type, count(*) 
  FROM Elements group by element_type

union

  select 'users',count(*) from Users

can we excute same with django orm?

Upvotes: 0

Views: 648

Answers (1)

Ranvijay sachan
Ranvijay sachan

Reputation: 2444

On Django, you can use | to join two querysets but I'm not using it here.

Because values/annotate actually returns a list of tuples instead of a query_set

you can run raw SQL on Django but raw is for optimization. https://docs.djangoproject.com/en/1.9/topics/db/sql/

object_count = Elements.objects.all().values('element_type').annotate(total=Count('element_type'))
response_data = {}
for q in object_count:
    if q['element_type'] == 'xyz':
        response_data['total_ xyz'] = q['total']
    if q['element_type'] == 'pqr':
        response_data['total_ pqr'] = q['total']
response_data['users_count'] = MyUser.objects.all().count()

Upvotes: 1

Related Questions