postgresql - 将pgSQL转换为学说

我正在接收错误[语法错误]行0，列257:错误：应为=，，>，>=！=，执行此条令查询生成器查询时得到“18”。数据库是pgSQL。

        $query = $this->entityManager
            ->createQueryBuilder()
            ->select('s.subscriptionReference')
            ->from(
                'OTDatabase\Entity\Membership\Member',
                'm'
            )
            ->innerJoin(
                'OTDatabase\Entity\Membership\MemberSubscriptions',
                's',
                'WITH',
                'm.record = s.memberReference'
            );

        $query
            ->andWhere('s.list = 1');

        $query
            ->andWhere('m.validated IN (:validated)')
            ->setParameter('validated', array('1', '3'));

        $query
            ->andWhere($query->expr()->lt(gmdate('Y-m-d H:i:s') , 's.subscription_begins'));

        $query
            ->andWhere('
            (
                ( now() <= s.subscriptionExpires )
                OR
                ( s.subscriptionBegins <= now() AND s.subscriptionExpires IS NULL )
            )');

        $result = $query->getQuery()
                        ->getArrayResult();

为了将来参考，正确的查询（借助@Logan Bailey）是：

        $query = $this->entityManager
            ->createQueryBuilder()
            ->select('s.subscriptionReference')
            ->from(
                'OTDatabase\Entity\Membership\Member',
                'm'
            )
            ->innerJoin(
                'OTDatabase\Entity\Membership\MemberSubscriptions',
                's',
                'WITH',
                'm.record = s.memberReference'
            );

        $query
            ->andWhere('s.list = 1');

        $query
            ->andWhere('m.validated IN (:validated)')
            ->setParameter('validated', array('1', '3'));

        $query
            ->andWhere($query->expr()->lt('s.subscriptionBegins' , ':subscriptionDate'))
            ->setParameter('subscriptionDate', gmdate('Y-m-d H:i:s'));

        $query
            ->andWhere($query->expr()->orX(
                $query->expr()->lt(':subscriptionDate' , 's.subscriptionExpires'),
                $query->expr()->isNull('s.subscriptionExpires')
            ));

最佳答案

你得用引号把日期括起来。您可能应该将其用作绑定参数。

$query
   ->andWhere($query->expr()->lt(':subscriptionDate' , 's.subscription_begins'))
   ->setParameter('subscriptionDate', gmdate('Y-m-d H:i:s'));

关于postgresql - 将pgSQL转换为学说，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32317598/