CakePHP - Sorting using HABTM Join Table Field
- by Ashok
Hello Cake Gurus, here's my problem:
Table1: Posts
id - int
title - varchar
Table2: Categories
id - int
name - varchar
HABTM JoinTable: categories_posts
id - int
post_id - int
category_id - int
postorder - int
As you can see, the join table contains a field called 'postorder' - This is for ordering the posts in a particular category. For example,
Posts: Post1, Post2, Post3, Post4
Categories: Cat1, Cat2
Ordering:
Cat1 - Post1, Post3, Post2
Cat2 - Post3, Post1, Post4
Now in CakePHP,
$postpages = $this->Post->Category->find('all');
gives me a array like
Array
(
[0] => Array
(
[Category] => Array
(
[id] => 13
[name] => Cat1
)
[Post] => Array
(
[0] => Array
(
[id] => 1
[title] => Post2
[CategoriesPost] => Array
(
[id] => 17
[post_id] => 1
[category_id] => 13
[postorder] => 3
)
)
[1] => Array
(
[id] => 4
[title] => Post1
[CategoriesPost] => Array
(
[id] => 21
[post_id] => 4
[category_id] => 13
[postorder] => 1
)
)
)
)
)
As you can see [Post], they are not ordered according to [CategoriesPost].postorder but are ordered according to [CategoriesPost].id. How can I get the array ordered according to [CategoriesPost].postorder?
Thanks in advance for your time :)
Edit:
The Queries from Cake's SQL Log are:
SELECT `Category`.`id`, `Category`.`name` FROM `categories` AS `Category` WHERE 1 = 1
SELECT `Post`.`id`, `Post`.`title`, `CategoriesPost`.`id`, `CategoriesPost`.`post_id`, `CategoriesPost`.`category_id`, `CategoriesPost`.`postorder` FROM `posts` AS `Post` JOIN `categories_posts` AS `CategoriesPost` ON (`CategoriesPost`.`category_id` IN (13, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52) AND `CategoriesPost`.`post_id` = `Post`.`id`)
What I am looking for is how to make cake put a Order By CategoriesPost.postorder in that second SELECT SQL Query.
