CakePHP 2.5.6 - Query with Paginator

Question

i'm trying to make a working query (have tried in cakephp direct query) like this one:

SELECT *,
   MATCH(b.title) AGAINST('web') as 'tscore',
   MATCH(b.description) AGAINST('web') as 'dscore',  
   MATCH(ba.authorName) AGAINST('web') as 'ascore',
   MATCH(bc.category) AGAINST('web') as 'cscore'
FROM books b
   LEFT JOIN books_has_book_author bhba 
   ON b.id = bhba.books_id

   LEFT JOIN book_author ba
   ON bhba.books_id = ba.id 

   LEFT JOIN book_categories bc
   ON b.book_categories_id = bc.id

WHERE 
   MATCH(b.title) AGAINST('web')
   OR MATCH(b.description) AGAINST('web')  
   OR MATCH(ba.authorName) AGAINST('web')
   OR MATCH(bc.category) AGAINST('web')
ORDER BY
   ('tscore' + 'dscore' + 'ascore' + 'cscore') DESC"

I have read a lot and i'm trying to implement this with cakephp Paginator.

I have tried a lot working with options/conditions but I think i'm missed something...

What I have done already:

My controller

$searchTerm = filter_var($this->request->query['searchTerm'], FILTER_SANITIZE_STRING);

# Make query
$data = $this->Search->searchTermReturned($searchTerm);


## Paginator don't work this way!!
$this->Paginator->settings = $this->paginate;
$paginatedData = $this->Paginator->paginate($data);
$this->set('data', $paginatedData);
#######  


 ## Pagintor Work this way!!
 $this->Paginator->settings = $this->paginate;
 $paginatedData = $this->Paginator->paginate('Search'); // My model
 $this->set('data', $paginatedData); // Just sent it to view
 #######

My Model

class Search extends AppModel {

public $name = 'books';

public function searchTermReturned($searchTerm) 
{

    return $this->query('SELECT *,
        MATCH(b.title) AGAINST("'.$searchTerm.'") as \'tscore\',
        MATCH(b.description) AGAINST("'.$searchTerm.'") as \'dscore\',  
        MATCH(ba.authorName) AGAINST("'.$searchTerm.'") as \'ascore\',
        MATCH(bc.category) AGAINST("'.$searchTerm.'") as \'cscore\'
    FROM books b

    LEFT JOIN books_has_book_author bhba 
        ON b.id = bhba.books_id

    LEFT JOIN book_author ba
        ON bhba.books_id = ba.id 

    LEFT JOIN book_categories bc
        ON b.book_categories_id = bc.id

    WHERE 
        MATCH(b.title) AGAINST("'.$searchTerm.'")
        OR MATCH(b.description) AGAINST("'.$searchTerm.'")  
        OR MATCH(ba.authorName) AGAINST("'.$searchTerm.'")
        OR MATCH(bc.category) AGAINST("'.$searchTerm.'")
    ORDER BY
        (\'tscore\' + \'dscore\' + \'ascore\' + \'cscore\') DESC'); 
}
}

I have tried set options and conditions on paginate function but can set all the parameters in the right way.

What I want to do is:

$paginatedData = $this->Paginator->paginate($query);

How can I achieve this with core functions from cakePHP?

Thank you all in advance.

Best regards, Marcelo

Please always mention your exact CakePHP version and tag your question accordingly! Also it's advised to show what you've already and what exactly you are having problems with. — ndm
It may be better to try to use cakephp search plugin github.com/CakeDC/search — Salines

lmarcelocc lmarcelocc · Accepted Answer · 2014-11-18T10:35:46

Well,

if I can help someone with the same problem here's the solution I arrive:

# Searching query
    $this->Paginator->settings = array(
        'limit' => 5,
        'fields' => array('*', 'MATCH(b.title) AGAINST("'.$searchTerm.'") as \'tscore\',
                            MATCH(b.description) AGAINST("'.$searchTerm.'") as \'dscore\',  
                            MATCH(ba.authorName) AGAINST("'.$searchTerm.'") as \'ascore\',
                            MATCH(bc.category) AGAINST("'.$searchTerm.'") as \'cscore\''),
        'conditions' =>  'MATCH(b.title) AGAINST("'.$searchTerm.'")
                            OR MATCH(b.description) AGAINST("'.$searchTerm.'")  
                            OR MATCH(ba.authorName) AGAINST("'.$searchTerm.'")
                            OR MATCH(bc.category) AGAINST("'.$searchTerm.'")',
        'joins' => array(
                            array(
                                'table' => 'books_has_book_author',
                                'alias' => 'bhba',
                                'type' => 'LEFT',
                                'conditions' => array(
                                    'b.id = bhba.books_id'
                                )
                            ),
                            array(
                                'table' => 'book_author',
                                'alias' => 'ba',
                                'type' => 'LEFT',
                                'conditions' => array(
                                    'bhba.books_id = ba.id'
                                )
                            ),
                            array(
                                'table' => 'book_categories',
                                'alias' => 'bc',
                                'type' => 'LEFT',
                                'conditions' => array(
                                    'b.book_categories_id = bc.id'
                                )
                            )
                        ),
        'order' => '(`tscore` + `dscore` + `ascore` + `cscore`) DESC',
    );


    $this->set('data', $this->Paginator->paginate());

Thanks all!

CakePHP 2.5.6 - Query with Paginator

1 Answers