For future reference, I solved the problem by defining views at the database layer for each model that computes the number of questions per model instance, ex:
select * from subjectQuestionCounts; +------------+-----------------------+---------------+ | subject_id | name | questionCount | +------------+-----------------------+---------------+ | 1 | Subject1 | 2 | | 2 | Subject2 | 0 | | 4 | Subject3 | 0 | +------------+-----------------------+---------------+ 3 rows in set (0.00 sec) Then created a model for subjectQuestionCount and associated it with the Subject model. Works for me and I am very happy!!!! Thanks for those who viewed and replied to my question. On Sunday, August 18, 2013 2:14:49 AM UTC-4, Jeremy Burns wrote: > > counterCache is your friend. > > Jeremy Burns > Class Outfit > > http://www.classoutfit.com > > On 17 Aug 2013, at 23:02:51, Nicholas Amon <[email protected]<javascript:>> > wrote: > > Hi, > > I have the following model relationships > > Question belongs to Chapter > Chapter belongs to Unit > > I want to add a virtual field in the Unit model that counts how many > questions a unit has. I tried this: > > public $virtualFields = array( > "questionCount" => 'SELECT COUNT(*) FROM questions as Question JOIN > chapters as Chapter ON (Question.chapter_id = Chapter.id) JOIN units as > Unit ON (Chapter.unit_id = Unit.id)' > ); > > The problem here is that it returns the same value for all untis. The > issue here is that I want the count for each Unit instance. In other > words, I would like to add a where clause so that the query becomes > something similar to: > > public $virtualFields = array( > "questionCount" => 'SELECT COUNT(*) FROM questions as Question JOIN > chapters as Chapter ON (Question.chapter_id = Chapter.id) JOIN units as > Unit ON (Chapter.unit_id = Unit.id) WHERE Unit.id = > CAKE_REPLACE_WITH_CURRENT_ID' > ); > > How do I get cake to CAKE_REPLACE_WITH_CURRENT_ID to be the ID of the > current Unit instance that is being retrieved? > > I hope I have made my question clear. Thanks in advance. > > -- > Like Us on FaceBook https://www.facebook.com/CakePHP > Find us on Twitter http://twitter.com/CakePHP > > --- > You received this message because you are subscribed to the Google Groups > "CakePHP" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] <javascript:>. > To post to this group, send email to [email protected]<javascript:> > . > Visit this group at http://groups.google.com/group/cake-php. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- Like Us on FaceBook https://www.facebook.com/CakePHP Find us on Twitter http://twitter.com/CakePHP --- You received this message because you are subscribed to the Google Groups "CakePHP" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/cake-php. For more options, visit https://groups.google.com/groups/opt_out.
