CakePHP 1.2: paginator and custom query
Hello everybody!
;-)
Today I'm broken my brain for making custom query in paginator ;-)
So. Let's begining!
I have two models (don't ask why ;-)
Post: id, body, ...
Answer: id, post_id, body ...
What I need? I want to search for %query% in Answer.body and in Post.body.
But!
I want to get list only like this: site.com/posts/view/id
Other words: if I found %query% in both body (Answer.body and Post.body) I don't need to get 2 url. Only one.
How to get it with SQL (MySQL)? It's so easy if you use DISTINCT in your query.
But, after 2 hours with cofee and brainstorming... I made this code:
$datas = $this->Post->findAll($query,"id");
foreach ($datas as $key => $value) {
$dat .= $value['Post']['id'] . " ";
}
$dat = explode (" ",$dat);
$dat = array_unique ($dat);
array_pop($dat);
$dat = implode(",",$dat);
$this->Post->recursive = -2;
$this->paginate = array ('limit' => '10','conditions' => 'Post.id IN ('. $dat .')', 'order' => 'Post.created DESC');
Yes, it's not so good idea, but it's simple, it's work. And I think - Keep It Simple St... ;-)
If you have better idea, I very glad to see your code!
;-)
Today I'm broken my brain for making custom query in paginator ;-)
So. Let's begining!
I have two models (don't ask why ;-)
Post: id, body, ...
Answer: id, post_id, body ...
What I need? I want to search for %query% in Answer.body and in Post.body.
But!
I want to get list only like this: site.com/posts/view/id
Other words: if I found %query% in both body (Answer.body and Post.body) I don't need to get 2 url. Only one.
How to get it with SQL (MySQL)? It's so easy if you use DISTINCT in your query.
But, after 2 hours with cofee and brainstorming... I made this code:
$datas = $this->Post->findAll($query,"id");
foreach ($datas as $key => $value) {
$dat .= $value['Post']['id'] . " ";
}
$dat = explode (" ",$dat);
$dat = array_unique ($dat);
array_pop($dat);
$dat = implode(",",$dat);
$this->Post->recursive = -2;
$this->paginate = array ('limit' => '10','conditions' => 'Post.id IN ('. $dat .')', 'order' => 'Post.created DESC');
Yes, it's not so good idea, but it's simple, it's work. And I think - Keep It Simple St... ;-)
If you have better idea, I very glad to see your code!
Labels: array, cakephp, custom query, pagination, query
posted by Владислав at
2:51 PM
4 Comments:
something with array_keys and implode would make the job easier.
I think you right ;-)
$conditions = array('Post.body LIKE' => $search, 'Answer.body LIKE' => $search);
$fields = array('DISTINCT Post.id', 'Post.title');
$this->Post->recursive = 1;
$ids = array_keys($this->Post->find('list', compact('fields', 'conditions')));
$data = $this->paginate(array('Post.id' => $ids));
what is the recursive -2 all about?
I'm afraid Rafael's code wouldn't work because CakePHP wouldn't understand that "DISTINCT" has a special meaning. It would run the right query but later when converting the result to a list-like output it would look for an array key "DISTINCT id" which of course, is wrong.
Here's how to work around it:
$posts = $this->Post->find('all',
array('fields' => 'DISTINCT Post.id', 'Post.title'));
$postsList =
Set::combine($posts, '{n}.Post.id', '{n}.Post.title');
$postsList would then contain data in a format that find('list') returns.
This is my blog post about using SQL DISTINCT to populate a select box.
Post a Comment
Subscribe to Post Comments [Atom]
<< Home