How to combine two or more querysets in a Django view

Question

I am trying to build the search for a Django site I am building  and in that search  I am searching in 3 different models  And to get pagination on the search result list  I would like to use a generic object list view to display the results  But to do that  I have to merge 3 querysets into one   How can I do that  I ve tried this   result list                  page list   Page objects filter      Q title  icontains cleaned search term         Q body  icontains cleaned search term   article list   Article objects filter      Q title  icontains cleaned search term         Q body  icontains cleaned search term         Q tags  icontains cleaned search term   post list   Post objects filter      Q title  icontains cleaned search term         Q body  icontains cleaned search term         Q tags  icontains cleaned search term    for x in page list      result list append x  for x in article list      result list append x  for x in post list      result list append x   return object list      request       queryset result list       template object name  result       paginate by 10       extra context            search term   search term       template name  search result list html     But this doesn t work  I get an error when I try to use that list in the generic view  The list is missing the clone attribute   Does anybody know how I can merge the three lists  page list  article list and post list

User · Answer

This recursive function concatenates array of querysets into one queryset    def merge query ar       if len ar    0          return  ar      while len ar  gt 1          tmp ar 0    ar 1          ar 0  tmp         ar pop 1          return ar

User · Answer

You can use Union qs   qs1 union qs2  qs3   But if you want to apply order by on the foreign models of the combined queryset   then you need to Select them before hand this way   otherwise it won t work Example qs   qs1 union qs2 select related  quot foreignModel quot    qs3 select related  quot foreignModel quot    qs order by  quot foreignModel  prop1 quot    where prop1 is a property in the foreign model

User · Answer

The big downside of your current approach is its inefficiency with large search result sets  as you have to pull down the entire result set from the database each time  even though you only intend to display one page of results   In order to only pull down the objects you actually need from the database  you have to use pagination on a QuerySet  not a list   If you do this  Django actually slices the QuerySet before the query is executed  so the SQL query will use OFFSET and LIMIT to only get the records you will actually display   But you can t do this unless you can cram your search into a single query somehow   Given that all three of your models have title and body fields  why not use model inheritance   Just have all three models inherit from a common ancestor that has title and body  and perform the search as a single query on the ancestor model

User · Answer

This will do the work without using any other libs result list   page list   article list   post list

User · Answer

Related  for mixing querysets from the same model  or for similar fields from a few models  Starting with Django 1 11 a QuerySet union   method is also available      union    union  other qs  all False        New in Django 1 11  Uses SQL   s UNION operator to combine the results of two or more QuerySets  For example    gt  gt  gt  qs1 union qs2  qs3        The UNION operator selects only distinct values by default  To allow duplicate values  use the all True   argument       union    intersection    and difference   return model instances of   the type of the first QuerySet even if the arguments are QuerySets of   other models  Passing different models works as long as the SELECT   list is the same in all QuerySets  at least the types  the names don   t   matter as long as the types in the same order        In addition  only LIMIT  OFFSET  and ORDER BY  i e  slicing and   order by    are allowed on the resulting QuerySet  Further  databases   place restrictions on what operations are allowed in the combined   queries  For example  most databases don   t allow LIMIT or OFFSET in   the combined queries

User · Answer

DATE FIELD MAPPING         Model1   date       Model2   pubdate      def my key func obj       return getattr obj  DATE FIELD MAPPING type obj     And then sorted chain Model1 objects all    Model2 objects all     key my key func    Quoted from https   groups google com forum   topic django-users 6wUNuJa4jVw  See Alex Gaynor

User · Answer

Concatenating the querysets into a list is the simplest approach  If the database will be hit for all querysets anyway  e g  because the result needs to be sorted   this won t add further cost   from itertools import chain result list   list chain page list  article list  post list     Using itertools chain is faster than looping each list and appending elements one by one  since itertools is implemented in C  It also consumes less memory than converting each queryset into a list before concatenating   Now it s possible to sort the resulting list e g  by date  as requested in hasen j s comment to another answer   The sorted   function conveniently accepts a generator and returns a list   result list   sorted      chain page list  article list  post list       key lambda instance  instance date created    If you re using Python 2 4 or later  you can use attrgetter instead of a lambda  I remember reading about it being faster  but I didn t see a noticeable speed difference for a million item list   from operator import attrgetter result list   sorted      chain page list  article list  post list       key attrgetter  date created

User · Answer

