[BUG] - Cookbook using a Tag filter doesn't find all recipes with that tag

[jwismar]

First Check

• This is not a feature request.
• I added a very descriptive title to this issue (title field is above this).
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the Mealie documentation, with the integrated search.
• I already read the docs and didn't find an answer.
• This issue can be replicated on the demo site (https://demo.mealie.io/).

What is the issue you are experiencing?

I have a couple hundred recipes in Mealie, including many cocktails. 34 of the the cocktails have the tag "Tiki" amongst other tags. I created a cookbook called "Tiki Cocktails" which should pull recipes that have "contains all of" the single tag "Tiki". Only 22 recipes get pulled into the cookbook.

I created a second cookbook called Tiki 2 using the same filter criteria, and it ends up containing the same 22 recipes. I also attempted this using an "is one of" filter, and get the same results.

If I look at the main recipes page, and filter using tag "Tiki", all 34 recipes get displayed.

I was not able to replicate this behavior on the demo site, but my test recipes there are obviously much simpler and don't have multiple Tags amongst other confounding factors.

Steps to Reproduce

Create a new cookbook. Select filter criteria for Tag called "Tiki". Save the cookbook. View the cookbook. Observe that only a subset of expected recipes are included.

Please provide relevant logs

Docker log contains no error messages, just access logs. Relevant request looks like:
INFO 2024-12-29T12:42:24 - [10.111.1.5:54334] 200 OK "GET /api/recipes?page=1&perPage=64&orderBy=name&orderDirection=asc&cookbook=tiki-cocktails HTTP/1.1"

Mealie Version

Docker image: ghcr.io/mealie-recipes/mealie:nightly
Build: 23a85f51d20b12ee93f7b25d312d70d18be0e92d

Deployment

Unraid

Additional Deployment Details

No response

[fakethinkpad85]

Not sure but same bug might apply to Categories as well, having the same issue using categories. Guessing they use the same filter logic.

[PancakeZik]

+1

[ChipSkylark37]

I wonder how this could be replicated.

I have 12 recipes tagged with "Want to Make" and a Cookbook that shows all of those successfully. The inclusion rule is very simple so if not everything is being displayed, something must be going on.

For the ones that don't show up, what if you REMOVE, save, ADD the tag back?

[PancakeZik]

In my case the problem seems to happen even more evidently with categories.

This one has 100+ recipes (which looks correct).
However, if I change it to this:

Then I get about 50 recipes in total (but there should be even more, giving that I'm adding an extra category that could be included.

This one also gives me only 50 more or less, instead of the 100+, but it should give the same results as the first one:

So yes, there's something funny going on with the filtering logic there.

[fakethinkpad85]

in my case the recipies page with category filter looks correct (4 recepies)

but the cookbok page looks like this, empty,

Now, that is a fairly new Category (Smoothies), and i have older categories with 50+ recepies and i have noticed that older recepies shows up correctly in cookbooks, but newer ones rarely (if any?) shows up on in the Cookbooks. So this is most likely a newer issue that is dependent on version when recipe was created.

[fakethinkpad85]

Would be happy to add a test user, backup and publish database if it helps.

[jwismar]

Extra data point: I just did the test with Categories, and did NOT see the same behavior that I'm seeing with Tags. Specifically, I currently have 165 recipes with category "Cocktail", and 3 with category "Cocktail Mixers". A cookbook with category "One of 'Cocktails'" shows all 165 recipes. "All of 'Cocktails'" shows 165. And "One of 'Cocktails' or 'Cocktail Mixers'" shows 168, as expected.

One observation is that all of my recipes have exactly 1 category, but one-to-many tags. For folks who are seeing this issue with categories, do you have multiple categories assigned to recipes?

[fakethinkpad85]

Most of mine have 1 category (i dont use tags), however, one of my categories have two recipies that has multiple categories. in the Recipe page with filter they both show up (again both these has multiple categories)

on the Cookbook page, only one of them are shown,

but like i said, most only have 1 categories and some show and some dont, seem more related to recipe age in my case.

[jwismar]

Interesting. I went to see whether my missing Tiki cocktails were the oldest ones -- I didn't remember seeing a correlation like that -- and the Tiki cookbook is working now, and showing all of the expected recipes. I literally did not change anything with that cookbook, although I was experimenting with different cookbooks a few minutes ago as described above.

[fakethinkpad85]

Did you happen to change sorting inside any cookbooks? tried changing sorting on one of them and now everything is displayed for me as well.

Clearly something is broken here even if it does seem to show correctly at first glance now.

[fakethinkpad85]

Noticed that if you sort by Rating, all recipies that dont have a rating is hidden.

[michael-genson]

For everyone running into issues, can you please post a screenshot of your cookbook settings (like above) as well as the response from the API when fetching said cookbook?

For example:

{
  "name": "Family Cookbook 1",
  "description": "",
  "slug": "family-cookbook-1",
  "position": 1,
  "public": false,
  "queryFilterString": "recipe_category.id IN [\"f81c88d1-5b3d-4cd7-80b3-d2b23da41af4\"]",
  "groupId": "7b3fc078-c988-413f-b8a2-a8c0a6e03e34",
  "householdId": "a374edd1-3b53-4b71-897e-f8198d7333b0",
  "id": "c3098bd4-ad40-4062-b8c7-b1ebe8bed7a3",
  "queryFilter": {
    "parts": [
      {
        "leftParenthesis": null,
        "rightParenthesis": null,
        "logicalOperator": null,
        "attributeName": "recipe_category.id",
        "relationalOperator": "IN",
        "value": [
          "f81c88d1-5b3d-4cd7-80b3-d2b23da41af4"
        ]
      }
    ]
  },
  "recipes": [...]
}

This data, along with a description of what you expect to see vs what you're actually seeing. I haven't been able to reproduce any issues locally, so I need more data to debug

[fakethinkpad85]

@michael-genson Not sure how to see/test the API result - Quick question, are you able to reproduce the issue where a recipe is not
being displayd in cookbook when sorting is set to Rating (and recipe not have a rating)?

This might have been the culprit for me all along.

[ChipSkylark37]

Can confirm, for me, I have all my recipes disappear if I sort by rating

My tag is 'Want to Cook' and I've not rated anything so that tracks.

So it's not necessarily an API issue, more a filtering issue that the 'Rating' button doesnt show a '0' / 'unrated' rating?

[michael-genson]

To see the network request, you can open the console on your browser (usually F12). It also helps to turn on the XHR filter (which will only show you the server requests)

Regarding the ratings, I'm not at my computer right now, but that sounds like a similar issue we ran into when we first implemented the last made sort

[ChipSkylark37]

The same issue applies to 'recipes' then. If you filter with a Category / Tag and then 'SORT BY' Rating, anything that isnt rated disappears.

[jwismar]

When I was seeing this issue, I was not changing any of the sorting settings in the cookbook config. Almost none of my recipes have ratings.

[fakethinkpad85]

The same issue applies to 'recipes' then. If you filter with a Category / Tag and then 'SORT BY' Rating, anything that isnt rated disappears.

Yes, however the sorting is shared between all Cookbooks but seperate for Recipe page. So i can have the Recipe page sorted by Date and Cookbooks sorted by Rating, thats probably why i first thought it was only occuring on the Cookbooks but you are correct that it applies to all pages as soon as a sort for rating is applied.

[PancakeZik]

For everyone running into issues, can you please post a screenshot of your cookbook settings (like above) as well as the response from the API when fetching said cookbook?

For example:

{
"name": "Family Cookbook 1",
"description": "",
"slug": "family-cookbook-1",
"position": 1,
"public": false,
"queryFilterString": "recipe_category.id IN ["f81c88d1-5b3d-4cd7-80b3-d2b23da41af4"]",
"groupId": "7b3fc078-c988-413f-b8a2-a8c0a6e03e34",
"householdId": "a374edd1-3b53-4b71-897e-f8198d7333b0",
"id": "c3098bd4-ad40-4062-b8c7-b1ebe8bed7a3",
"queryFilter": {
"parts": [
{
"leftParenthesis": null,
"rightParenthesis": null,
"logicalOperator": null,
"attributeName": "recipe_category.id",
"relationalOperator": "IN",
"value": [
"f81c88d1-5b3d-4cd7-80b3-d2b23da41af4"
]
}
]
},
"recipes": [...]
}
This data, along with a description of what you expect to see vs what you're actually seeing. I haven't been able to reproduce any issues locally, so I need more data to debug

Here's mine.
In my case, when both the categories are selected, more than half of the recipes that should show up do not.

{

"page": 1,
"per_page": 7,
"total": 7,
"total_pages": 1,
"items": [
    {
        "name": "Everyday Lunch and Dinner",
        "description": "",
        "slug": "everyday-lunch-and-dinner",
        "position": 1,
        "public": false,
        "queryFilterString": "tags.id IN [\"0f62cf8f-cb79-4b9d-a59c-2e645e431cf1\"] AND recipe_category.id IN [\"dd5f479a-f727-4500-a013-9f7a0dead651\",\"45471291-9911-4865-8a0a-cccfb0d75314\"]",
        "groupId": "feaa7ef5-234c-4a97-a3d2-5f3602abd2ae",
        "householdId": "7655b5bf-c08a-4579-9819-6c02928ff45d",
        "id": "476787ea-084c-450a-9686-d286562b2aa8",
        "queryFilter": {
            "parts": [
                {
                    "leftParenthesis": null,
                    "rightParenthesis": null,
                    "logicalOperator": null,
                    "attributeName": "tags.id",
                    "relationalOperator": "IN",
                    "value": [
                        "0f62cf8f-cb79-4b9d-a59c-2e645e431cf1"
                    ]
                },
                {
                    "leftParenthesis": null,
                    "rightParenthesis": null,
                    "logicalOperator": "AND",
                    "attributeName": "recipe_category.id",
                    "relationalOperator": "IN",
                    "value": [
                        "dd5f479a-f727-4500-a013-9f7a0dead651",
                        "45471291-9911-4865-8a0a-cccfb0d75314"
                    ]
                }
            ]
        }
    },

}

[fakethinkpad85]

Noticed something in the request url/xhr/fetch when setting the Rating as sorting it also adds a queryFilter to not include NULL ratings - changing Sort order it removes this.

https://XXX/api/explore/groups/home/recipes?page=1&perPage=32&orderBy=rating&orderDirection=desc&cookbook=smoothies&queryFilter=rating+IS+NOT+NULL

[michael-genson]

@PancakeZik what value are you sorting by? And, can you try changing the sort and seeing if that fixes the issue?

I have a feeling this is related to nulls and the following relationships:

• ratings
• favorites
• last made

Try to see if which recipes are missing are correlated to those properties (e.g. they all do/don't have ratings, are/aren't favorited, etc.)

The reason I suspect this is because the query we're building does some joins on those relationships

[ChipSkylark37]

I can confirm that setting it to sort by Ratings or Last Made will 'break' it from Favorites, Recipes, and Cookbooks.

Sorting by Alphabetical or something that will always have a 'value' or sorts will bring it back.

But that definitely does add to confusion - If I sort by rating, it should be from 0 - 5, not 1 - 5.

Last made should also include NULL from 'Not Made > Most Recently Made'

[PancakeZik]

@PancakeZik what value are you sorting by? And, can you try changing the sort and seeing if that fixes the issue?

I have a feeling this is related to nulls and the following relationships:

• ratings
• favorites
• last made

Try to see if which recipes are missing are correlated to those properties (e.g. they all do/don't have ratings, are/aren't favorited, etc.)

The reason I suspect this is because the query we're building does some joins on those relationships

Interesting, I investigated and in my case I don't think it's related to sorting.
I'm sorting alphabetically, and when sorting ascending, it shows 33 recipes, the last one being "Caponata"
If sorting alphabetically, WITH THE SAME CRITERIA, and choosing descending, it shows 34 recipes, the first one being "Zingy Basa" and the last one being "Sweet Chilli".
So it actually seems to select the correct recipes, but it just stops populating the list at that point, for some reason.
Sorting by updated is the same: it shows a different set if I select either ascending or descending, but all under the correct criteria still.

[michael-genson]

Hmmmm, that might have something to do with pagination then. Our page size is 30

[michael-genson]

I can confirm sorting by last made or rating is broken; that is definitely missing unmade/unrated recipes.

Try as I might I can't reproduce the tag/category issue. I've tried a bunch of different tag/category combinations with different sorts and it always matches exactly what I expect (the only exceptions being mentioned above)

[fakethinkpad85]

Not used to python but i see some suspicious code in the repo,

`class RepositoryRecipes(HouseholdRepositoryGeneric[Recipe, RecipeModel]):
user_id: UUID4 | None = None

@property
def column_aliases(self):
    if not self.user_id:
        return {}

    return {
        "last_made": self._get_last_made_col_alias(),
        "rating": self._get_rating_col_alias(),
    }

`

` def _get_rating_col_alias(self) -> sa.ColumnElement | None:
"""Computed rating which uses the user's rating if it exists, otherwise falling back to the recipe's rating"""

    effective_rating = sa.case(
        (
            sa.exists().where(
                UserToRecipe.recipe_id == self.model.id,
                UserToRecipe.user_id == self.user_id,
                UserToRecipe.rating != None,  # noqa E711
                UserToRecipe.rating > 0,
            ),`

[michael-genson]

Yeah those generate subqueries to calculate the sort values. My gut says we're probably doing an inner join instead of an outer join. Need to dig in further though and probably look at the generated SQL

[ChipSkylark37]

What file is 'sort' code under? I'd like to take a look myself, just out of curiosity. I'm more familiar with MS SQL, but im sure that can translate back to SQLite and Postgres, at least to look through a bit.

[michael-genson]

Turns out it was the frontend adding a "IS NOT NULL" constraint before sending to the backend. This comes from some old code that probably made sense at the time, but doesn't make sense now.

More here: #4908

[fakethinkpad85]

Ah good find, wondered why the fetch was adding that to the url request.