basically, need order, based on multiple values manytomanyfield.
so want achieve having objects questioned values on top, continuing objects have less of questioned values. continuing objects don't have of these values.
the models:
class color(models.model): name = models.charfield(max_length=200) class item(models.model): surface_color = models.manytomanyfield(color) the instances created based on models above:
- item 1) surface_color=[1, 2, 3]
- item 2) surface_color=[1, 2]
- item 3) surface_color=[2]
- item 4) surface_color=[1, 3]
now need order based multiple colors:
fake query: item.objects.all().order_by(surface_color_id=[1, 3])
the query should have following results:
- item 4, since has both 1 , 3
- item 1, since has both 1 , 3
- item 2, since has 1
- item 3, since has none
is possible single queryset? or need spam multiple queries each combination?
the things found on internet ordering multiple fields, values.
any appreciated.
this should give want:
list(item.objects.filter(surface_color__in=[1,3]).distinct().annotate(num_colors=count('surface_color')).order_by('-num_colors')) + list(item.objects.exclude(surface_color__in=[1,3]).distinct()) it requires 2 queries, though don't need separate query each item.
same logic broken down comments:
# make sure import count somewhere in file first django.db.models import count # id of color objects match color_ids_to_match = [1,3] #----------------------------------------------------------------------------------------------------------------------- # item objects color objects matching `color_ids_to_match` # - `surface_color` field has **at least one** color object matching id `color_ids_to_match` # - matches items #4, #1, #2 sample data items_with_matching_color = item.objects.filter(surface_color__in=color_ids_to_match).distinct() # adds select field query track number of surface_color objects matched # - **not total** number of color objects associated through `surface_color` items_with_matching_color = items_with_matching_color.annotate(num_colors=count('surface_color')) # order field in descending order # - note order undetermined between item objects same `num_colors` value items_with_matching_color = items_with_matching_color.order_by('-num_colors') #----------------------------------------------------------------------------------------------------------------------- # item objects **not** associated color objects id in `color_ids_to_match` # - matches item #3 sample data items_without_matching_color = item.objects.exclude(surface_color__in=color_ids_to_match).distinct() # optional - sets num_colors field 0 queryset in case need information django.db.models.expressions import rawsql items_without_matching_color = items_without_matching_color.annotate(num_colors=rawsql('0', ())) #----------------------------------------------------------------------------------------------------------------------- # convert 2 querysets lists , concatenate them # - necessary because simple queryset union such `items_with_matching_color | items_without_matching_color` # not maintain order between 2 querysets ordered_items = list(items_with_matching_color) + list(items_without_matching_color) output using sample data:
>>> ordered_items [<item: 1: <queryset [<color: 1>, <color: 2>, <color: 3>]>>, <item: 4: <queryset [<color: 1>, <color: 3>]>>, <item: 2: <queryset [<color: 1>, <color: 2>]>>, <item: 3: <queryset [<color: 2>]>>] note here, item 1 before item 4. mentioned order between 2 doesn't matter since both matched same number of color objects. can add parameter order_by further refine ordering needs.
getting number of matching color objects:
<item: 1: <queryset [<color: 1>, <color: 2>, <color: 3>]>> >>> ordered_items[0].num_colors 2 >>> ordered_items[1] <item: 4: <queryset [<color: 1>, <color: 3>]>> >>> ordered_items[1].num_colors 2 >>> ordered_items[2] <item: 2: <queryset [<color: 1>, <color: 2>]>> >>> ordered_items[2].num_colors 1 >>> ordered_items[3] <item: 3: <queryset [<color: 2>]>> >>> ordered_items[3].num_colors 0 