Re: Count column in index view
One more issue that has deflated my initial euphoria... The results don't paginate. How can I rectify that? On Jan 2, 4:53 am, Jeremy Burns wrote: > A success! Using your self created join code I arrived at this: > > $results = $this->LocationType->find( > 'all', > array( > 'contain' => false, > 'joins' => array( > array( > 'table' => 'locations', > 'type' => 'left', > 'alias' => 'Location', > 'foreignKey' => false, > 'conditions' => array('LocationType.id = > Location.location_type_id') > ) > ), > 'fields' => array('LocationType.id', 'LocationType.name', > 'COUNT(Location.id) as location_count'), > 'group' => array('LocationType.id', 'LocationType.name'), > ) > ); > > ...which works. Still seams long handed to me though. > > On Jan 2, 4:49 am, Jeremy Burns wrote: > > > > > I have changed my code slightly from: > > > $results = $this->LocationType->Location->find( > > to > > $results = $this->LocationType->find( > > > This does not error, but only produces the location_types that have > > locations. Therefore the results are still incomplete. > > > On Jan 2, 4:45 am, Jeremy Burns wrote: > > > > Hi John > > > > Thanks for this, but I still cannot get this to return the results I > > > need. I note that in your example the 'parent' is the author and the > > > child is the article. In my example the parent is the location_type > > > and the child is the location. Your code would exist (and work) within > > > the child (i.e. article or location) controller, whereas I need it to > > > run in the parent (i.e author or location_type) controller. When I try > > > to switch the code around I run into all sorts of errors. For example, > > > if I use your unbinding and then binding method... > > > > $this->LocationType->unbindModel( array('hasMany' => array > > > ('Location'))); > > > $this->LocationType->bindModel( array('hasOne' => array('Location'))); > > > $results = $this->LocationType->Location->find( > > > 'all', > > > array( > > > 'contain' => array('Location'), > > > 'fields' => array('LocationType.id', 'LocationType.name', > > > 'COUNT > > > (Location.id) as location_count'), > > > 'conditions' => array('Location.id' => array(1,2,3,4)), > > > 'group' => array('LocationType.id', 'LocationType.name'), > > > ) > > > ); > > > > ...it triggers the error: > > > > Model "Location" is not associated with model "Location" > > > ...which (by process of elimination) is triggered by the line: > > > > 'contain' => array('Location') > > > > Bizzarre. > > > > I also fiddled around with the self created join method with no > > > success. > > > > It strikes me that you and I have put a lot of effort into delivering > > > something extremely simple that appears to be a bug in the core code. > > > Would you agree? If so (and there are no other suggestions > > > forthcoming!) then i propose I raise a ticket. > > > > On Dec 30 2009, 10:15 am, John Andersen > > > wrote: > > > > > With the hasOne relationship, the query looks like: > > > > [code] > > > > SELECT `Author`.`id`, `Author`.`name`, COUNT(`Article`.`id`) as > > > > article_count FROM `authors` AS `Author` LEFT JOIN `articles` AS > > > > `Article` ON (`Article`.`author_id` = `Author`.`id`) WHERE > > > > `Author`.`id` IN (1, 2, 13) GROUP BY `Author`.`id`, `Author`.`name` > > > > [/code] > > > > > With the hasMany relationship, the query looks like: > > > > [code] > > > > SELECT `Author`.`id`, `Author`.`name`, COUNT(`Article`.`id`) as > > > > article_count FROM `authors` AS `Author` WHERE `Author`.`id` IN (1, 2, > > > > 13) GROUP BY `Author`.`id`, `Author`.`name` > > > > [/code] > > > > Contain does not assist in creating a LEFT JOIN, which is why > > > > Article.id is an unknown column! > > > > > As far as I remember, the issue is probably not in the Containable > > > > behaviour, but somewhere in DboSource, but I may be wrong here :) > > > > > There is another way: > > > > 1) Set contain equal false. > > > > 2) Define the joins yourself, as in the example below: > > > > [code] > > > > $results = $this->Article->Author->find( > > > > 'all', > > > > array( > > > > 'contain' => false, > > > > 'joins' => array( > > > > array( > > > > 'table' => 'articles', > > > > 'type' => 'left', > > > > 'alias' => 'Article', > > > > 'foreignKey' => false, > > > > 'conditions' => array('Article.author_id = > > > > Author.id') > > > > ) > > > > ), > > > > 'fields' => array('Author.id', 'Author.name', 'COUNT > > > > (Article.id) as article_count'), > > > > 'group' => array('Author.id
Re: Count column in index view
A success! Using your self created join code I arrived at this: $results = $this->LocationType->find( 'all', array( 'contain' => false, 'joins' => array( array( 'table' => 'locations', 'type' => 'left', 'alias' => 'Location', 'foreignKey' => false, 'conditions' => array('LocationType.id = Location.location_type_id') ) ), 'fields' => array('LocationType.id', 'LocationType.name', 'COUNT(Location.id) as location_count'), 'group' => array('LocationType.id', 'LocationType.name'), ) ); ...which works. Still seams long handed to me though. On Jan 2, 4:49 am, Jeremy Burns wrote: > I have changed my code slightly from: > > $results = $this->LocationType->Location->find( > to > $results = $this->LocationType->find( > > This does not error, but only produces the location_types that have > locations. Therefore the results are still incomplete. > > On Jan 2, 4:45 am, Jeremy Burns wrote: > > > > > Hi John > > > Thanks for this, but I still cannot get this to return the results I > > need. I note that in your example the 'parent' is the author and the > > child is the article. In my example the parent is the location_type > > and the child is the location. Your code would exist (and work) within > > the child (i.e. article or location) controller, whereas I need it to > > run in the parent (i.e author or location_type) controller. When I try > > to switch the code around I run into all sorts of errors. For example, > > if I use your unbinding and then binding method... > > > $this->LocationType->unbindModel( array('hasMany' => array > > ('Location'))); > > $this->LocationType->bindModel( array('hasOne' => array('Location'))); > > $results = $this->LocationType->Location->find( > > 'all', > > array( > > 'contain' => array('Location'), > > 'fields' => array('LocationType.id', 'LocationType.name', > > 'COUNT > > (Location.id) as location_count'), > > 'conditions' => array('Location.id' => array(1,2,3,4)), > > 'group' => array('LocationType.id', 'LocationType.name'), > > ) > > ); > > > ...it triggers the error: > > > Model "Location" is not associated with model "Location" > > ...which (by process of elimination) is triggered by the line: > > > 'contain' => array('Location') > > > Bizzarre. > > > I also fiddled around with the self created join method with no > > success. > > > It strikes me that you and I have put a lot of effort into delivering > > something extremely simple that appears to be a bug in the core code. > > Would you agree? If so (and there are no other suggestions > > forthcoming!) then i propose I raise a ticket. > > > On Dec 30 2009, 10:15 am, John Andersen > > wrote: > > > > With the hasOne relationship, the query looks like: > > > [code] > > > SELECT `Author`.`id`, `Author`.`name`, COUNT(`Article`.`id`) as > > > article_count FROM `authors` AS `Author` LEFT JOIN `articles` AS > > > `Article` ON (`Article`.`author_id` = `Author`.`id`) WHERE > > > `Author`.`id` IN (1, 2, 13) GROUP BY `Author`.`id`, `Author`.`name` > > > [/code] > > > > With the hasMany relationship, the query looks like: > > > [code] > > > SELECT `Author`.`id`, `Author`.`name`, COUNT(`Article`.`id`) as > > > article_count FROM `authors` AS `Author` WHERE `Author`.`id` IN (1, 2, > > > 13) GROUP BY `Author`.`id`, `Author`.`name` > > > [/code] > > > Contain does not assist in creating a LEFT JOIN, which is why > > > Article.id is an unknown column! > > > > As far as I remember, the issue is probably not in the Containable > > > behaviour, but somewhere in DboSource, but I may be wrong here :) > > > > There is another way: > > > 1) Set contain equal false. > > > 2) Define the joins yourself, as in the example below: > > > [code] > > > $results = $this->Article->Author->find( > > > 'all', > > > array( > > > 'contain' => false, > > > 'joins' => array( > > > array( > > > 'table' => 'articles', > > > 'type' => 'left', > > > 'alias' => 'Article', > > > 'foreignKey' => false, > > > 'conditions' => array('Article.author_id = > > > Author.id') > > > ) > > > ), > > > 'fields' => array('Author.id', 'Author.name', 'COUNT > > > (Article.id) as article_count'), > > > 'group' => array('Author.id', 'Author.name'), > > > ) > > > ); > > > [/code] > > > > But that is also not very simple! > > > > Will keep you updated, should I find a better solution! Enjoy, > > > John > > > > On Dec 30, 12:07 pm, Jeremy Burns wrote: > > > > > Thank you John - I will try this out. Strikes me as very odd that this > > > > is not much much simpler! > >
Re: Count column in index view
I have changed my code slightly from: $results = $this->LocationType->Location->find( to $results = $this->LocationType->find( This does not error, but only produces the location_types that have locations. Therefore the results are still incomplete. On Jan 2, 4:45 am, Jeremy Burns wrote: > Hi John > > Thanks for this, but I still cannot get this to return the results I > need. I note that in your example the 'parent' is the author and the > child is the article. In my example the parent is the location_type > and the child is the location. Your code would exist (and work) within > the child (i.e. article or location) controller, whereas I need it to > run in the parent (i.e author or location_type) controller. When I try > to switch the code around I run into all sorts of errors. For example, > if I use your unbinding and then binding method... > > $this->LocationType->unbindModel( array('hasMany' => array > ('Location'))); > $this->LocationType->bindModel( array('hasOne' => array('Location'))); > $results = $this->LocationType->Location->find( > 'all', > array( > 'contain' => array('Location'), > 'fields' => array('LocationType.id', 'LocationType.name', > 'COUNT > (Location.id) as location_count'), > 'conditions' => array('Location.id' => array(1,2,3,4)), > 'group' => array('LocationType.id', 'LocationType.name'), > ) > ); > > ...it triggers the error: > > Model "Location" is not associated with model "Location" > ...which (by process of elimination) is triggered by the line: > > 'contain' => array('Location') > > Bizzarre. > > I also fiddled around with the self created join method with no > success. > > It strikes me that you and I have put a lot of effort into delivering > something extremely simple that appears to be a bug in the core code. > Would you agree? If so (and there are no other suggestions > forthcoming!) then i propose I raise a ticket. > > On Dec 30 2009, 10:15 am, John Andersen > wrote: > > > > > With the hasOne relationship, the query looks like: > > [code] > > SELECT `Author`.`id`, `Author`.`name`, COUNT(`Article`.`id`) as > > article_count FROM `authors` AS `Author` LEFT JOIN `articles` AS > > `Article` ON (`Article`.`author_id` = `Author`.`id`) WHERE > > `Author`.`id` IN (1, 2, 13) GROUP BY `Author`.`id`, `Author`.`name` > > [/code] > > > With the hasMany relationship, the query looks like: > > [code] > > SELECT `Author`.`id`, `Author`.`name`, COUNT(`Article`.`id`) as > > article_count FROM `authors` AS `Author` WHERE `Author`.`id` IN (1, 2, > > 13) GROUP BY `Author`.`id`, `Author`.`name` > > [/code] > > Contain does not assist in creating a LEFT JOIN, which is why > > Article.id is an unknown column! > > > As far as I remember, the issue is probably not in the Containable > > behaviour, but somewhere in DboSource, but I may be wrong here :) > > > There is another way: > > 1) Set contain equal false. > > 2) Define the joins yourself, as in the example below: > > [code] > > $results = $this->Article->Author->find( > > 'all', > > array( > > 'contain' => false, > > 'joins' => array( > > array( > > 'table' => 'articles', > > 'type' => 'left', > > 'alias' => 'Article', > > 'foreignKey' => false, > > 'conditions' => array('Article.author_id = > > Author.id') > > ) > > ), > > 'fields' => array('Author.id', 'Author.name', 'COUNT > > (Article.id) as article_count'), > > 'group' => array('Author.id', 'Author.name'), > > ) > > ); > > [/code] > > > But that is also not very simple! > > > Will keep you updated, should I find a better solution! Enjoy, > > John > > > On Dec 30, 12:07 pm, Jeremy Burns wrote: > > > > Thank you John - I will try this out. Strikes me as very odd that this > > > is not much much simpler! > > > > On Dec 30, 10:05 am, John Andersen wrote: > > > > > Hi Jeremy, > > > > > Seems like Contain can't find out to make a join when there is a > > > > hasMany relationship between the Author and the Article models. I made > > > > changes to Example A, so that Contain makes a LEFT JOIN, which > > > > includes Authors with no Articles :) > > > > > 1) Unbind the hasMany relationship. > > > > 2) Bind a hasOne relationship. > > > > 3) Make the find on the Author model - I have include two author ids > > > > with articles and one (13) without articles. > > > > [code] > > > > $this->Article->Author->unbindModel( array('hasMany' => array > > > > ('Article'))); > > > > $this->Article->Author->bindModel( array('hasOne' => array > > > > ('Article'))); > > > > $results = $this->Article->Author->find( > > > > 'all', > > > > array( > > > > 'contain' => array('Article'), > > > > 'fields' => array('Author.id', 'Author.name', 'CO
Re: Count column in index view
Hi John Thanks for this, but I still cannot get this to return the results I need. I note that in your example the 'parent' is the author and the child is the article. In my example the parent is the location_type and the child is the location. Your code would exist (and work) within the child (i.e. article or location) controller, whereas I need it to run in the parent (i.e author or location_type) controller. When I try to switch the code around I run into all sorts of errors. For example, if I use your unbinding and then binding method... $this->LocationType->unbindModel( array('hasMany' => array ('Location'))); $this->LocationType->bindModel( array('hasOne' => array('Location'))); $results = $this->LocationType->Location->find( 'all', array( 'contain' => array('Location'), 'fields' => array('LocationType.id', 'LocationType.name', 'COUNT (Location.id) as location_count'), 'conditions' => array('Location.id' => array(1,2,3,4)), 'group' => array('LocationType.id', 'LocationType.name'), ) ); ...it triggers the error: Model "Location" is not associated with model "Location" ...which (by process of elimination) is triggered by the line: 'contain' => array('Location') Bizzarre. I also fiddled around with the self created join method with no success. It strikes me that you and I have put a lot of effort into delivering something extremely simple that appears to be a bug in the core code. Would you agree? If so (and there are no other suggestions forthcoming!) then i propose I raise a ticket. On Dec 30 2009, 10:15 am, John Andersen wrote: > With the hasOne relationship, the query looks like: > [code] > SELECT `Author`.`id`, `Author`.`name`, COUNT(`Article`.`id`) as > article_count FROM `authors` AS `Author` LEFT JOIN `articles` AS > `Article` ON (`Article`.`author_id` = `Author`.`id`) WHERE > `Author`.`id` IN (1, 2, 13) GROUP BY `Author`.`id`, `Author`.`name` > [/code] > > With the hasMany relationship, the query looks like: > [code] > SELECT `Author`.`id`, `Author`.`name`, COUNT(`Article`.`id`) as > article_count FROM `authors` AS `Author` WHERE `Author`.`id` IN (1, 2, > 13) GROUP BY `Author`.`id`, `Author`.`name` > [/code] > Contain does not assist in creating a LEFT JOIN, which is why > Article.id is an unknown column! > > As far as I remember, the issue is probably not in the Containable > behaviour, but somewhere in DboSource, but I may be wrong here :) > > There is another way: > 1) Set contain equal false. > 2) Define the joins yourself, as in the example below: > [code] > $results = $this->Article->Author->find( > 'all', > array( > 'contain' => false, > 'joins' => array( > array( > 'table' => 'articles', > 'type' => 'left', > 'alias' => 'Article', > 'foreignKey' => false, > 'conditions' => array('Article.author_id = > Author.id') > ) > ), > 'fields' => array('Author.id', 'Author.name', 'COUNT > (Article.id) as article_count'), > 'group' => array('Author.id', 'Author.name'), > ) > ); > [/code] > > But that is also not very simple! > > Will keep you updated, should I find a better solution! Enjoy, > John > > On Dec 30, 12:07 pm, Jeremy Burns wrote: > > > > > Thank you John - I will try this out. Strikes me as very odd that this > > is not much much simpler! > > > On Dec 30, 10:05 am, John Andersen wrote: > > > > Hi Jeremy, > > > > Seems like Contain can't find out to make a join when there is a > > > hasMany relationship between the Author and the Article models. I made > > > changes to Example A, so that Contain makes a LEFT JOIN, which > > > includes Authors with no Articles :) > > > > 1) Unbind the hasMany relationship. > > > 2) Bind a hasOne relationship. > > > 3) Make the find on the Author model - I have include two author ids > > > with articles and one (13) without articles. > > > [code] > > > $this->Article->Author->unbindModel( array('hasMany' => array > > > ('Article'))); > > > $this->Article->Author->bindModel( array('hasOne' => array > > > ('Article'))); > > > $results = $this->Article->Author->find( > > > 'all', > > > array( > > > 'contain' => array('Article'), > > > 'fields' => array('Author.id', 'Author.name', 'COUNT > > > (Article.id) as article_count'), > > > 'conditions' => array('Author.id' => array(1,2,13)), > > > 'group' => array('Author.id', 'Author.name'), > > > ) > > > ); > > > debug($results); > > > [/code] > > > > The result is: > > > [result] > > > 28871\controllers\articles_controller.php (line 48) > > > Array > > > ( > > > [0] => Array > > > ( > > > [Author] => Array > > > ( > > > [id] => 1 > > >
Re: Count column in index view
With the hasOne relationship, the query looks like: [code] SELECT `Author`.`id`, `Author`.`name`, COUNT(`Article`.`id`) as article_count FROM `authors` AS `Author` LEFT JOIN `articles` AS `Article` ON (`Article`.`author_id` = `Author`.`id`) WHERE `Author`.`id` IN (1, 2, 13) GROUP BY `Author`.`id`, `Author`.`name` [/code] With the hasMany relationship, the query looks like: [code] SELECT `Author`.`id`, `Author`.`name`, COUNT(`Article`.`id`) as article_count FROM `authors` AS `Author` WHERE `Author`.`id` IN (1, 2, 13) GROUP BY `Author`.`id`, `Author`.`name` [/code] Contain does not assist in creating a LEFT JOIN, which is why Article.id is an unknown column! As far as I remember, the issue is probably not in the Containable behaviour, but somewhere in DboSource, but I may be wrong here :) There is another way: 1) Set contain equal false. 2) Define the joins yourself, as in the example below: [code] $results = $this->Article->Author->find( 'all', array( 'contain' => false, 'joins' => array( array( 'table' => 'articles', 'type' => 'left', 'alias' => 'Article', 'foreignKey' => false, 'conditions' => array('Article.author_id = Author.id') ) ), 'fields' => array('Author.id', 'Author.name', 'COUNT (Article.id) as article_count'), 'group' => array('Author.id', 'Author.name'), ) ); [/code] But that is also not very simple! Will keep you updated, should I find a better solution! Enjoy, John On Dec 30, 12:07 pm, Jeremy Burns wrote: > Thank you John - I will try this out. Strikes me as very odd that this > is not much much simpler! > > On Dec 30, 10:05 am, John Andersen wrote: > > > Hi Jeremy, > > > Seems like Contain can't find out to make a join when there is a > > hasMany relationship between the Author and the Article models. I made > > changes to Example A, so that Contain makes a LEFT JOIN, which > > includes Authors with no Articles :) > > > 1) Unbind the hasMany relationship. > > 2) Bind a hasOne relationship. > > 3) Make the find on the Author model - I have include two author ids > > with articles and one (13) without articles. > > [code] > > $this->Article->Author->unbindModel( array('hasMany' => array > > ('Article'))); > > $this->Article->Author->bindModel( array('hasOne' => array > > ('Article'))); > > $results = $this->Article->Author->find( > > 'all', > > array( > > 'contain' => array('Article'), > > 'fields' => array('Author.id', 'Author.name', 'COUNT > > (Article.id) as article_count'), > > 'conditions' => array('Author.id' => array(1,2,13)), > > 'group' => array('Author.id', 'Author.name'), > > ) > > ); > > debug($results); > > [/code] > > > The result is: > > [result] > > 28871\controllers\articles_controller.php (line 48) > > Array > > ( > > [0] => Array > > ( > > [Author] => Array > > ( > > [id] => 1 > > [name] => Hans Christian Andersen > > ) > > [0] => Array > > ( > > [article_count] => 19 > > ) > > ) > > [1] => Array > > ( > > [Author] => Array > > ( > > [id] => 2 > > [name] => Terry Prattchet > > ) > > [0] => Array > > ( > > [article_count] => 19 > > ) > > ) > > [2] => Array > > ( > > [Author] => Array > > ( > > [id] => 13 > > [name] => Test > > ) > > [0] => Array > > ( > > [article_count] => 0 > > ) > > ) > > ) > > [/result] > > > Hope this helps you on the way, > > John > > > On Dec 30, 6:52 am, Jeremy Burns wrote: > > > > Thanks John. Option B does indeed work - sort of! It returns a list of > > > location types with a count of their locations, but only where there > > > is a location. In other words, it does not give me the location types > > > that do not have have any locations. > > > > Any more ideas - anyone? > > > > On Dec 29, 12:35 pm, John Andersen wrote: > > > > > A correction, example A gives the author with all the articles, but > > > > the article count is 1 (one) - so no luck using that! > > > > Enjoy, > > > > John > > > > [snip] 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-p
Re: Count column in index view
Thank you John - I will try this out. Strikes me as very odd that this is not much much simpler! On Dec 30, 10:05 am, John Andersen wrote: > Hi Jeremy, > > Seems like Contain can't find out to make a join when there is a > hasMany relationship between the Author and the Article models. I made > changes to Example A, so that Contain makes a LEFT JOIN, which > includes Authors with no Articles :) > > 1) Unbind the hasMany relationship. > 2) Bind a hasOne relationship. > 3) Make the find on the Author model - I have include two author ids > with articles and one (13) without articles. > [code] > $this->Article->Author->unbindModel( array('hasMany' => array > ('Article'))); > $this->Article->Author->bindModel( array('hasOne' => array > ('Article'))); > $results = $this->Article->Author->find( > 'all', > array( > 'contain' => array('Article'), > 'fields' => array('Author.id', 'Author.name', 'COUNT > (Article.id) as article_count'), > 'conditions' => array('Author.id' => array(1,2,13)), > 'group' => array('Author.id', 'Author.name'), > ) > ); > debug($results); > [/code] > > The result is: > [result] > 28871\controllers\articles_controller.php (line 48) > Array > ( > [0] => Array > ( > [Author] => Array > ( > [id] => 1 > [name] => Hans Christian Andersen > ) > [0] => Array > ( > [article_count] => 19 > ) > ) > [1] => Array > ( > [Author] => Array > ( > [id] => 2 > [name] => Terry Prattchet > ) > [0] => Array > ( > [article_count] => 19 > ) > ) > [2] => Array > ( > [Author] => Array > ( > [id] => 13 > [name] => Test > ) > [0] => Array > ( > [article_count] => 0 > ) > ) > ) > [/result] > > Hope this helps you on the way, > John > > On Dec 30, 6:52 am, Jeremy Burns wrote: > > > > > Thanks John. Option B does indeed work - sort of! It returns a list of > > location types with a count of their locations, but only where there > > is a location. In other words, it does not give me the location types > > that do not have have any locations. > > > Any more ideas - anyone? > > > On Dec 29, 12:35 pm, John Andersen wrote: > > > > A correction, example A gives the author with all the articles, but > > > the article count is 1 (one) - so no luck using that! > > > Enjoy, > > > John > > > [snip] 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
Re: Count column in index view
Hi Jeremy, Seems like Contain can't find out to make a join when there is a hasMany relationship between the Author and the Article models. I made changes to Example A, so that Contain makes a LEFT JOIN, which includes Authors with no Articles :) 1) Unbind the hasMany relationship. 2) Bind a hasOne relationship. 3) Make the find on the Author model - I have include two author ids with articles and one (13) without articles. [code] $this->Article->Author->unbindModel( array('hasMany' => array ('Article'))); $this->Article->Author->bindModel( array('hasOne' => array ('Article'))); $results = $this->Article->Author->find( 'all', array( 'contain' => array('Article'), 'fields' => array('Author.id', 'Author.name', 'COUNT (Article.id) as article_count'), 'conditions' => array('Author.id' => array(1,2,13)), 'group' => array('Author.id', 'Author.name'), ) ); debug($results); [/code] The result is: [result] 28871\controllers\articles_controller.php (line 48) Array ( [0] => Array ( [Author] => Array ( [id] => 1 [name] => Hans Christian Andersen ) [0] => Array ( [article_count] => 19 ) ) [1] => Array ( [Author] => Array ( [id] => 2 [name] => Terry Prattchet ) [0] => Array ( [article_count] => 19 ) ) [2] => Array ( [Author] => Array ( [id] => 13 [name] => Test ) [0] => Array ( [article_count] => 0 ) ) ) [/result] Hope this helps you on the way, John On Dec 30, 6:52 am, Jeremy Burns wrote: > Thanks John. Option B does indeed work - sort of! It returns a list of > location types with a count of their locations, but only where there > is a location. In other words, it does not give me the location types > that do not have have any locations. > > Any more ideas - anyone? > > On Dec 29, 12:35 pm, John Andersen wrote: > > > A correction, example A gives the author with all the articles, but > > the article count is 1 (one) - so no luck using that! > > Enjoy, > > John > > [snip] 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
Re: Count column in index view
Thanks John. Option B does indeed work - sort of! It returns a list of location types with a count of their locations, but only where there is a location. In other words, it does not give me the location types that do not have have any locations. Any more ideas - anyone? On Dec 29, 12:35 pm, John Andersen wrote: > A correction, example A gives the author with all the articles, but > the article count is 1 (one) - so no luck using that! > Enjoy, > John > [snip] 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
Re: Count column in index view
A correction, example A gives the author with all the articles, but the article count is 1 (one) - so no luck using that! Enjoy, John [snip] 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
Re: Count column in index view
Hi Jeremy, I tried to play with your problem, using my own models and came up with this: Example A - gives the same issue as you have - unknown column! $results = $this->Author->find( 'all', array( 'contain' => array('Article'), 'conditions' => array('Author.id' => 1), 'fields' => array('Author.id', 'Author.name', 'COUNT(*) as article_count'), 'group' => array('Author.id', 'Author.name'), 'order' => 'Author.id' ) ); debug($results); Example B - trying from the other side of the relationship - gives the correct result, maybe you can change your find? $results = $this->Author->Article->find( 'all', array( 'contain' => array('Author'), 'conditions' => array('Article.author_id' => 1), 'fields' => array('Author.id', 'Author.name', 'COUNT(*) as article_count'), 'group' => array('Author.id', 'Author.name'), 'order' => 'Author.id' ) ); debug($results); Result B: 28871\controllers\authors_controller.php (line 35) Array ( [0] => Array ( [Author] => Array ( [id] => 1 [name] => Hans Christian Andersen ) [0] => Array ( [article_count] => 19 ) ) ) Hope this helps you on the way, enjoy, John On Dec 29, 2:14 pm, Jeremy Burns wrote: > Hi John - thanks for the reply. No luck, I'm afraid - I get the same > result. > > I have simplified my example so I can focus in on what's not right: > > $results = $this->LocationType->find( > 'all', > array( > 'recursive' => 2, > 'contain' => array( > 'Location' => array( > 'fields' => array( > 'id' > ) > ) > ), > 'fields' => array( > 'LocationType.id', > 'LocationType.name', > 'COUNT(Location.id) as location_count' > ), > 'group' => array( > 'LocationType.id', > 'LocationType.name' > ), > 'order' => 'LocationType.lft' > ) > ); > > If I omit the COUNT field, I get an array that includes Locations > where necessary. When I include the COUNT field I get an error: > Unknown column 'Location.id' in 'field list'. The SQL that is being > run is: > > SELECT `LocationType`.`id`, `LocationType`.`name`, COUNT > (`Location`.`id`) as location_count FROM `location_types` AS > `LocationType` WHERE 1 = 1 GROUP BY `LocationType`.`id`, > `LocationType`.`name` ORDER BY `LocationType`.`lft` ASC > > As you can see, the locations table is not in the SQL even though they > are connected in the database, in the model and on the contain > statement. > > I have torn almost all of the hair I had left trying to crack what > should be a no brainer. Any more help please? > > On Dec 29, 9:43 am, John Andersen wrote: > > > Ok, I will give it a try :) > > > 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 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' > >
Re: Count column in index view
Hi John - thanks for the reply. No luck, I'm afraid - I get the same result. I have simplified my example so I can focus in on what's not right: $results = $this->LocationType->find( 'all', array( 'recursive' => 2, 'contain' => array( 'Location' => array( 'fields' => array( 'id' ) ) ), 'fields' => array( 'LocationType.id', 'LocationType.name', 'COUNT(Location.id) as location_count' ), 'group' => array( 'LocationType.id', 'LocationType.name' ), 'order' => 'LocationType.lft' ) ); If I omit the COUNT field, I get an array that includes Locations where necessary. When I include the COUNT field I get an error: Unknown column 'Location.id' in 'field list'. The SQL that is being run is: SELECT `LocationType`.`id`, `LocationType`.`name`, COUNT (`Location`.`id`) as location_count FROM `location_types` AS `LocationType` WHERE 1 = 1 GROUP BY `LocationType`.`id`, `LocationType`.`name` ORDER BY `LocationType`.`lft` ASC As you can see, the locations table is not in the SQL even though they are connected in the database, in the model and on the contain statement. I have torn almost all of the hair I had left trying to crack what should be a no brainer. Any more help please? On Dec 29, 9:43 am, John Andersen wrote: > Ok, I will give it a try :) > > 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 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 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 =
Re: Count column in index view
Ok, I will give it a try :) 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 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 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 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
Re: Count column in index view
Any takers for this please? On Dec 23, 6:01 am, Jeremy Burns 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 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 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
Re: Count column in index view
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 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 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
Re: Count column in index view
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 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
Re: Count column in index view
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
Re: Count column in index view
Ooh. Slightly apprehensive about pinging this group for any response, but here goes. I have started using counterCache, which solves the 'count' issue for me. But I am still unclear on the best policy for adding aggregate columns to paginated index views. I'd really appreciate some advice please. Happy Christmas. On Dec 18, 10:33 am, Jeremy Burns wrote: > I have a model that uses the Tree behaviour, so it has a self join in > the database (parent_id to id). In my index view I'd like to add a > column that shows the count of direct children for 'this' row. I know > I can get that number on a row by row basis using childCount, but how > do I use that as part of my contain and pagination? 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