Re: habtm limit
The query actually looks ok to me ... in what way are the results incorrect
?
On Tue, Sep 29, 2009 at 8:55 PM, bram wrote:
>
> These are the resulting queries:
>
> SELECT `User`.`id`, `User`.`username`, `User`.`name` FROM `users` AS
> `User` ORDER BY `name` ASC
>
> SELECT `Event`.`id`, `Event`.`date`, `EventsUser`.`id`,
> `EventsUser`.`event_id`, `EventsUser`.`user_id` FROM `events` AS
> `Event` JOIN `events_users` AS `EventsUser` ON (`EventsUser`.`user_id`
> IN (3, 1, 2) AND `EventsUser`.`event_id` = `Event`.`id`) WHERE 1 = 1
> ORDER BY `Event`.`date` DESC LIMIT 1
>
> I actually don't know how to rewrite this query to get the latest
> associated event (one) for each user...
>
>
> On Sep 29, 3:30 pm, Bert Van den Brande wrote:
> > Have a look at the query it generates (set debug to 2) , I'm sure this
> will
> > shine a bright light on the problem ...
> >
> > On Mon, Sep 28, 2009 at 9:48 PM, bram wrote:
> >
> > > In my app, I have a User model that has a habtm association with
> > > 'Event'. In one of the views, I want to show a list of Users and their
> > > most recent event.
> >
> > > I ended up with this definition in the User model:
> >
> > >var $hasAndBelongsToMany = array(
> > >'Event' => array(
> > >'className' => 'Event',
> > >'joinTable' => 'events_users',
> > >'foreignKey' => 'user_id',
> > >'associationForeignKey' => 'event_id',
> > >'unique' => true,
> > >'limit' => 1,
> > >'order' => 'Event.date DESC'
> > >)
> > >);
> >
> > > find('all') returns the list of all users and only one of them has an
> > > associated event.
> >
> > > When increasing the limit, it does show associated events, but when
> > > some user don't have events associated, some other users will have
> > > multiple events returned.
> >
> > > Is this a cake bug or do I have the wrong impression of the limit in a
> > > habtm association?
> >
> > > Cheers,
> >
> > > Bram
> >
>
--~--~-~--~~~---~--~~
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: habtm limit
These are the resulting queries:
SELECT `User`.`id`, `User`.`username`, `User`.`name` FROM `users` AS
`User` ORDER BY `name` ASC
SELECT `Event`.`id`, `Event`.`date`, `EventsUser`.`id`,
`EventsUser`.`event_id`, `EventsUser`.`user_id` FROM `events` AS
`Event` JOIN `events_users` AS `EventsUser` ON (`EventsUser`.`user_id`
IN (3, 1, 2) AND `EventsUser`.`event_id` = `Event`.`id`) WHERE 1 = 1
ORDER BY `Event`.`date` DESC LIMIT 1
I actually don't know how to rewrite this query to get the latest
associated event (one) for each user...
On Sep 29, 3:30 pm, Bert Van den Brande wrote:
> Have a look at the query it generates (set debug to 2) , I'm sure this will
> shine a bright light on the problem ...
>
> On Mon, Sep 28, 2009 at 9:48 PM, bram wrote:
>
> > In my app, I have a User model that has a habtm association with
> > 'Event'. In one of the views, I want to show a list of Users and their
> > most recent event.
>
> > I ended up with this definition in the User model:
>
> > var $hasAndBelongsToMany = array(
> > 'Event' => array(
> > 'className' => 'Event',
> > 'joinTable' => 'events_users',
> > 'foreignKey' => 'user_id',
> > 'associationForeignKey' => 'event_id',
> > 'unique' => true,
> > 'limit' => 1,
> > 'order' => 'Event.date DESC'
> > )
> > );
>
> > find('all') returns the list of all users and only one of them has an
> > associated event.
>
> > When increasing the limit, it does show associated events, but when
> > some user don't have events associated, some other users will have
> > multiple events returned.
>
> > Is this a cake bug or do I have the wrong impression of the limit in a
> > habtm association?
>
> > Cheers,
>
> > Bram
--~--~-~--~~~---~--~~
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: habtm limit
Have a look at the query it generates (set debug to 2) , I'm sure this will
shine a bright light on the problem ...
On Mon, Sep 28, 2009 at 9:48 PM, bram wrote:
>
> In my app, I have a User model that has a habtm association with
> 'Event'. In one of the views, I want to show a list of Users and their
> most recent event.
>
> I ended up with this definition in the User model:
>
>var $hasAndBelongsToMany = array(
>'Event' => array(
>'className' => 'Event',
>'joinTable' => 'events_users',
>'foreignKey' => 'user_id',
>'associationForeignKey' => 'event_id',
>'unique' => true,
>'limit' => 1,
>'order' => 'Event.date DESC'
>)
>);
>
> find('all') returns the list of all users and only one of them has an
> associated event.
>
> When increasing the limit, it does show associated events, but when
> some user don't have events associated, some other users will have
> multiple events returned.
>
> Is this a cake bug or do I have the wrong impression of the limit in a
> habtm association?
>
> Cheers,
>
> Bram
> >
>
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
habtm limit
In my app, I have a User model that has a habtm association with
'Event'. In one of the views, I want to show a list of Users and their
most recent event.
I ended up with this definition in the User model:
var $hasAndBelongsToMany = array(
'Event' => array(
'className' => 'Event',
'joinTable' => 'events_users',
'foreignKey' => 'user_id',
'associationForeignKey' => 'event_id',
'unique' => true,
'limit' => 1,
'order' => 'Event.date DESC'
)
);
find('all') returns the list of all users and only one of them has an
associated event.
When increasing the limit, it does show associated events, but when
some user don't have events associated, some other users will have
multiple events returned.
Is this a cake bug or do I have the wrong impression of the limit in a
habtm association?
Cheers,
Bram
--~--~-~--~~~---~--~~
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: Issue with HABTM, LIMIT and the created SQL statements
Thanks a lot Brian, The second link certainly helped me on the way to a solution! Also thanks for pointing out my naming error :) That will be fixed! Enjoy, John On Apr 18, 7:50 pm, brian wrote: > I'm not sure I follow all that but I think these may be some use: > > http://teknoid.wordpress.com/2008/07/17/forcing-an-sql-join-in-cakephp/ > http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-... > > Also, your join table should be named "articles_collections". > [snip] --~--~-~--~~~---~--~~ 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: Issue with HABTM, LIMIT and the created SQL statements
I'm not sure I follow all that but I think these may be some use: http://teknoid.wordpress.com/2008/07/17/forcing-an-sql-join-in-cakephp/ http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find Also, your join table should be named "articles_collections". On Sat, Apr 18, 2009 at 3:56 AM, John Andersen wrote: > > Dear all, > > Having three tables in a standard HABTM configuration: > > Model "Article" HABTM model "Collection" > Model "Collection" HABTM model "Article" > > Giving the tables "articles" -> "article_collections" -> > "collections". > > My goal is to retrieve 4 of the articles which are related to the > collection named "featured". > > I have tried retrieving the articles from the "Article" model and from > the "Collection" model point, but without success: > > Issues: > 1) From the "Article" model point, I specify conditions that the > collection is named "featured" and limit articles to 4, but CakePHP > creates SQL that places the "featured" condition together with the > "Article" model, not the "Collection" model. > 2) From the "Collection" model point, I specify the same conditions, > but CakePHP puts the limit on the "Collection", not on the "Article", > thus having no influence on the number of articles. > > Having now looked through several descriptions of HABTM here and other > sites, I have returned to you. > > Are there a specific way to specify the conditions, so that either the > 1) or the 2) issue can be solved? > > Best wishes, > John > > > --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Issue with HABTM, LIMIT and the created SQL statements
Dear all, Having three tables in a standard HABTM configuration: Model "Article" HABTM model "Collection" Model "Collection" HABTM model "Article" Giving the tables "articles" -> "article_collections" -> "collections". My goal is to retrieve 4 of the articles which are related to the collection named "featured". I have tried retrieving the articles from the "Article" model and from the "Collection" model point, but without success: Issues: 1) From the "Article" model point, I specify conditions that the collection is named "featured" and limit articles to 4, but CakePHP creates SQL that places the "featured" condition together with the "Article" model, not the "Collection" model. 2) From the "Collection" model point, I specify the same conditions, but CakePHP puts the limit on the "Collection", not on the "Article", thus having no influence on the number of articles. Having now looked through several descriptions of HABTM here and other sites, I have returned to you. Are there a specific way to specify the conditions, so that either the 1) or the 2) issue can be solved? Best wishes, John --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
