Re: Include count of nested associated object as virtual field.

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 <nichol...@gmail.com> 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 cake-php+u...@googlegroups.com.
To post to this group, send email to cake...@googlegroups.com.
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 cake-php+unsubscribe@googlegroups.com.
To post to this group, send email to cake-php@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php.
For more options, visit https://groups.google.com/groups/opt_out.