(学说)选择数组集合不为空的位置

Question

我有一个类帖子：

/**
 * @ORMEntity(repositoryClass="AppRepositoryPostRepository")
 */
class Post
{
    const TYPE_TEXT   = 1;
    const TYPE_PHOTOS = 2;
    const TYPE_VIDEO  = 3;
    /**
     * @ORMOneToMany(targetEntity="Photo", mappedBy="post")
     */
    private $photos;
    and other properties, methods, etc... 

我只想带回任何照片的帖子。

我有一个 DQL 查询，如下所示：

    $qb = $this->createQueryBuilder('p');
    $qb->select('p, postPhotos, etc...')
        ->leftJoin('p.photos', 'postPhotos')
        ->leftJoin('p.videos', 'postVideos')
        etc...
    if ($mediaType != null)
    {
        switch ($mediaType) {
            case Post::TYPE_PHOTOS:
                $qb->andWhere('postPhotos != :null')
                    ->setParameter('null', null);

"！= ：null"不起作用，COUNT（postPhotos）也不起作用（显然是出于聚合原因）。

有没有办法我可以指定只带回有 1 张或多张照片的帖子？

Answer 1

快速回答：如果您将 leftJoin 的用法替换为 just join（或 innerJoin），那么您将得到您想要的：只有至少有 1 张照片的帖子。

详

如果你看看这个有用的SO问答：

不同的 MySQL 连接方法

。你会发现一些优秀的维恩图，显示了左连接和内连接之间的区别。然后，如果你查看 Doctrine\ORM\QueryBuilder 类，你会发现它们有三种连接方法：

• join（只调用innerJoin）
• 内部加入
• 左加入

    /**
     * Creates and adds a join over an entity association to the query.
     *
     * The entities in the joined association will be fetched as part of the query
     * result if the alias used for the joined association is placed in the select
     * expressions.
     *
     * <code>
     *     $qb = $em->createQueryBuilder()
     *         ->select('u')
     *         ->from('User', 'u')
     *         ->join('u.Phonenumbers', 'p', ExprJoin::WITH, 'p.is_primary = 1');
     * </code>
     *
     * @param string      $join          The relationship to join.
     * @param string      $alias         The alias of the join.
     * @param string|null $conditionType The condition type constant. Either ON or WITH.
     * @param string|null $condition     The condition for the join.
     * @param string|null $indexBy       The index for the join.
     *
     * @return self
     */
    public function join($join, $alias, $conditionType = null, $condition = null, $indexBy = null)
    {
        return $this->innerJoin($join, $alias, $conditionType, $condition, $indexBy);
    }
    /**
     * Creates and adds a join over an entity association to the query.
     *
     * The entities in the joined association will be fetched as part of the query
     * result if the alias used for the joined association is placed in the select
     * expressions.
     *
     *     [php]
     *     $qb = $em->createQueryBuilder()
     *         ->select('u')
     *         ->from('User', 'u')
     *         ->innerJoin('u.Phonenumbers', 'p', ExprJoin::WITH, 'p.is_primary = 1');
     *
     * @param string      $join          The relationship to join.
     * @param string      $alias         The alias of the join.
     * @param string|null $conditionType The condition type constant. Either ON or WITH.
     * @param string|null $condition     The condition for the join.
     * @param string|null $indexBy       The index for the join.
     *
     * @return self
     */
    public function innerJoin($join, $alias, $conditionType = null, $condition = null, $indexBy = null)
    {
        $parentAlias = substr($join, 0, strpos($join, '.'));
        $rootAlias = $this->findRootAlias($alias, $parentAlias);
        $join = new ExprJoin(
            ExprJoin::INNER_JOIN, $join, $alias, $conditionType, $condition, $indexBy
        );
        return $this->add('join', [$rootAlias => $join], true);
    }
    /**
     * Creates and adds a left join over an entity association to the query.
     *
     * The entities in the joined association will be fetched as part of the query
     * result if the alias used for the joined association is placed in the select
     * expressions.
     *
     * <code>
     *     $qb = $em->createQueryBuilder()
     *         ->select('u')
     *         ->from('User', 'u')
     *         ->leftJoin('u.Phonenumbers', 'p', ExprJoin::WITH, 'p.is_primary = 1');
     * </code>
     *
     * @param string      $join          The relationship to join.
     * @param string      $alias         The alias of the join.
     * @param string|null $conditionType The condition type constant. Either ON or WITH.
     * @param string|null $condition     The condition for the join.
     * @param string|null $indexBy       The index for the join.
     *
     * @return self
     */
    public function leftJoin($join, $alias, $conditionType = null, $condition = null, $indexBy = null)
    {
        $parentAlias = substr($join, 0, strpos($join, '.'));
        $rootAlias = $this->findRootAlias($alias, $parentAlias);
        $join = new ExprJoin(
            ExprJoin::LEFT_JOIN, $join, $alias, $conditionType, $condition, $indexBy
        );
        return $this->add('join', [$rootAlias => $join], true);
    }

将代码更改为使用 innerJoin（或只是连接）将导致 Doctrine 在生成的 SQL 中发出 INNER JOIN，该 SQL 将仅返回连接两侧存在"某些东西"的记录，因此，任何没有照片的帖子都不会包含在结果中。