如何将复杂的 MySQL 查询转换为 Doctrine2

2022-01-03 00:00:00 mysql symfony doctrine-orm

我正在尝试使用学说创建加权搜索.这就是我在直接 sql 中的做法.我想知道如何将它转换为我们的学说 2 方法.我正在尝试使用 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 来完成.Doctrine 不支持 IF 语句.但是同样的功能可以通过 CASE 语句来实现.示例 DQL 如下所示，

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

相关文章