我怎样才能在cake php中进行sql联合？

发布于 2024-12-04 21:32:33 字数 492 浏览 1 评论 0原文

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

  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 查询，它也应该返回关联的表数据...

有什么想法如何使用 cake 内置函数来获取数据吗？

原文

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

So when I use
$this->Video->find('all')--> I get all the result of the associated table datas.

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧时浪漫 2024-12-11 21:32:33

您可以通过 4 种或更多不同的方式来执行此操作...最简单但不推荐的方法是使用

$this->Model->query($query);

其中 $query 是上面所述的查询。

第二种方法，但可能不是你想要的，是重做你的sql查询，你会得到相同的结果（但不与别名分开），如下所示：

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

这个查询可以很容易地用这样的find完成

$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 中的 Union 语法，它可以直接构建语句。

第四种方法与第一种方法不太一样，您必须创建一个具有 beforeFind 函数的行为，并且必须检查选项是否并集并创建查询或创建类似第三种选项的内容。

您将使用这样的查找来调用它，

$this->Video->find('all', array('conditions'=>$conditions, 'union'=> $union));

这将不太像可链接或可包含的行为。

第五个方法是修改你的 cakephp sql 驱动程序...这个，我真的不知道你必须做的更改，但这是实现这一点的一种方法...这个驱动程序负责解释和创建查询，连接到数据库并执行查询...

请记住，cakephp find 会进行必要的检查，以防止 SQLInyection 和其他风险... $model->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.

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

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))

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.

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.

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.

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...

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

回复收藏 0 原文