The first thing I observe is that the group statement does not include
all the non-grouped columns, which it should, like:
'group' => array(
'LocationType.id',
'LocationType.name',
'LocationType.parent_id',
'ParentLocationType.id',
'ParentLocationType.name'
),
Please try it out, enjoy,
John
On Dec 23, 8:01 am, Jeremy Burns <jeremybu...@me.com> wrote:
> 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+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
No comments:
Post a Comment