So, I am trying to do this the right way using relations, rather than just crafting some SQL, but so far, all I am getting is a headache.
I have tables (and matching models) that are something like this:
fruit -< fruitMixMembers >- fruitMixes -< fruitBowls
the -< indicating a "has many" relationship, with a matching "belongs to" in each model.
so, fruit rows have an id,
fruitMixMembers has ids of multiple fruit and a id of a fruitMix
and fruitBowls has an id of a fruit mix and a pk identifying the bowl
so id like to find all the bowls that have a fruit of name "apple" in them.
in SQL I would do:
SELECT fruitBowls.* from fruitBowls B, fruitMixes M, fruitMixMembers X, fruit F WHERE F.name=‘Apple’ AND F.id = X.fruitId AND X.fruitMixId=M.id AND M.id=B.fruitMixId
But my head scartching has so far failed to get me the correct sequence of calls in Yii
$bowls = FruitBowls::model()->with(‘fruitMixes’)
is about as far as I get before Things Go Wrong.
Someone point me inthe right direction?