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