Wednesday, March 12, 2008

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!

Labels: , , , ,

4 Comments:

Blogger Anton said...

something with array_keys and implode would make the job easier.

5:21 PM  
Blogger Vlad said...

I think you right ;-)

2:25 AM  
Blogger Rafael Bandeira said...

$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?

10:08 AM  
Blogger Petr 'PePa' Pavel said...

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.

2:29 PM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home