I have a table “Categories” with hierarchical data. Model uses Tree behavior.
I have another table for an entity “Ingredients” set up with belongsTo a Category.
I’d like to build a jQuery ajax search/select field for ingredients. The ultimate aim is to capture the Ingredient.id to be the option value that will get sent with the form data when the form is submitted. The wrinkle is that I would like to have three elements in my array for the jQuery widget (Ingredient.id, Ingredient.name, Concatenated Category hierarchy). The concatenated string is what will be used to match against what the user is typing… the name is what will populate the select field when the user accepts a result… the id is what will get sent to the server.
That last element, the concatenated string… it’s simply the string you would get if you concatenated the Category.name of the category assigned to the ingredient, plus the Category.name of every one of its ancestors up the tree.
SO: Imagine you’re searching for Stevia. That would belongTo the category “Artificial Sweetener”, whose parent is “Sweeteners”, and then whose parent is “Desserts”. This is a quick and dirty example. There may be many more steps to the hierarchy. The user might type ‘stevia’ and match, or might type ‘artificial’ or ‘sweetener’ (and see Stevia and others as options to select from)… or might type ‘desserts’ (and see a lot more stuff to select from, including Stevia).
Anyway. The question is:
I’ve been trying to query this list using a single CakePHP query. It’s failed so far. It requires creating at least one subquery (and I think probably two) and that final subquery must also “know” about the id value found per record in the main query. This exceeds my SQL abilities. I can write both the main query and the subquery that works if I hardcode the id for the category I’m looking for in the subquery… but then ALL the data from all the categories ends up concatenated and assigned to the first record from the main query.
So part of me is thinking: this wouldn’t be hard if I just ran my main query and then used foreach on the results set to run a new query each time per each record to look up the threaded category, concatenate them and append it to my first result set.
But I feel stupid running so many queries. I feel like this probably can and should be done in a single query.
So… do I try to figure out what that single query should be (and then just do raw SQL in my controller? or maybe move it to my model instead)
Or do I try to figure out a way to do this using CakePHP’s built in associations handling and query/subquery building using just ->find() ?
Or do I stop the madness, break down, and just run one query and then foreach it with another query for each record? This approach may also prove less opaque when trying to maintain the code in the future.
What are the performance implications? Are they real?