Try this   matches   pages   articles   posts   It retains all the functions of the querysets which is nice if you want to order by or similar   Please note  this doesn t work on querysets from two different models

User · Answer

here s an idea    just pull down one full page of results from each of the three and then throw out the 20 least useful ones    this eliminates the large querysets and that way you only sacrifice a little performance instead of a lot

User · Answer

Concatenating the querysets into a list is the simplest approach  If the database will be hit for all querysets anyway  e g  because the result needs to be sorted   this won t add further cost   from itertools import chain result list   list chain page list  article list  post list     Using itertools chain is faster than looping each list and appending elements one by one  since itertools is implemented in C  It also consumes less memory than converting each queryset into a list before concatenating   Now it s possible to sort the resulting list e g  by date  as requested in hasen j s comment to another answer   The sorted   function conveniently accepts a generator and returns a list   result list   sorted      chain page list  article list  post list       key lambda instance  instance date created    If you re using Python 2 4 or later  you can use attrgetter instead of a lambda  I remember reading about it being faster  but I didn t see a noticeable speed difference for a million item list   from operator import attrgetter result list   sorted      chain page list  article list  post list       key attrgetter  date created

User · Answer

Try this   matches   pages   articles   posts   It retains all the functions of the querysets which is nice if you want to order by or similar   Please note  this doesn t work on querysets from two different models

User · Answer

You can use the QuerySetChain class below  When using it with Django s paginator  it should only hit the database with COUNT    queries for all querysets and SELECT   queries only for those querysets whose records are displayed on the current page   Note that you need to specify template name  if using a QuerySetChain with generic views  even if the chained querysets all use the same model   from itertools import islice  chain  class QuerySetChain object               Chains multiple subquerysets  possibly of different models  and behaves as     one queryset   Supports minimal methods needed for use with     django core paginator               def   init   self   subquerysets           self querysets   subquerysets      def count self                       Performs a  count   for all subquerysets and returns the number of         records as an integer                      return sum qs count   for qs in self querysets       def  clone self            Returns a clone of this queryset chain          return self   class    self querysets       def  all self            Iterates records in all subquerysets          return chain  self querysets       def   getitem   self  ndx                       Retrieves an item or slice from the chained set of results from all         subquerysets                      if type ndx  is slice              return list islice self  all    ndx start  ndx stop  ndx step or 1           else              return islice self  all    ndx  ndx 1  next     In your example  the usage would be   pages   Page objects filter Q title  icontains cleaned search term                                Q body  icontains cleaned search term   articles   Article objects filter Q title  icontains cleaned search term                                      Q body  icontains cleaned search term                                      Q tags  icontains cleaned search term   posts   Post objects filter Q title  icontains cleaned search term                                Q body  icontains cleaned search term                                 Q tags  icontains cleaned search term   matches   QuerySetChain pages  articles  posts    Then use matches with the paginator like you used result list in your example   The itertools module was introduced in Python 2 3  so it should be available in all Python versions Django runs on

User · Answer

here s an idea    just pull down one full page of results from each of the three and then throw out the 20 least useful ones    this eliminates the large querysets and that way you only sacrifice a little performance instead of a lot

User · Answer

This will do the work without using any other libs result list   page list   article list   post list

User · Answer

Requirements  Django  2 0 2  django-querysetsequence  0 8  In case you want to combine querysets and still come out with a QuerySet  you might want to check out django-queryset-sequence   But one note about it  It only takes two querysets as it s argument  But with python reduce you can always apply it to multiple querysets   from functools import reduce from queryset sequence import QuerySetSequence  combined queryset   reduce QuerySetSequence  list of queryset    And that s it  Below is a situation I ran into and how I employed list comprehension  reduce and django-queryset-sequence  from functools import reduce from django shortcuts import render     from queryset sequence import QuerySetSequence  class People models Model       user   models OneToOneField User  on delete models CASCADE      mentor   models ForeignKey  self   null True  on delete models SET NULL  related name  my mentees    class Book models Model       name   models CharField max length 20      owner   models ForeignKey Student  on delete models CASCADE     as a mentor  I want to see all the books owned by all my mentees in one view  def mentee books request       template    my mentee books html      mentor   People objects get user request user      my mentees   mentor my mentees all     returns QuerySet of all my mentees     mentee books   reduce QuerySetSequence   each book set all   for each in my mentees        return render request  template    mentee books    mentee books

User · Answer

