SELECT
category.name AS Category,
count(category.name) as Count
FROM category
JOIN recipe
ON category.name = recipe.category
GROUP BY category.name;
Or at least i think i do
Lemme try and put it in words: I have a table recipes and a table categories. I want to display the category and the number of recipes in that category on the app. I guess that would have been easier if i had a single category per recipe, BUT (I had to do this in HARD mode from the get-go) i use “This field is a List” as a category field per recipe. I have absolutely no idea how to count an array
Also, is this the best way to do it? Since these counts will be performed REGULARLY on page-display. If it would be less performance intense i would gladly use another storage method.
All help appreciated!!
Yes, “hard mode” is to have a list of categories on the recipe, “easier mode” is to also have a list of recipe’s on the category … but let’s do hard first.
You will have a repeating group with search for categories on this recipe.
Then in the repeating group you will need a text box which is another search for Recipes … returning the :count where recipe category contains Current Cell’s Category.
Old Uncle Nigel’s top tip for bubble database design is to think about how you want to QUERY your data just as much as how you want to store it. You only have to store it ONCE, but you are going to query it lots of time. Make you life easier.
So add a “List of Recipes” field to your Category (List, field type Recipe) and then when you create the Recipe, you will need to do a “Make Changes to a List” … the list being the list of Categories for this Recipe and the change being “List of Recipes” Add “The Recipe from Step 1”.
Then, in your list of Categories you just count the list of Recipes.
A bit like this …
(this is possible colours, so not exactly the same data as above, but you should get the idea I hope).
This way you should be able to sort by counts too… which is a lot harder in hard mode
