Coverage for cookbook/helper/recipe_search.py: 71%
405 statements
« prev ^ index » next coverage.py v7.4.0, created at 2023-12-29 01:02 +0100
1import json
2from datetime import date, timedelta
4from django.contrib.postgres.search import SearchQuery, SearchRank, SearchVector, TrigramSimilarity
5from django.core.cache import cache
6from django.db.models import Avg, Case, Count, Exists, F, Max, OuterRef, Q, Subquery, Value, When
7from django.db.models.functions import Coalesce, Lower, Substr
8from django.utils import timezone, translation
10from cookbook.helper.HelperFunctions import Round, str2bool
11from cookbook.managers import DICTIONARY
12from cookbook.models import (CookLog, CustomFilter, Food, Keyword, Recipe, SearchFields,
13 SearchPreference, ViewLog)
14from recipes import settings
17# TODO consider creating a simpleListRecipe API that only includes minimum of recipe info and minimal filtering
18class RecipeSearch():
19 _postgres = settings.DATABASES['default']['ENGINE'] == 'django.db.backends.postgresql'
21 def __init__(self, request, **params):
22 self._request = request
23 self._queryset = None
24 if f := params.get('filter', None):
25 custom_filter = (
26 CustomFilter.objects.filter(id=f, space=self._request.space)
27 .filter(Q(created_by=self._request.user) | Q(shared=self._request.user) | Q(recipebook__shared=self._request.user))
28 .first()
29 )
30 if custom_filter:
31 self._params = {**json.loads(custom_filter.search)}
32 self._original_params = {**(params or {})}
33 # json.loads casts rating as an integer, expecting string
34 if isinstance(self._params.get('rating', None), int):
35 self._params['rating'] = str(self._params['rating'])
36 else:
37 self._params = {**(params or {})}
38 else:
39 self._params = {**(params or {})}
40 if self._request.user.is_authenticated:
41 CACHE_KEY = f"search_pref_{request.user.id}"
42 cached_result = cache.get(CACHE_KEY, default=None)
43 if cached_result is not None:
44 self._search_prefs = cached_result
45 else:
46 self._search_prefs = request.user.searchpreference
47 cache.set(CACHE_KEY, self._search_prefs, timeout=10)
48 else:
49 self._search_prefs = SearchPreference()
50 self._string = self._params.get("query").strip() if self._params.get("query", None) else None
51 self._rating = self._params.get("rating", None)
52 self._keywords = {
53 "or": self._params.get("keywords_or", None) or self._params.get("keywords", None),
54 "and": self._params.get("keywords_and", None),
55 "or_not": self._params.get("keywords_or_not", None),
56 "and_not": self._params.get("keywords_and_not", None),
57 }
58 self._foods = {
59 "or": self._params.get("foods_or", None) or self._params.get("foods", None),
60 "and": self._params.get("foods_and", None),
61 "or_not": self._params.get("foods_or_not", None),
62 "and_not": self._params.get("foods_and_not", None),
63 }
64 self._books = {
65 "or": self._params.get("books_or", None) or self._params.get("books", None),
66 "and": self._params.get("books_and", None),
67 "or_not": self._params.get("books_or_not", None),
68 "and_not": self._params.get("books_and_not", None),
69 }
70 self._steps = self._params.get("steps", None)
71 self._units = self._params.get("units", None)
72 # TODO add created by
73 # TODO image exists
74 self._sort_order = self._params.get("sort_order", None)
75 self._internal = str2bool(self._params.get("internal", None))
76 self._random = str2bool(self._params.get("random", False))
77 self._new = str2bool(self._params.get("new", False))
78 self._num_recent = int(self._params.get("num_recent", 0))
79 self._include_children = str2bool(self._params.get("include_children", None))
80 self._timescooked = self._params.get("timescooked", None)
81 self._cookedon = self._params.get("cookedon", None)
82 self._createdon = self._params.get("createdon", None)
83 self._updatedon = self._params.get("updatedon", None)
84 self._viewedon = self._params.get("viewedon", None)
85 self._makenow = self._params.get("makenow", None)
86 self._never_used_food = str2bool(self._params.get("never_used_food", False))
87 # this supports hidden feature to find recipes missing X ingredients
88 if isinstance(self._makenow, bool) and self._makenow == True:
89 self._makenow = 0
90 elif isinstance(self._makenow, str) and self._makenow in ["yes", "true"]:
91 self._makenow = 0
92 else:
93 try:
94 self._makenow = int(self._makenow)
95 except (ValueError, TypeError):
96 self._makenow = None
98 self._search_type = self._search_prefs.search or "plain"
99 if self._string:
100 if self._postgres:
101 self._unaccent_include = self._search_prefs.unaccent.values_list('field', flat=True)
102 else:
103 self._unaccent_include = []
104 self._icontains_include = [x + '__unaccent' if x in self._unaccent_include else x for x in self._search_prefs.icontains.values_list('field', flat=True)]
105 self._istartswith_include = [x + '__unaccent' if x in self._unaccent_include else x for x in self._search_prefs.istartswith.values_list('field', flat=True)]
106 self._trigram_include = None
107 self._fulltext_include = None
108 self._trigram = False
109 if self._postgres and self._string:
110 self._language = DICTIONARY.get(translation.get_language(), 'simple')
111 self._trigram_include = [x + '__unaccent' if x in self._unaccent_include else x for x in self._search_prefs.trigram.values_list('field', flat=True)]
112 self._fulltext_include = self._search_prefs.fulltext.values_list('field', flat=True) or None
114 if self._search_type not in ['websearch', 'raw'] and self._trigram_include:
115 self._trigram = True
116 self.search_query = SearchQuery(
117 self._string,
118 search_type=self._search_type,
119 config=self._language,
120 )
121 self.search_rank = None
122 self.orderby = []
123 self._filters = None
124 self._fuzzy_match = None
126 def get_queryset(self, queryset):
127 self._queryset = queryset
128 self._queryset = self._queryset.prefetch_related('keywords')
129 self._build_sort_order()
130 self._recently_viewed(num_recent=self._num_recent)
131 self._cooked_on_filter(cooked_date=self._cookedon)
132 self._created_on_filter(created_date=self._createdon)
133 self._updated_on_filter(updated_date=self._updatedon)
134 self._viewed_on_filter(viewed_date=self._viewedon)
135 self._favorite_recipes(times_cooked=self._timescooked)
136 self._new_recipes()
137 self.keyword_filters(**self._keywords)
138 self.food_filters(**self._foods)
139 self.book_filters(**self._books)
140 self.rating_filter(rating=self._rating)
141 self.internal_filter(internal=self._internal)
142 self.step_filters(steps=self._steps)
143 self.unit_filters(units=self._units)
144 self._makenow_filter(missing=self._makenow)
145 self._never_used_food_filter()
146 self.string_filters(string=self._string)
147 return self._queryset.filter(space=self._request.space).order_by(*self.orderby)
149 def _sort_includes(self, *args):
150 for x in args:
151 if x in self.orderby:
152 return True
153 elif "-" + x in self.orderby:
154 return True
155 return False
157 def _build_sort_order(self):
158 if self._random:
159 self.orderby = ["?"]
160 else:
161 order = []
162 # TODO add userpreference for default sort order and replace '-favorite'
163 default_order = ["name"]
164 # recent and new_recipe are always first; they float a few recipes to the top
165 if self._num_recent:
166 order += ["-recent"]
167 if self._new:
168 order += ["-new_recipe"]
170 # if a sort order is provided by user - use that order
171 if self._sort_order:
172 if not isinstance(self._sort_order, list):
173 order += [self._sort_order]
174 else:
175 order += self._sort_order
176 if not self._postgres or not self._string:
177 if "score" in order:
178 order.remove("score")
179 if "-score" in order:
180 order.remove("-score")
181 # if no sort order provided prioritize text search, followed by the default search
182 elif self._postgres and self._string and (self._trigram or self._fulltext_include):
183 order += ["-score", *default_order]
184 # otherwise sort by the remaining order_by attributes or favorite by default
185 else:
186 order += default_order
187 order[:] = [Lower("name").asc() if x == "name" else x for x in order]
188 order[:] = [Lower("name").desc() if x == "-name" else x for x in order]
189 self.orderby = order
191 def string_filters(self, string=None):
192 if not string:
193 return
195 self.build_text_filters(self._string)
196 if self._postgres:
197 self.build_fulltext_filters(self._string)
198 self.build_trigram(self._string)
200 query_filter = Q()
201 if self._filters:
202 for f in self._filters:
203 query_filter |= f
205 # this creates duplicate records which can screw up other aggregates, see makenow for workaround
206 self._queryset = self._queryset.filter(query_filter).distinct()
207 if self._fulltext_include:
208 if self._fuzzy_match is None:
209 self._queryset = self._queryset.annotate(score=Coalesce(Max(self.search_rank), 0.0))
210 else:
211 self._queryset = self._queryset.annotate(rank=Coalesce(Max(self.search_rank), 0.0))
213 if self._fuzzy_match is not None:
214 simularity = self._fuzzy_match.filter(pk=OuterRef('pk')).values('simularity')
215 if not self._fulltext_include:
216 self._queryset = self._queryset.annotate(score=Coalesce(Subquery(simularity), 0.0))
217 else:
218 self._queryset = self._queryset.annotate(simularity=Coalesce(Subquery(simularity), 0.0))
219 if self._sort_includes('score') and self._fulltext_include and self._fuzzy_match is not None:
220 self._queryset = self._queryset.annotate(score=F('rank') + F('simularity'))
221 else:
222 query_filter = Q()
223 for f in [x + "__unaccent__iexact" if x in self._unaccent_include else x + "__iexact" for x in SearchFields.objects.all().values_list("field", flat=True)]:
224 query_filter |= Q(**{"%s" % f: self._string})
225 self._queryset = self._queryset.filter(query_filter).distinct()
227 def _cooked_on_filter(self, cooked_date=None):
228 if self._sort_includes('lastcooked') or cooked_date:
229 lessthan = self._sort_includes('-lastcooked') or '-' in (cooked_date or [])[:1]
230 if lessthan:
231 default = timezone.now() - timedelta(days=100000)
232 else:
233 default = timezone.now()
234 self._queryset = self._queryset.annotate(
235 lastcooked=Coalesce(Max(Case(When(cooklog__created_by=self._request.user, cooklog__space=self._request.space, then='cooklog__created_at'))), Value(default))
236 )
237 if cooked_date is None:
238 return
240 cooked_date = date(*[int(x)for x in cooked_date.split('-') if x != ''])
242 if lessthan:
243 self._queryset = self._queryset.filter(lastcooked__date__lte=cooked_date).exclude(lastcooked=default)
244 else:
245 self._queryset = self._queryset.filter(lastcooked__date__gte=cooked_date).exclude(lastcooked=default)
247 def _created_on_filter(self, created_date=None):
248 if created_date is None:
249 return
250 lessthan = '-' in created_date[:1]
251 created_date = date(*[int(x) for x in created_date.split('-') if x != ''])
252 if lessthan:
253 self._queryset = self._queryset.filter(created_at__date__lte=created_date)
254 else:
255 self._queryset = self._queryset.filter(created_at__date__gte=created_date)
257 def _updated_on_filter(self, updated_date=None):
258 if updated_date is None:
259 return
260 lessthan = '-' in updated_date[:1]
261 updated_date = date(*[int(x)for x in updated_date.split('-') if x != ''])
262 if lessthan:
263 self._queryset = self._queryset.filter(updated_at__date__lte=updated_date)
264 else:
265 self._queryset = self._queryset.filter(updated_at__date__gte=updated_date)
267 def _viewed_on_filter(self, viewed_date=None):
268 if self._sort_includes("lastviewed") or viewed_date:
269 longTimeAgo = timezone.now() - timedelta(days=100000)
270 self._queryset = self._queryset.annotate(
271 lastviewed=Coalesce(Max(Case(When(viewlog__created_by=self._request.user, viewlog__space=self._request.space, then='viewlog__created_at'))), Value(longTimeAgo))
272 )
273 if viewed_date is None:
274 return
275 lessthan = '-' in viewed_date[:1]
276 viewed_date = date(*[int(x)for x in viewed_date.split('-') if x != ''])
278 if lessthan:
279 self._queryset = self._queryset.filter(lastviewed__date__lte=viewed_date).exclude(lastviewed=longTimeAgo)
280 else:
281 self._queryset = self._queryset.filter(lastviewed__date__gte=viewed_date).exclude(lastviewed=longTimeAgo)
283 def _new_recipes(self, new_days=7):
284 # TODO make new days a user-setting
285 if not self._new:
286 return
287 self._queryset = self._queryset.annotate(
288 new_recipe=Case(
289 When(created_at__gte=(timezone.now() - timedelta(days=new_days)), then=('pk')),
290 default=Value(0),
291 )
292 )
294 def _recently_viewed(self, num_recent=None):
295 if not num_recent:
296 if self._sort_includes("lastviewed"):
297 self._queryset = self._queryset.annotate(
298 lastviewed=Coalesce(Max(Case(When(viewlog__created_by=self._request.user, viewlog__space=self._request.space, then="viewlog__pk"))), Value(0))
299 )
300 return
302 num_recent_recipes = (
303 ViewLog.objects.filter(created_by=self._request.user, space=self._request.space)
304 .values('recipe').annotate(recent=Max('created_at')).order_by('-recent')[:num_recent]
305 )
306 self._queryset = self._queryset.annotate(recent=Coalesce(Max(Case(When(pk__in=num_recent_recipes.values('recipe'), then='viewlog__pk'))), Value(0)))
308 def _favorite_recipes(self, times_cooked=None):
309 if self._sort_includes('favorite') or times_cooked:
310 less_than = '-' in (str(times_cooked) or []) and not self._sort_includes('-favorite')
311 if less_than:
312 default = 1000
313 else:
314 default = 0
315 favorite_recipes = (
316 CookLog.objects.filter(created_by=self._request.user, space=self._request.space, recipe=OuterRef('pk'))
317 .values('recipe')
318 .annotate(count=Count('pk', distinct=True))
319 .values('count')
320 )
321 self._queryset = self._queryset.annotate(favorite=Coalesce(Subquery(favorite_recipes), default))
322 if times_cooked is None:
323 return
325 if times_cooked == "0":
326 self._queryset = self._queryset.filter(favorite=0)
327 elif less_than:
328 self._queryset = self._queryset.filter(favorite__lte=int(times_cooked.replace('-', ''))).exclude(favorite=0)
329 else:
330 self._queryset = self._queryset.filter(favorite__gte=int(times_cooked))
332 def keyword_filters(self, **kwargs):
333 if all([kwargs[x] is None for x in kwargs]):
334 return
335 for kw_filter in kwargs:
336 if not kwargs[kw_filter]:
337 continue
338 if not isinstance(kwargs[kw_filter], list):
339 kwargs[kw_filter] = [kwargs[kw_filter]]
341 keywords = Keyword.objects.filter(pk__in=kwargs[kw_filter])
342 if "or" in kw_filter:
343 if self._include_children:
344 f_or = Q(keywords__in=Keyword.include_descendants(keywords))
345 else:
346 f_or = Q(keywords__in=keywords)
347 if "not" in kw_filter:
348 self._queryset = self._queryset.exclude(f_or)
349 else:
350 self._queryset = self._queryset.filter(f_or)
351 elif "and" in kw_filter:
352 recipes = Recipe.objects.all()
353 for kw in keywords:
354 if self._include_children:
355 f_and = Q(keywords__in=kw.get_descendants_and_self())
356 else:
357 f_and = Q(keywords=kw)
358 if "not" in kw_filter:
359 recipes = recipes.filter(f_and)
360 else:
361 self._queryset = self._queryset.filter(f_and)
362 if 'not' in kw_filter:
363 self._queryset = self._queryset.exclude(id__in=recipes.values('id'))
365 def food_filters(self, **kwargs):
366 if all([kwargs[x] is None for x in kwargs]):
367 return
368 for fd_filter in kwargs:
369 if not kwargs[fd_filter]:
370 continue
371 if not isinstance(kwargs[fd_filter], list):
372 kwargs[fd_filter] = [kwargs[fd_filter]]
374 foods = Food.objects.filter(pk__in=kwargs[fd_filter])
375 if "or" in fd_filter:
376 if self._include_children:
377 f_or = Q(steps__ingredients__food__in=Food.include_descendants(foods))
378 else:
379 f_or = Q(steps__ingredients__food__in=foods)
381 if "not" in fd_filter:
382 self._queryset = self._queryset.exclude(f_or)
383 else:
384 self._queryset = self._queryset.filter(f_or)
385 elif "and" in fd_filter:
386 recipes = Recipe.objects.all()
387 for food in foods:
388 if self._include_children:
389 f_and = Q(steps__ingredients__food__in=food.get_descendants_and_self())
390 else:
391 f_and = Q(steps__ingredients__food=food)
392 if "not" in fd_filter:
393 recipes = recipes.filter(f_and)
394 else:
395 self._queryset = self._queryset.filter(f_and)
396 if 'not' in fd_filter:
397 self._queryset = self._queryset.exclude(id__in=recipes.values('id'))
399 def unit_filters(self, units=None, operator=True):
400 if operator != True:
401 raise NotImplementedError
402 if not units:
403 return
404 if not isinstance(units, list):
405 units = [units]
406 self._queryset = self._queryset.filter(steps__ingredients__unit__in=units)
408 def rating_filter(self, rating=None):
409 if rating or self._sort_includes('rating'):
410 lessthan = '-' in (rating or [])
411 reverse = 'rating' in (self._sort_order or []) and '-rating' not in (self._sort_order or [])
412 if lessthan or reverse:
413 default = 100
414 else:
415 default = 0
416 # TODO make ratings a settings user-only vs all-users
417 self._queryset = self._queryset.annotate(rating=Round(Avg(Case(When(cooklog__created_by=self._request.user, then="cooklog__rating"), default=default))))
418 if rating is None:
419 return
421 if rating == "0":
422 self._queryset = self._queryset.filter(rating=0)
423 elif lessthan:
424 self._queryset = self._queryset.filter(rating__lte=int(rating[1:])).exclude(rating=0)
425 else:
426 self._queryset = self._queryset.filter(rating__gte=int(rating))
428 def internal_filter(self, internal=None):
429 if not internal:
430 return
431 self._queryset = self._queryset.filter(internal=internal)
433 def book_filters(self, **kwargs):
434 if all([kwargs[x] is None for x in kwargs]):
435 return
436 for bk_filter in kwargs:
437 if not kwargs[bk_filter]:
438 continue
439 if not isinstance(kwargs[bk_filter], list):
440 kwargs[bk_filter] = [kwargs[bk_filter]]
442 if "or" in bk_filter:
443 f = Q(recipebookentry__book__id__in=kwargs[bk_filter])
444 if "not" in bk_filter:
445 self._queryset = self._queryset.exclude(f)
446 else:
447 self._queryset = self._queryset.filter(f)
448 elif "and" in bk_filter:
449 recipes = Recipe.objects.all()
450 for book in kwargs[bk_filter]:
451 if 'not' in bk_filter:
452 recipes = recipes.filter(recipebookentry__book__id=book)
453 else:
454 self._queryset = self._queryset.filter(recipebookentry__book__id=book)
455 if 'not' in bk_filter:
456 self._queryset = self._queryset.exclude(id__in=recipes.values('id'))
458 def step_filters(self, steps=None, operator=True):
459 if operator != True:
460 raise NotImplementedError
461 if not steps:
462 return
463 if not isinstance(steps, list):
464 steps = [steps]
465 self._queryset = self._queryset.filter(steps__id__in=steps)
467 def build_fulltext_filters(self, string=None):
468 if not string:
469 return
470 if self._fulltext_include:
471 vectors = []
472 rank = []
473 if 'name' in self._fulltext_include:
474 vectors.append('name_search_vector')
475 rank.append(SearchRank('name_search_vector', self.search_query, cover_density=True))
476 if 'description' in self._fulltext_include:
477 vectors.append('desc_search_vector')
478 rank.append(SearchRank('desc_search_vector', self.search_query, cover_density=True))
479 if 'steps__instruction' in self._fulltext_include:
480 vectors.append('steps__search_vector')
481 rank.append(SearchRank('steps__search_vector', self.search_query, cover_density=True))
482 if 'keywords__name' in self._fulltext_include:
483 # explicitly settings unaccent on keywords and foods so that they behave the same as search_vector fields
484 vectors.append('keywords__name__unaccent')
485 rank.append(SearchRank('keywords__name__unaccent', self.search_query, cover_density=True))
486 if 'steps__ingredients__food__name' in self._fulltext_include:
487 vectors.append('steps__ingredients__food__name__unaccent')
488 rank.append(SearchRank('steps__ingredients__food__name', self.search_query, cover_density=True))
490 for r in rank:
491 if self.search_rank is None:
492 self.search_rank = r
493 else:
494 self.search_rank += r
495 # modifying queryset will annotation creates duplicate results
496 self._filters.append(Q(id__in=Recipe.objects.annotate(vector=SearchVector(*vectors)).filter(Q(vector=self.search_query))))
498 def build_text_filters(self, string=None):
499 if not string:
500 return
502 if not self._filters:
503 self._filters = []
504 # dynamically build array of filters that will be applied
505 for f in self._icontains_include:
506 self._filters += [Q(**{"%s__icontains" % f: self._string})]
508 for f in self._istartswith_include:
509 self._filters += [Q(**{"%s__istartswith" % f: self._string})]
511 def build_trigram(self, string=None):
512 if not string:
513 return
514 if self._trigram:
515 trigram = None
516 for f in self._trigram_include:
517 if trigram:
518 trigram += TrigramSimilarity(f, self._string)
519 else:
520 trigram = TrigramSimilarity(f, self._string)
521 self._fuzzy_match = (
522 Recipe.objects.annotate(trigram=trigram)
523 .distinct()
524 .annotate(simularity=Max('trigram'))
525 .values('id', 'simularity')
526 .filter(simularity__gt=self._search_prefs.trigram_threshold)
527 )
528 self._filters += [Q(pk__in=self._fuzzy_match.values('pk'))]
530 def _makenow_filter(self, missing=None):
531 if missing is None or (isinstance(missing, bool) and missing == False):
532 return
533 shopping_users = [*self._request.user.get_shopping_share(), self._request.user]
534 onhand_filter = Q(steps__ingredients__food__onhand_users__in=shopping_users) # food onhand
535 # ignore substitutions when also using the never_used_food filter
536 if not self._never_used_food:
537 # or substitute food onhand
538 onhand_filter |= (Q(steps__ingredients__food__substitute__onhand_users__in=shopping_users)
539 | Q(steps__ingredients__food__in=self.__children_substitute_filter(shopping_users))
540 | Q(steps__ingredients__food__in=self.__sibling_substitute_filter(shopping_users))
541 )
542 makenow_recipes = (
543 Recipe.objects.annotate(
544 count_food=Count("steps__ingredients__food__pk", filter=Q(steps__ingredients__food__isnull=False), distinct=True),
545 count_onhand=Count("steps__ingredients__food__pk", filter=onhand_filter, distinct=True),
546 count_ignore_shopping=Count(
547 "steps__ingredients__food__pk", filter=Q(steps__ingredients__food__ignore_shopping=True, steps__ingredients__food__recipe__isnull=True), distinct=True
548 ),
549 has_child_sub=Case(When(steps__ingredients__food__in=self.__children_substitute_filter(shopping_users), then=Value(1)), default=Value(0)),
550 has_sibling_sub=Case(When(steps__ingredients__food__in=self.__sibling_substitute_filter(shopping_users), then=Value(1)), default=Value(0)),
551 )
552 .annotate(missingfood=F("count_food") - F("count_onhand") - F("count_ignore_shopping"))
553 .filter(missingfood=missing)
554 )
555 self._queryset = self._queryset.distinct().filter(id__in=makenow_recipes.values("id"))
557 def _never_used_food_filter(self):
558 # filters recipes to include foods that have never been used
559 if not self._never_used_food:
560 return
561 self._queryset = self._queryset.filter(steps__ingredients__food__in=Food.objects.filter(~Q(ingredient__step__recipe__cooklog__isnull=False)).distinct())
563 @staticmethod
564 def __children_substitute_filter(shopping_users=None):
565 children_onhand_subquery = Food.objects.filter(path__startswith=OuterRef('path'), depth__gt=OuterRef('depth'), onhand_users__in=shopping_users)
566 return (
567 Food.objects.exclude( # list of foods that are onhand and children of: foods that are not onhand and are set to use children as substitutes
568 Q(onhand_users__in=shopping_users) | Q(ignore_shopping=True, recipe__isnull=True) | Q(substitute__onhand_users__in=shopping_users)
569 )
570 .exclude(depth=1, numchild=0)
571 .filter(substitute_children=True)
572 .annotate(child_onhand_count=Exists(children_onhand_subquery))
573 .filter(child_onhand_count=True)
574 )
576 @staticmethod
577 def __sibling_substitute_filter(shopping_users=None):
578 sibling_onhand_subquery = Food.objects.filter(
579 path__startswith=Substr(OuterRef('path'), 1, Food.steplen * (OuterRef('depth') - 1)), depth=OuterRef('depth'), onhand_users__in=shopping_users
580 )
581 return (
582 Food.objects.exclude( # list of foods that are onhand and siblings of: foods that are not onhand and are set to use siblings as substitutes
583 Q(onhand_users__in=shopping_users) | Q(ignore_shopping=True, recipe__isnull=True) | Q(substitute__onhand_users__in=shopping_users)
584 )
585 .exclude(depth=1, numchild=0)
586 .filter(substitute_siblings=True)
587 .annotate(sibling_onhand=Exists(sibling_onhand_subquery))
588 .filter(sibling_onhand=True)
589 )