This can be achieved by two ways either   1st way to do this  Use union operator for queryset   to take union of two queryset  If both queryset belongs to same model   single model than it is possible to combine querysets by using union operator   For an instance  pagelist1   Page objects filter      Q title  icontains cleaned search term         Q body  icontains cleaned search term   pagelist2   Page objects filter      Q title  icontains cleaned search term         Q body  icontains cleaned search term   combined list   pagelist1   pagelist2   this would take union of two querysets   2nd way to do this  One other way to achieve combine operation between two queryset is to use itertools chain function   from itertools import chain combined results   list chain pagelist1  pagelist2

User · Answer

here s an idea    just pull down one full page of results from each of the three and then throw out the 20 least useful ones    this eliminates the large querysets and that way you only sacrifice a little performance instead of a lot

User · Answer

Concatenating the querysets into a list is the simplest approach  If the database will be hit for all querysets anyway  e g  because the result needs to be sorted   this won t add further cost   from itertools import chain result list   list chain page list  article list  post list     Using itertools chain is faster than looping each list and appending elements one by one  since itertools is implemented in C  It also consumes less memory than converting each queryset into a list before concatenating   Now it s possible to sort the resulting list e g  by date  as requested in hasen j s comment to another answer   The sorted   function conveniently accepts a generator and returns a list   result list   sorted      chain page list  article list  post list       key lambda instance  instance date created    If you re using Python 2 4 or later  you can use attrgetter instead of a lambda  I remember reading about it being faster  but I didn t see a noticeable speed difference for a million item list   from operator import attrgetter result list   sorted      chain page list  article list  post list       key attrgetter  date created

User · Answer

The big downside of your current approach is its inefficiency with large search result sets  as you have to pull down the entire result set from the database each time  even though you only intend to display one page of results   In order to only pull down the objects you actually need from the database  you have to use pagination on a QuerySet  not a list   If you do this  Django actually slices the QuerySet before the query is executed  so the SQL query will use OFFSET and LIMIT to only get the records you will actually display   But you can t do this unless you can cram your search into a single query somehow   Given that all three of your models have title and body fields  why not use model inheritance   Just have all three models inherit from a common ancestor that has title and body  and perform the search as a single query on the ancestor model

User · Answer

Related  for mixing querysets from the same model  or for similar fields from a few models  Starting with Django 1 11 a QuerySet union   method is also available      union    union  other qs  all False        New in Django 1 11  Uses SQL   s UNION operator to combine the results of two or more QuerySets  For example    gt  gt  gt  qs1 union qs2  qs3        The UNION operator selects only distinct values by default  To allow duplicate values  use the all True   argument       union    intersection    and difference   return model instances of   the type of the first QuerySet even if the arguments are QuerySets of   other models  Passing different models works as long as the SELECT   list is the same in all QuerySets  at least the types  the names don   t   matter as long as the types in the same order        In addition  only LIMIT  OFFSET  and ORDER BY  i e  slicing and   order by    are allowed on the resulting QuerySet  Further  databases   place restrictions on what operations are allowed in the combined   queries  For example  most databases don   t allow LIMIT or OFFSET in   the combined queries

User · Answer

Requirements  Django  2 0 2  django-querysetsequence  0 8  In case you want to combine querysets and still come out with a QuerySet  you might want to check out django-queryset-sequence   But one note about it  It only takes two querysets as it s argument  But with python reduce you can always apply it to multiple querysets   from functools import reduce from queryset sequence import QuerySetSequence  combined queryset   reduce QuerySetSequence  list of queryset    And that s it  Below is a situation I ran into and how I employed list comprehension  reduce and django-queryset-sequence  from functools import reduce from django shortcuts import render     from queryset sequence import QuerySetSequence  class People models Model       user   models OneToOneField User  on delete models CASCADE      mentor   models ForeignKey  self   null True  on delete models SET NULL  related name  my mentees    class Book models Model       name   models CharField max length 20      owner   models ForeignKey Student  on delete models CASCADE     as a mentor  I want to see all the books owned by all my mentees in one view  def mentee books request       template    my mentee books html      mentor   People objects get user request user      my mentees   mentor my mentees all     returns QuerySet of all my mentees     mentee books   reduce QuerySetSequence   each book set all   for each in my mentees        return render request  template    mentee books    mentee books

User · Answer

