CakePHP将自定义查询从控制器传递到模型paginate（） [英] CakePHP pass custom query from controller into model paginate()

问题描述

我使用Cakephp 2.X版本

我要运行sql like：

  SELECT field1，field2，field3，field3，
（IF（LOCATE（'abc'，field1），4,0）+ IF（LOCATE（'abc'，field2） 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分数DESC 
  

这个sql是由代码动态生成的。所以这个sql需要传递到paginate（）函数为了做分页由cakephp。看起来cakephp分页函数不够灵活，不能处理复杂的sql。

要执行这个复杂的sql，我如何覆盖paginate（） / p>

说明：

我尝试执行自定义查询分页。 Controller获取关键字并生成SQL，我想将SQL字符串传递给 public function paginate（Model $ model，$ conditions，$ fields，$ order，$ limi ...）函数在模型中被覆盖以运行此自定义查询。如何将SQL字符串传递给paginate函数。

任何建议或提示？

解决方案

您的问题的简短回答是，您不能通过此查询分页您的数据，或者，当完全使用计算字段。您可能已经注意到，数据，特别是在您的计算字段，不会返回到数据 - > 模型 - > variable 数组格式。相反，它作为索引数组返回。

为了实现你想在CakePHP范围内做的事，我将使用一个普通的find来获取实际存储在表中的数据，并写入一些虚拟字段，执行您要查找的计算。然后，您可以基于虚拟字段进行分页：

由于虚拟字段在执行查找时与常规字段非常相似，Controller :: paginate ）将能够通过虚拟字段进行排序。

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?

解决方案

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将自定义查询从控制器传递到模型paginate（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！