我如何做蛋糕php中的sql union？ [英] How can I do sql union in cake php?

问题描述

我必须获取模型的所有关联数据。基本上UNION查询如下：

I have to get all the associative data for the model. Basically the UNION query is as below:

  SELECT * FROM `videos` AS `U1` 
  WHERE `U1`.`level_id` = '1' AND `U1`.`submitted_date` > '2011-09-11'
UNION 
  SELECT * FROM `videos` AS `U2`
  WHERE `U2`.`level_id` = '1' AND `U2`.`submitted_date` < '2011-09-11'
  ORDER BY  submitted_date DESC
  LIMIT 0,10

所以当我使用
$ this-> Video-> find（'all'） - > 表数据。

现在我想使用UNION SQL查询，并且应该返回关联的表数据...

Now I want to use the UNION SQL query and that should return associated table data too...

任何想法如何使用蛋糕内置函数获取数据？

Any ideas how to use the cake inbuilt function to get data?

推荐答案

方法...最简单但不推荐使用$ this-> Model-> query（$ query）;其中$ query是上面的查询。

You can do this in 4 or more different ways... the easiest but not recomended is using $this->Model->query($query); where $query is the query stated above.

第二种方式，但可能不是你想要的，是重做你的sql查询，你会得到相同的结果与别名）像这样：

The second way but may not be what you want, is to redo your sql query you will get same result (but not separated with the alias) like this:

SELECT * FROM `videos` AS `U1` 
WHERE `U1`.`level_id` = '1' AND (`U1`.`submitted_date` > '2011-09-11' OR `U1`.`submitted_date` < '2011-09-11')
ORDER BY  submitted_date DESC
LIMIT 0,10

这个查询可以很容易地找到， p>

This query can be easily done with find like this

$conditions = array(
    'Video.level_id'=>1,
    'OR' => array(
        'Video.submitted_date <'=> '2011-09-11',
        'Video.submitted_date >'=> '2011-09-11'
    )
);
$this->Video->find('all', array('conditions'=>$conditions)) 

第三种方式将是Abba Bryant谈论的方式，这里详细解释在cakePhp中的联合语法直接构建语句。

The third way will be the one that Abba Bryant talk about, explained in detail here Union syntax in cakePhp that works building the statement directly.

第四种方式会更喜欢第一种方式，行为，有一个beforeFind函数，你必须检查一个选项联合和创建查询或创建类似第三个选项。

The fourth way will like the first one more less, you will have to create a behaviour that have a beforeFind function and there you will have to check if a option union and create the query or to create something like the the third option.

您将使用这样的名称调用它
$ this-> Video-> find（'all'，array（'conditions'=> $ conditions ，'union'=> $ union））;
这将是一个更少的可链接或可控行为。

you will call it with a find like this $this->Video->find('all', array('conditions'=>$conditions, 'union'=> $union)); This will be something more less like the linkable or containable behavior.

第一种方法是修改你的cakephp sql驱动程序...这一个，我真的不知道你要做的更改，但它是一种方式得到的...这个驱动程序负责解释和创建查询，连接到db并执行查询...

The fith way is to modified your cakephp sql driver... this one, i don't really know the changes you have to do, but it is a way to get to that... This drivers are the responsible to interpret and create the queries, connect to db and execute the queries...

记住cakephp find做检查必要的，以防止SQLInyection和其他风险... $ model->查询不会做这个测试，所以要小心

REMEMBER that cakephp find do the checks neccesary to prevent SQLInyection and other risks... the $model->query will NOT do this tests so be carefull

这篇关于我如何做蛋糕php中的sql union？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！