原则选择具有关系条件的实体 - Doctrine select entities with relation condition

Doctrine select entities with relation condition

给定两张表:

id               (int PK)
title            (varchar(255))
date             (datetime)

notification_seen

id               (int PK)
notification_id  (int FK)
user_id          (int FK)

用户

id               (int PK)

映射为简单的Doctrine实体，其中Notification的关系定义如下:

@ORM'OneToMany(targetEntity="NotificationSeen", mappedBy="notification")
private $seenBy;

和 NotificationSeen :

@ORM'ManyToOne(targetEntity="Notification", inversedBy="seenBy")
@ORM'JoinColumn(name="notification_id")
private $notification;
@ORM'ManyToOne(targetEntity="User", inversedBy="seen")
@ORM'JoinColumn(name="user_id")
private $user;

和用户实体:

@ORM'OneToMany(targetEntity="NotificationSeen", mappedBy="users")
private $seen;

我想选择所有Notification对象，这些不是通过seenBy关系连接的。

如何使用QueryBuilder在NotificationRepository中选择这样的实体?如有任何帮助，不胜感激。

更新:

NotificationRepository:

中的当前方法实现

public function getUnreadNotification(User $user): array
{
    return $this->createQueryBuilder('u')
        ->orderBy('u.date', 'desc')
        ->getQuery()
        ->getResult();
}

基本上你只是想查询数据库中没有相关NotificationSeen的Notifications:

public function getUnreadNotification(User $user): array
{
    return $this->createQueryBuilder('n')
        ->join('n.seenBy', 'ns')
        ->where('ns.id IS NULL')
        ->orderBy('n.created', 'desc')
        ->getQuery()
        ->getResult();
}

这是我对Mike评论的建议

[…也就是说，我需要从通知表中选择指定用户的notification_seen表中不存在的所有实体。

我没试过，但应该可以。

public function getUnreadNotifications(User $user)
{
    // Create query builder
    $qb = $this->createQueryBuilder('n');
    // someone saw the notification
    $nobodySawIt = $qb->expr()->eq('SIZE(n.seenBy)', 0);
    // Create expression for 'another guy saw the notification, but not my user'
    $someOneElseSawIt = $qb->expr()->andX(
        $qb->expr()->neq('SIZE(n.seenBy)', 0), // someone saw the notification
        $qb->expr()->neq('u.id', $user->getId()) // but not this user
    );
    // Create the query
    $qb
        ->leftJoin('n.seenBy', 's') // Join on NotificationSeen
        ->leftJoin('s.user', 'u') // Join on User
        ->orWhere($nobodySawIt) // I want unread notifications
        ->orWhere($someOneElseSawIt); // and notifications that this guys didn't read yet
      /*
        ->addOrderBy(alias.property)
        ->setFirstResult($offset)
        ->setMaxResults($limit);
       */
    return $qb->getQuery()->getResult();
}

告诉我有什么不对劲