advanced ingredient search question

[Cubangirl]

Wonderful discussion gentlemen. I too have augmented my type tags, renamed some and deleted others which were meaningless for my needs. I have been able to run queries of the db, for example a count of recipes that used unsalted butter. I went in and changed all the measures to my preferred (TBS. tsp. etc.) rather than using the cumbersome replace within the program. Having learned the hard way, I always make a copy and work of that when I am trying new stuff. Before I realized that the ingredients often had minimally different versions, I had real trouble doing ingredient searches. I have slowly and methodically changed the ingredient db so that I don't have to go through half a page of cuts of beef and separated inventory items so I could actually use the inventory as a guide. I still don't understand the reason for having salted and unsalted butter as the same inventory item. I use the latter way more than the former and replace them at different amounts (down to 3 lbs for unsalted, 1 lb. for salted).
BTW, I have found that the easiest way to find what I can make with a particular ingredient is to click on that ingredient, e.g. I had half a savoy cabbage, so I clicked on savoy cabbage and it gave me the recipes that used it. Would not work for butter, flour or chocolate, but would work for most veggies, obviously not for onion, garlic, and a few others. I look forward to hearing about more of your tweaks.

[Lee Grainger]

Just as a matter of interest, what do you think the SQL statement is that would yield the desired results?

Lee

[Dan Sanderson]

I'm no SQL expert, but here it is in words more or less:

select the list of unique recipe_id from the list of ingredients where: [ingredient_text contains 'chicken'] and [[ingredient_text does not contain 'stock'] and [ingredient_text does not contain 'broth']]

[not sure of the right bracketing here...]

This requires the select be on the set of recipe_ingredients, not on the concatinated set of ingredient texts contained in the 'ingredientsearchstring' element in the t_recipe table.

You then have to take the duplicates out of the set to account for the case of two or more 'chicken' ingedients [say chicken thighs on one line and chicken legs on anther].

Obviously there is a performance consideration here by searching the ingredients table instead of the pre-prepared search string in the recipe table, but I can't see how to do it once the texts are all concatinated together, even with the vertical bar separating them.

However, once you are on SQL server this should be doable as a database stored procedure and let the database engine worry about efficiency.

If we had some sort of 'back door' spot to plug in an arbitrary SQL statement that returned a set of recipe_id's to the display module [I assume this is what your search module does], we would be free to concoct any weird sort of 'organizations' we wanted. [And probably causing you support headaches from people who shouldn't be using such a feature:]

[Lee Grainger]

Thanks, Dan. But I would still like to see the SQL statement.

Lee

[Dan Sanderson]

As I said, I'm no SQL expert, I'll give it some thought and see what I can do. Maybe there's someone out there with a better handle on SQL syntax??

[I've been retired from the software game for about 12 yrs now, and wiped most of it from memory....]

[Cubangirl]

Ditto here, I've been retired now 5 years and avoided anything that looked like a database for the first 4. I have played a little but have not attempted anything complicated mainly from lack of time and a slow desktop. Now have an i7 running 64 w7 pro, MS office pro 2007, so I have the power, but alas it is tax time, so it will be April before I get to play again. I will work on it then. Now if I could absorb xml by osmosis, I could have so much fun. I do agree it should be set so only someone who knows what sql is would be tempted to use it. Still we might come up with a simplified version that would help all.

[Dan Sanderson]

Lee, here's an SQL statement that will return a list of the recipeid's that have 'chicken' and optionally 'chicken stock' or 'chicken broth', but does not include recipeid's that only have stock or broth. [except for the sort of pathalogical case where the ingredient is something like "chicken thighs marinated in chicken broth'.

I've tested this in Access and it seems to do what I intended:

select distinct
recipeid
from t_recipeingredient
where (
(ingredienttext like "*chicken*")
and not (ingredienttext like "*broth*")
and not (ingredienttext like "*stock*")
)
;

BTW: I don't have a very big database, only about 1300 recipes, but the query gives sub-second response.

[Lee Grainger]

Excellent. Thanks. The reason that I asked was that if I was the only one who knew how to generate the SQL statements then the point was moot.

So, given that I am not prepared to add ad-hoc queries at this time (beyond those possible using the advanced tab of the search window), here is your workaround:

1) Change your select statement into an update statement that will update the colorflag column to a color of your choice.

2) Create a search in Living Cookbook that returns all recipes flagged with that color.

While it is not the solution you were looking for, it works and you don't have to wait for me to develop and test anything. And, remember, this approach is only required for those few queries that can't be constructed using Living Cookbook's advanced search tab.

Enjoy!

Lee

[Dan Sanderson]

Thanks Lee. Good suggestion.

I'm leary of actually updating the database, afraid of screwing something up. I do the messing around on a copy of LC's database. I'll probably just add chicken to the 'type' list and use 'paste special' to apply it. I hope you consider adding an custom search feature in some future release, but I understand it would be a low payback thing for you.

Thanks for your participation in this thread.

Dan

[Lee Grainger]

Always a pleasure.

Cheers.

Lee

[Astilbe]

This is all very complicated, so perhaps I don't belong here.

Is there a way to do a search based on reviews? I always rate a recipe after preparing it the first time (based on family preferences).

That would be helpful ... to me

[Gringamuyloca]

Originally Posted by Astilbe

This is all very complicated, so perhaps I don't belong here. Oh yes you do... you asked for help, so you definitely belong!

Is there a way to do a search based on reviews? Yes there is! Follow the instructions below I always rate a recipe after preparing it the first time (based on family preferences).

That would be helpful ... to me

I'm not sure how you rate your recipes, but if it didn't score a three.. well, for me, probably don't want to see it again unless it's all I have on hand.


what I did:
1. Click on Recipe
2. Right Click on recipe searches
3. Click on New Folder
4. Click on Search Folder
5. Name the folders Ratings.. or what You would like
6. Click on Sub-Folder and browse to Recipe Type Searches
7. Add comments if you'd like ( This is a 3 Star or better search) and then click ok.
8. A blank window will appear,
9. Click on New Search up in the bar.
10. Give your Search a name I called mine 3 Star +
11. Tab to Search folder and click on Recipe Type Searches to expand the 'tree'
12. You should see your new folder called 'Ratings', click on the folder icon
13. Click OK.
14. Being that you want to search your recipes the "Search in:' can be left default to 'Recipes'
15. You'll see 2 tabs, 'Basic' and 'Enhanced'.. click on 'Enhanced'
16. At the bottom of the window you will see 3 fields. Column, Condition, Value.
17. Click on the Column field and scroll down to 'Average Rating' and select.
18. Mouse or Tab over to "Condition', Click and select the > (greater than symbol)
19.Tab or Mouse over to the 'Value' field and enter 3. ( This will return all recipes with a rating of 3 stars or more.
20. Click on the 'Add To List' button.
21. Click on OK at the bottom of the window.


Presto Bingo, there should be a list of recipes that have your "Average Rating" criteria(s)
If you only want to see 5 Star ratings, then the "Condtion" field should be set to = and the "Value" field set to 5

Sorry my 'formating' isn't consistent, but it should be good enough to get you by... any problems, post back as you will receive help... if you ask!

Cheers