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 <jeremybu...@me.com> 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 <j.andersen...@gmail.com> 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 <jeremybu...@me.com> 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 <j.andersen...@gmail.com> 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+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en