Just add the field and groupings to the find (http://book.cakephp.org/
view/73/Retrieving-Your-Data)
find('all', array('fields' => array('Group.*', '(COUNT(*)) as
group_count', 'User.username')
'group' => array('Group.id', 'Group.name',
'Group.created', 'Group.modified', 'User.username')
Or even easier (although less cake-like, probably more efficient at
the DB level):
'fields' => array('Group.*', '(select count(1) from users User where
User.group_id = Group.id) as users_count')
On Jan 12, 11:01 pm, "Arthur Pemberton" <pem...@gmail.com> wrote:
> I was trying to do what would be a fairly simple thing in plain SQL.
> The bake script generated a nice groups_controller for me which
> paginated the groups listing. Fairly simple.
>
> All I wanted to do was add a user count to the index. The required SQL would
> be:
>
> SELECT `Group`.`id` , `Group`.`name` , `Group`.`created` ,
> `Group`.`modified` , `User`.`username` , COUNT( * )
> FROM `groups` AS `Group`
> INNER JOIN `users` AS `User` ON `User`.`group_id` = `Group`.`id`
> GROUP BY `Group`.`id` , `Group`.`name` , `Group`.`created` ,
> `Group`.`modified` , `User`.`username`
> LIMIT 0 , 30
>
> this compared to the original SQL before i touched the index action
> generated by bake:
> SELECT `Group`.`id`, `Group`.`name`, `Group`.`created`,
> `Group`.`modified` FROM `groups` AS `Group` WHERE 1 = 1 LIMIT 20
>
> While there is a lot more SQL, the result set is equivalent, and can
> be ordered and limited just as before.
>
> And hour later, I have not found a good way to get the user count.,
> short of doing the paginate, and then manually inserting the user
> counts after.
>
> Is there any way to just force my own SQL into paginate?
>
> I sawhttp://book.cakephp.org/view/249/Custom-Query-Paginationbut it
> doesn't what to override and where.
>
> Is there a way i can have cake use an SQL view i make just for this query?
>
> --
> Fedora 9 : sulphur is good for the skin
> (www.pembo13.com)
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
