A little more info. When I paginate as above, Cake makes the following queries:
SELECT `User`.`label`, `User`.`slug`, `User`.`id` FROM `users` AS
`User` WHERE 1 = 1 ORDER BY `User`.`sort_label` ASC LIMIT 20
SELECT `Publication`.`title`, `Publication`.`slug`,
`Publication`.`thumbnail`, `Publication`.`media_type_id`,
`Publication`.`user_id` FROM `publications` AS `Publication` WHERE
`Publication`.`user_id` IN (745, 1058, 94, 1490, 386, 1106, 592, 1209,
1415, 814, 855, 889, 610, 1598, 971, 178, 740, 678, 1105, 621)
Obviously, this isn't going to get me what I want, as all Users will
be included. However, when I paginate on Publication, I get:
SELECT COUNT(*) AS `count` FROM `showcases` AS `Publication` INNER
JOIN `users` AS `User` ON (`Publication`.`user_id` = `User`.`id`) LEFT
JOIN `media_types` AS `MediaType` ON (`Publication`.`media_type_id` =
`MediaType`.`id`) WHERE 1 = 1
SELECT `Publication`.`user_id`, `Publication`.`id`,
`Publication`.`title`, `Publication`.`slug`,
`Publication`.`thumbnail`, `User`.`user_type_id`, `User`.`label`,
`User`.`slug`, `MediaType`.`name`, `MediaType`.`slug`, `User`.`id`,
`MediaType`.`id` FROM `publications` AS `Publication` INNER JOIN
`users` AS `User` ON (`Publication`.`user_id` = `User`.`id`) LEFT JOIN
`media_types` AS `MediaType` ON (`Publication`.`media_type_id` =
`MediaType`.`id`) WHERE 1 = 1 LIMIT 20
The INNER JOIN is used, but the results array will, of course, repeat
User for each Publication.So, for an individual User, I might see
something like:
[0]
publication
User
MediaType
[1]
publication
User
MediaType
[2]
publication
User
MediaType
But what I'm after is:
[0] User
...
Publication
[0] ...
MediaType
[1] ...
MediaType
[2] ...
MediaType
On Sat, Feb 14, 2009 at 5:07 PM, brian <[email protected]> wrote:
> I'm trying to paginate a on a model, Publication, which belongsTo
> User. User hasMany Publication, though not all Users have any
> Publications. In PublicationsController, I'd like to paginate a list
> of Users and each of their Publications.
>
> User:
> public $hasMany = array('Publication');
>
> Publication:
> public $belongsTo = array('User' => array('type' => 'INNER'), 'MediaType');
>
>
> publications_controller:
>
> public $paginate = array(
> 'Publication' -> array(
> // ...
> ),
> 'User' => array(
> 'limit' => 20,
> 'order' => array('User.sort_label' => 'ASC'),
> 'fields' => array('User.label', 'User.slug'),
> 'contain' => array(
> 'Publication' => array(
> 'fields' => array(
> 'Publication.title',
> 'Publication.slug',
> 'Publication.thumbnail'
> ),
> 'MediaType' => array(
> 'fields' => array('name', 'slug')
> )
> )
> )
> )
> );
>
> I have a separate 'User' array there because I want to be able to
> group the Publications by User, rather than get a list of Publications
> with User repeated for each one.
>
> Now, when I paginate against 'User' I get an array like this below.
>
> Array
> (
> [0] => Array
> (
> [User] => Array
> (
> [label] => '...'
> [slug] => '...'
> [id] => 745
> )
>
> [Publication] => Array
> (
> )
>
> )
>
> [1] => Array
> (
> [User] => Array
> ...
>
> This is the right format, but the problem is that it includes an empty
> Publication array for those Users without any in the DB. Cake seems to
> be ignoring the INNER type. I suspect that it's because I'm trying to
> paginate.
>
> I've been searching around and have tried unbinding and rebinding.
> However, each thing I've tried results in no change or errors in the
> SQL. Is there no way to do this from PublicationsController?
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
