I have progressed a little with this, but am still struggling. I seem
to get hung up on the simplest of things with CakePHP, which is so
frustrating. I am prepared to be humiliated with an equally simple
answer to my problem.
I have a table called location_types with acts as a tree (with a self
join on id => parent_id). I have a $belongsTo table called locations,
which is joined to location_types on locations.location_type_id.
I want my location types index view to have a column that shows the
number of locations within the location type for that row.
Here is some sample code (that doesn't work):
$this->paginate['LocationType'] = array(
'contain' => array(
'ParentLocationType' => array(
'fields' => array(
'id',
'name'
)
),
'Location' => array(
'fields' => array(
'id',
'location_type_id'
)
)
),
'fields' => array(
'LocationType.id',
'LocationType.name',
'LocationType.parent_id',
'ParentLocationType.id',
'ParentLocationType.name',
'COUNT(LocationType.id) AS countLocation'
),
'group' => array(
'LocationType.id'
),
'order' => array ('LocationType.lft' => 'asc')
);
$locationTypes = $this->paginate('LocationType');
$this->set('locationTypes', $locationTypes);
Although I am including the Location model in my contain statement it
won't recognise COUNT(Location.id). I notice that the SQL that Cake
outputs does not include the locations table, which is clearly why
COUNT(Location.id) is not recognised. I have experimented with various
permutations by changing the group array and the field inside the COUNT
() function, but I cannot get the result I am looking for.
What am I doing wrong, please?
On Dec 22, 11:04 am, Jeremy Burns <jeremybu...@me.com> wrote:
> Jamal - I really appreciate your reply - thank you.
>
> This approach might well work, but I am sure there must be a simpler
> way. I want to make the database do as little work as possible. In
> plain old SQL I can do this:
>
> SELECT a.id, a.name
> count(b.id) as subCount
> from tablea a
> left join tableb b
> on (b.type_id = a.id)
> group by (a.id)
>
> ...which returns me all of the rows in tablea with an extra column
> that shows me the count of rows in tableb that have the type_id in the
> row. One query and bosh I have my results with the count.
>
> How do I do this in Cake?
>
> On Dec 22, 9:13 am, Jamal Aziz <jamalsaepula...@gmail.com> wrote:
>
>
>
> > Hi Jeremy,
>
> > I think you can do that in your model with afterFind callback. For
> > example:
>
> > class MyModel extends AppModel{
> > var $actsAs = array('Tree');
>
> > function afterFind($results, $primary){
> > // for multiple result
> > if(is_array($results)){
> > if(is_array($results[0])){
> > foreach($results as $key => $val){
> >
> > $results[$key][$this->alias]['childCount'] = $this->childCount
> > ($results[$key][$this->alias][$this->primaryKey]);
> > }
> > } else { // for single result
> > $results[$this->alias]['childCount'] =
> > $this->childCount($results
> > [$key][$this->alias][$this->primaryKey]);
> > }
> > }
> > }
>
> > }
>
> > Note: I am not test this code myself.
>
> > Hope this help.
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
