如何将复杂的MySQL查询转换为Doctrine2 [英] How to convert a complex MySQL Query to Doctrine2

查看:87
本文介绍了如何将复杂的MySQL查询转换为Doctrine2的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试使用原则创建加权搜索。这是我如何在直接的SQL中。我想知道如何将它转换为我们的doctrine2方法。我正在尝试使用symfony2进行搜索。

I am trying to create a weighted search using doctrine. This is how i do it in straight sql. Im wondering how i would convert it to us the doctrine2 methods. I am trying to do this search using symfony2.

此外,如果有更好的方法来做到这一点,我对此开放。谢谢。

Also if there is a better way to do this I am open to that to. Thanks.

"SELECT   *,
    IF(`name` LIKE "%$searchterm%",  20,
    IF(`name` LIKE "%$searchterm%", 10, 0)) +
    IF(`address` LIKE "%$searchterm%", 5,  0) +
    IF(`city`   LIKE "%$searchterm%", 1,  0)
    AS `weight`
 FROM `table_name`
 WHERE
     (`name` LIKE "%$searchterm%" OR
      `address` LIKE "%$searchterm%" OR
      `city`  LIKE "%$searchterm%")
 ORDER BY `weight` DESC
 LIMIT 20"


推荐答案

是的,可以使用 DQL 。原则不支持 IF 语句。但是,通过 CASE 语句可以实现相同的功能。

Yes, it can be done using DQL. Doctrine does not support IF statement. But same functionality can be achieved by CASE statement. Sample DQL is given bellow,

$dql = "SELECT t, 
          (CASE 
            WHEN (t.name LIKE :searchterm) THEN 10 
            ELSE 0
          END) + 
          (CASE 
            WHEN (t.address LIKE :searchterm) THEN 5 
            ELSE 0
          END) + 
          (CASE 
            WHEN (t.city LIKE :searchterm) THEN 1 
            ELSE 0
          END)
          AS weight 
        FROM YourBundleName:TableName t 
        WHERE 
          t.name LIKE :searchterm OR 
          t.address LIKE :searchterm OR 
          t.city LIKE :searchterm
        ORDER BY weight DESC
      ";
$query = $entityManager->createQuery($dql)
  ->setFirstResult(0)
  ->setMaxResults(20)
  ->setParameter('searchterm' , $searchterm)
  ;

这篇关于如何将复杂的MySQL查询转换为Doctrine2的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