Re: Calling select queries within find()

> I posted a similar query here last week. I was hoping to use Set class
> to do the same. I guess grigri wasn't around ;-)

Sorry mate, must have missed that one. I haven't been checking this
group as often as I used to.

afaik, there's no way to do this with the Set class alone :( There are
some things it just can't do. Although I suppose I could write up a
diff patch for adding functionality for a Set::replace(),
Set::multiInsert() or some such method.

My preferred method of dealing with this situation is to modify the
datasource. I always override the datasource anyway, so it's no biggie
to override resultSet() it I need to. The advantage here is that there
isn't an extra loop over the resultset; hence a performance increase
over using an afterFind() method for larger result sets.

Specifically, I override it so that a field specified as Bacon__eggs
(that's a double-underscore) will slot into the 'Bacon' entry.

With this, the above query becomes:

'fields' => array('TeamsUser.*', "(SELECT COUNT(id) FROM teams_users
AS TeamsUser WHERE TeamsUser.team_id = Team.id AND TeamsUser.status =
'approved') AS TeamsUser__totalMembers")

And the resultset is as expected.

hth
grigri

On Feb 4, 2:39 am, brian <bally.z...@gmail.com> wrote:
> I posted a similar query here last week. I was hoping to use Set class
> to do the same. I guess grigri wasn't around ;-)
>
> Teknoid suggested this method:
>
> http://teknoid.wordpress.com/2008/09/29/dealing-with-calculated-field...
>
> On Tue, Feb 3, 2009 at 8:41 PM, Miles J <mileswjohn...@gmail.com> wrote:
>
> > Ok I got it working, but its outside of the array I want. Anyway to
> > get it in there?
>
> > [TeamsUser] => Array
> >                                (
> >                                    [id] => 1
> >                                    [team_id] => 1
> >                                    [user_id] => 2
> >                                    [role] => leader
> >                                    [status] => approved
> >                                    [joinDate] => 1231636154
> >                                )
>
> >                            [0] => Array
> >                                (
> >                                    [totalMembers] => 6
> >                                )
>
> > Heres my query:
>
> > 'fields' => array('TeamsUser.*', "(SELECT COUNT(id) FROM teams_users
> > AS TeamsUser WHERE TeamsUser.team_id = Team.id AND TeamsUser.status =
> > 'approved') AS totalMembers")
>
> > I tried doing AS TeamsUser.totalMembers and it didnt work.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---