This can be achieved by two ways either   1st way to do this  Use union operator for queryset   to take union of two queryset  If both queryset belongs to same model   single model than it is possible to combine querysets by using union operator   For an instance  pagelist1   Page objects filter      Q title  icontains cleaned search term         Q body  icontains cleaned search term   pagelist2   Page objects filter      Q title  icontains cleaned search term         Q body  icontains cleaned search term   combined list   pagelist1   pagelist2   this would take union of two querysets   2nd way to do this  One other way to achieve combine operation between two queryset is to use itertools chain function   from itertools import chain combined results   list chain pagelist1  pagelist2

User · Answer

Concatenating the querysets into a list is the simplest approach  If the database will be hit for all querysets anyway  e g  because the result needs to be sorted   this won t add further cost   from itertools import chain result list   list chain page list  article list  post list     Using itertools chain is faster than looping each list and appending elements one by one  since itertools is implemented in C  It also consumes less memory than converting each queryset into a list before concatenating   Now it s possible to sort the resulting list e g  by date  as requested in hasen j s comment to another answer   The sorted   function conveniently accepts a generator and returns a list   result list   sorted      chain page list  article list  post list       key lambda instance  instance date created    If you re using Python 2 4 or later  you can use attrgetter instead of a lambda  I remember reading about it being faster  but I didn t see a noticeable speed difference for a million item list   from operator import attrgetter result list   sorted      chain page list  article list  post list       key attrgetter  date created

User · Answer

You can use the QuerySetChain class below  When using it with Django s paginator  it should only hit the database with COUNT    queries for all querysets and SELECT   queries only for those querysets whose records are displayed on the current page   Note that you need to specify template name  if using a QuerySetChain with generic views  even if the chained querysets all use the same model   from itertools import islice  chain  class QuerySetChain object               Chains multiple subquerysets  possibly of different models  and behaves as     one queryset   Supports minimal methods needed for use with     django core paginator               def   init   self   subquerysets           self querysets   subquerysets      def count self                       Performs a  count   for all subquerysets and returns the number of         records as an integer                      return sum qs count   for qs in self querysets       def  clone self            Returns a clone of this queryset chain          return self   class    self querysets       def  all self            Iterates records in all subquerysets          return chain  self querysets       def   getitem   self  ndx                       Retrieves an item or slice from the chained set of results from all         subquerysets                      if type ndx  is slice              return list islice self  all    ndx start  ndx stop  ndx step or 1           else              return islice self  all    ndx  ndx 1  next     In your example  the usage would be   pages   Page objects filter Q title  icontains cleaned search term                                Q body  icontains cleaned search term   articles   Article objects filter Q title  icontains cleaned search term                                      Q body  icontains cleaned search term                                      Q tags  icontains cleaned search term   posts   Post objects filter Q title  icontains cleaned search term                                Q body  icontains cleaned search term                                 Q tags  icontains cleaned search term   matches   QuerySetChain pages  articles  posts    Then use matches with the paginator like you used result list in your example   The itertools module was introduced in Python 2 3  so it should be available in all Python versions Django runs on

User · Answer

You can use the QuerySetChain class below  When using it with Django s paginator  it should only hit the database with COUNT    queries for all querysets and SELECT   queries only for those querysets whose records are displayed on the current page   Note that you need to specify template name  if using a QuerySetChain with generic views  even if the chained querysets all use the same model   from itertools import islice  chain  class QuerySetChain object               Chains multiple subquerysets  possibly of different models  and behaves as     one queryset   Supports minimal methods needed for use with     django core paginator               def   init   self   subquerysets           self querysets   subquerysets      def count self                       Performs a  count   for all subquerysets and returns the number of         records as an integer                      return sum qs count   for qs in self querysets       def  clone self            Returns a clone of this queryset chain          return self   class    self querysets       def  all self            Iterates records in all subquerysets          return chain  self querysets       def   getitem   self  ndx                       Retrieves an item or slice from the chained set of results from all         subquerysets                      if type ndx  is slice              return list islice self  all    ndx start  ndx stop  ndx step or 1           else              return islice self  all    ndx  ndx 1  next     In your example  the usage would be   pages   Page objects filter Q title  icontains cleaned search term                                Q body  icontains cleaned search term   articles   Article objects filter Q title  icontains cleaned search term                                      Q body  icontains cleaned search term                                      Q tags  icontains cleaned search term   posts   Post objects filter Q title  icontains cleaned search term                                Q body  icontains cleaned search term                                 Q tags  icontains cleaned search term   matches   QuerySetChain pages  articles  posts    Then use matches with the paginator like you used result list in your example   The itertools module was introduced in Python 2 3  so it should be available in all Python versions Django runs on

