Hi,
I've got a query i've created in cakephp using a lot of join arrays. I've
also unbound associated arrays for the purpose of this query so that i can
get the results exactly as I need them.
The resulting query is:
SELECT `forums`.`id`, `Thread`.`id`, `firstpost`.`title`,
`lastpost`.`title`, (SELECT COUNT(1) FROM posts WHERE posts.thread_id =
Thread.id) AS postcount FROM `threads` AS `Thread` INNER JOIN posts AS
`firstpost` ON (`firstpost`.`thread_id` = `Thread`.`id` AND `firstpost`.`id`
= (SELECT MIN(id) FROM posts WHERE `posts`.`thread_id` = `Thread`.`id`))
INNER JOIN posts AS `lastpost` ON (`lastpost`.`thread_id` = `Thread`.`id`
AND `lastpost`.`id` = (SELECT MAX(id) FROM posts WHERE `posts`.`thread_id` =
`Thread`.`id`)) INNER JOIN forums ON (`forums`.`id` = `Thread`.`forum_id`)
LEFT JOIN `forums` AS `Forum` ON (`Thread`.`forum_id` = `Forum`.`id`) WHERE
`forums`.`id` = 1 LIMIT 20
(note, the limit is applied by the paginator automatically).
My problem is when it comes to sorting.
Sorting on Thread.id works perfectly fine ( echo $paginator->sort('Thread',
'Thread.id'); )
but sorting wont work when I try to sort by "lastpost" or "postcount" (
echo $paginator->sort('Posts','postcount'); )
Is this not possible with out of the box pagination? or am I missing
something?
For reference, here's the value of the query inside the $this->paginate
variable:
return array(
'fields' => array(
'forums.id',
'Thread.id',
'firstpost.title',
'lastpost.title',
'(SELECT COUNT(1) FROM posts WHERE posts.thread_id =
Thread.id) AS postcount'
),
'conditions' => array('forums.id' => $id),
'order' => array('Thread.created' => 'asc'),
'joins' => array(
array(
'table' => 'posts',
'alias' => 'firstpost',
'type' => 'INNER',
'conditions' => array(
'firstpost.thread_id = Thread.id',
'firstpost.id = (SELECT MIN(id) FROM posts WHERE
posts.thread_id = Thread.id)'
)
),
array(
'table' => 'posts',
'alias' => 'lastpost',
'type' => 'INNER',
'conditions' => array(
'lastpost.thread_id = Thread.id',
'lastpost.id = (SELECT MAX(id) FROM posts WHERE
posts.thread_id = Thread.id)'
)
),
array(
'table' => 'forums',
'type' => 'INNER',
'conditions' => array(
'forums.id = Thread.forum_id'
)
)
)
);
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 [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
