CakePHP pass custom query from controller into model paginate()

Question

I am using Cakephp 2.X version

Im going to run sql like:

 SELECT field1, field2, field3, field3,
            ( IF(LOCATE('abc', field1), 4, 0) + IF(LOCATE('abc', field2), 3, 0) + IF(LOCATE('abc', field3), 2, 0) ) AS score
            FROM sometable
            WHERE ( (field1 LIKE '%abc%')  OR  (field2 LIKE '%abc%')  OR  (field3 LIKE '%abc%') )
            ORDER BY score DESC

this sql is generated by code dynamically. So this sql need to be passed into paginate() function in order to do the pagination by cakephp. It seems that cakephp pagination function is not flexible enough to deal with complex sql.

To execute this complex sql, How can I override paginate() to paginate my result?

Description:

I try to do Custom Query Pagination. The Controller get keywords and generate the SQL, I want to pass SQL string to public function paginate(Model $model, $conditions, $fields, $order, $limi ...) function which is overridded in Model to run this custom query. How could i pass SQL string to paginate function.

Any suggestion or hint please?

You need to provide some of your code to give some sort of context for what you are doing. I will tell you that, given the question, you are probably not going to be able to do what you are trying to do. What are you using a custom query for? Since custom queries tend to be needed only when CakePHP cannot accomplish something, most likely the paginate function will not either. You'll probably need to write your own pagination for a custom query. — Scott Harwell
@Scott Harwell thank you for reply. my question is to deal with some complex sql, i want to pass SQL into paginate function directly, rather than using basic fields, condition parameters. In my case, i need to use Model::query() then write my own pagination? any suggesion — EeE
I can't really make a suggestion since I have no context for an answer. If you provide your query, then people can make suggestions for a solution because they understand what you are trying to do with your data. — Scott Harwell
@Scott Harwell sorry my bad. i have update my question with query. thank you for your time — EeE

Scott Harwell Scott Harwell · Accepted Answer · 2012-01-12T12:18:50

The short answer to your question is that you cannot paginate your data through this query -- or, when using calculated fields altogether. As you have probably noticed, the data, especially in your calculated fields, is not returned to cake in the data->Model->variable array format. Rather, it is returned as an indexed array.

In order to achieve what you are trying to do within the scope of CakePHP, I would use a normal find to get the data that is actually stored in the table and write some Virtual Fields that do the calculations that you are looking for. Then, you can paginate based on the virtual fields:

Since virtual fields behave much like regular fields when doing find’s, Controller::paginate() will be able to sort by virtual fields too.

CakePHP pass custom query from controller into model paginate()

1 Answers