User · Answer

The big downside of your current approach is its inefficiency with large search result sets  as you have to pull down the entire result set from the database each time  even though you only intend to display one page of results   In order to only pull down the objects you actually need from the database  you have to use pagination on a QuerySet  not a list   If you do this  Django actually slices the QuerySet before the query is executed  so the SQL query will use OFFSET and LIMIT to only get the records you will actually display   But you can t do this unless you can cram your search into a single query somehow   Given that all three of your models have title and body fields  why not use model inheritance   Just have all three models inherit from a common ancestor that has title and body  and perform the search as a single query on the ancestor model

User · Answer

here s an idea    just pull down one full page of results from each of the three and then throw out the 20 least useful ones    this eliminates the large querysets and that way you only sacrifice a little performance instead of a lot

User · Answer

In case you want to chain a lot of querysets  try this   from itertools import chain result   list chain  docs     where  docs is a list of querysets

User · Answer

The big downside of your current approach is its inefficiency with large search result sets  as you have to pull down the entire result set from the database each time  even though you only intend to display one page of results   In order to only pull down the objects you actually need from the database  you have to use pagination on a QuerySet  not a list   If you do this  Django actually slices the QuerySet before the query is executed  so the SQL query will use OFFSET and LIMIT to only get the records you will actually display   But you can t do this unless you can cram your search into a single query somehow   Given that all three of your models have title and body fields  why not use model inheritance   Just have all three models inherit from a common ancestor that has title and body  and perform the search as a single query on the ancestor model

User · Answer

DATE FIELD MAPPING         Model1   date       Model2   pubdate      def my key func obj       return getattr obj  DATE FIELD MAPPING type obj     And then sorted chain Model1 objects all    Model2 objects all     key my key func    Quoted from https   groups google com forum   topic django-users 6wUNuJa4jVw  See Alex Gaynor

User · Answer

You can use the QuerySetChain class below  When using it with Django s paginator  it should only hit the database with COUNT    queries for all querysets and SELECT   queries only for those querysets whose records are displayed on the current page   Note that you need to specify template name  if using a QuerySetChain with generic views  even if the chained querysets all use the same model   from itertools import islice  chain  class QuerySetChain object               Chains multiple subquerysets  possibly of different models  and behaves as     one queryset   Supports minimal methods needed for use with     django core paginator               def   init   self   subquerysets           self querysets   subquerysets      def count self                       Performs a  count   for all subquerysets and returns the number of         records as an integer                      return sum qs count   for qs in self querysets       def  clone self            Returns a clone of this queryset chain          return self   class    self querysets       def  all self            Iterates records in all subquerysets          return chain  self querysets       def   getitem   self  ndx                       Retrieves an item or slice from the chained set of results from all         subquerysets                      if type ndx  is slice              return list islice self  all    ndx start  ndx stop  ndx step or 1           else              return islice self  all    ndx  ndx 1  next     In your example  the usage would be   pages   Page objects filter Q title  icontains cleaned search term                                Q body  icontains cleaned search term   articles   Article objects filter Q title  icontains cleaned search term                                      Q body  icontains cleaned search term                                      Q tags  icontains cleaned search term   posts   Post objects filter Q title  icontains cleaned search term                                Q body  icontains cleaned search term                                 Q tags  icontains cleaned search term   matches   QuerySetChain pages  articles  posts    Then use matches with the paginator like you used result list in your example   The itertools module was introduced in Python 2 3  so it should be available in all Python versions Django runs on

User · Answer

This recursive function concatenates array of querysets into one queryset    def merge query ar       if len ar    0          return  ar      while len ar  gt 1          tmp ar 0    ar 1          ar 0  tmp         ar pop 1          return ar

User · Answer

In case you want to chain a lot of querysets  try this   from itertools import chain result   list chain  docs     where  docs is a list of querysets

User · Answer

You can use Union qs   qs1 union qs2  qs3   But if you want to apply order by on the foreign models of the combined queryset   then you need to Select them before hand this way   otherwise it won t work Example qs   qs1 union qs2 select related  quot foreignModel quot    qs3 select related  quot foreignModel quot    qs order by  quot foreignModel  prop1 quot    where prop1 is a property in the foreign model

[django] How to combine two or more querysets in a Django view?

Examples related to django

Examples related to search

Examples related to django-queryset

Examples related to django-q