jquery - Symfony - 使用 Doctrine 创建复杂查询

Question

在 Symfony 中使用 Doctrine 创建更复杂查询的最佳方法是什么？例如，我需要具有 3 个参数、两个 AND 和一个嵌套 OR 条件的查询。在 mySQL 中，它看起来像这样:

        SELECT *
        FROM schedule sched
        WHERE shed.date = '01-01-2018'  --first parameter
        AND (sched.timefrom BETWEEN 8 AND 12   -- second and third parameter
        OR sched.timeto BETWEEN 8 AND 12)      -- also second and third

我尝试了多种方法，但没有任何效果:

  $query = $em->createQuery(
        'SELECT sched
         FROM schedule sched
         WHERE shed.date = :d
         AND (sched.timefrom BETWEEN :tf AND :tt
         OR sched.timeto BETWEEN :tf AND :tt)'
       ) ->setParameters(['d' => $date->format('Y-m-d'), 'tf' => $timefrom, 'tt' => $timeto]);

即使我将 BETWEEN 替换为完整状态，这也不起作用，如下所示:

     (sched.timefrom > :tf AND sched.timefrom < :tt)

使用 QueryBuilder 也不起作用，我尝试了多种方法，这是最后一种(也尝试使用 expressionBuilder，也不起作用):

      $repository = $this->getDoctrine()->getRepository(schedule::Class);
      $query = $repository->createQueryBuilder('sched');
      $query->where('sched.date = :date AND ((sched.timefrom > :timefrom AND sched.timefrom < :timeto) OR (shced.timeto > :timefrom AND sched.timeto < :timeto))')
      ->setParameter('date', $date->format('Y-m-d'))
      ->setParameter('tf', $timefrom)
      ->setParameter('tt', $timeto);
       $query = $query->getQuery();

还有:

      $repository = $this->getDoctrine()->getRepository(schedule::Class);
      $query = $repository->createQueryBuilder('sched');
      $query->where('sched.date = :date')
      ->andWhere('sched.timefrom > :timefrom AND sched.timefrom < :timeto') 
      ->orwhere('sched.timeto > :timefrom AND sched.timeto < :timeto')
      ->setParameter('date', $date->format('Y-m-d'))
      ->setParameter('tf', $timefrom)
      ->setParameter('tt', $timeto);
       $query = $query->getQuery();

如何创建包含许多 OR 和 AND 以及一些括号的查询？谢谢!

Answer 1

你在寻找类似的东西吗？

  $repository = $this->getDoctrine()->getRepository(schedule::Class);
    $q = $repository->createQueryBuilder('sched');
    $expr = $q->expr();

    $exprQuery =
        $expr->andX(
            $expr->eq('sched.date',$date-format('Y-m-d')),
            $expr->orX(
                $expr->between('sched.timefrom',$timefrom,$timeto),
                $expr->between('sched.timeto',$timefrom,$timeto)
            )
        );

    $q->where($exprQuery);
    $q = $q->getQuery();