Doctrine select entities with relation condition

1
votes

Given two tables:

notification

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

notification_seen

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

user

id               (int PK)

mapped as simple Doctrine entities where Notification has a relation defined as follows:

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

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

and User entity:

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

I want to select all Notification objects which are not connected through the seenBy relation.

How to select such entities in the NotificationRepository with the QueryBuilder? Any help would be appreciated.

UPDATE:

Current method implementation in the NotificationRepository:

public function getUnreadNotification(User $user): array
{
    return $this->createQueryBuilder('u')
        ->orderBy('u.date', 'desc')
        ->getQuery()
        ->getResult();
}
2
phpsymfonydoctrine-ormdoctrine
If there is no notificationSeen between user and notification, how can you find any notification? There is any other links between a user and a notification that are not exposed in your post? Or you mean that you want to create a new notificationSeen relationship? - olibiaz
I want to select those entities through the Notification repository, not through the User entity. So to speak I need to select all entities from the notification table that don't exists in the notification_seen table for the specified user. - emix
Can we have a look at the code you've tried? the query builder part? - olibiaz
Well I added what I got, which is essentially nothing. - emix
The Notification has no direct relation to User except through NotificationSeen, how do you expect to implement a join in the db if there is NotificationSeen to join the other two tables? - jcroll

2 Answers

2
votes

Basically you just want to query the db for Notifications that have no related NotificationSeen:

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();
}
0
votes

Here's my proposal for Mike's comment

[...] So to speak I need to select all entities from the notification table that don't exists in the notification_seen table for the specified user.

I didn't try, but it should work.

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

Tell me if something's wrong