[v1.0.0b] [Task] - Implement Service Side Full Text Search

[hay-kot]

What is the problem this task addresses?

Currently the fuzzy search is implemented client side. This works good for most users, but quickly becomes overloaded when a large volume of recipes exists on the server. This is especially problematic for users with older devices. Search should be moved fully to the server side.

Doing this would also likely remove the need to load foods on recipes since we can query the database directly with filters.

Proposed/Possible Solution(s)?

Features

The all recipes endpoint should implement the following

• Filter by Food support
• Full Text search for Fuzzy or Folded matches for the following fields
  • Name
  • Description
  • Instruction Steps
  • Ingredient Notes

Implementation Details

Filter By Food

Filtering by foods should be as simple as adding the following to the Query builder

if foods:
    for food in foods:
        if isinstance(food, UUID):
            q = q.filter(RecipeModel.recipe_ingredient.any(RecipeIngredient.food_id == food))

and adding an additional foods parameter to the PaginationQuery to allow for UUID's to be passed.

With this implement we should be able to eliminate the need for loading foods which would speed up recipe loading even more

https://github.com/hay-kot/mealie/blob/88843a8e4a674603464a9f2351b81f6d590ae512/mealie/schema/recipe/recipe.py#L105

Full Text Search

Basic implementation for name and description are fairly straight forward

Need to add a search property to the PaginationQuery Model

https://github.com/hay-kot/mealie/blob/9e77a9f367d9f2cf704054842613b6ba399841a2/mealie/schema/response/pagination.py#L19-L24

And then add the following to the search query

# Text Search name and description
if pagination.search:
    q = q.filter(
        or_(
            RecipeModel.name.ilike(f"%{pagination.search}%"),
            RecipeModel.description.ilike(f"%{pagination.search}%"),
            RecipeModel.recipe_ingredient.any(
                RecipeIngredient.note.ilike(f"%{pagination.search}%"),
            ),
            RecipeModel.recipe_instructions.any(
                RecipeInstruction.text.ilike(f"%{pagination.search}%"),
            ),
        )
    )

That would go around here somewhere https://github.com/hay-kot/mealie/blob/9e77a9f367d9f2cf704054842613b6ba399841a2/mealie/repos/repository_recipes.py#L204

NOTE: This would not be true fuzzy matching, but would rely on the SQL implementation of the LIKE keyword to perform the filtering which is significantly different than what is happening now.

---

The bulk of the work for this is going to be around cleaning up the frontend search implementation to be less... shit... The in browser implementation will need to be removed and we'll need to implement a debounced search in both the pop-up search and the search page.

[fleshgolem]

I know scoring results like this, is really not much fun in basic SQL, but IMO we should at least make sure that matches on the title are ordered in front of everything else. Its been a while since i have done this, but i think you can achieve this with something like

q = q.order_by(RecipeModel.name.ilike(f"%{pagination.search}%"))