Doctrine Single Table Inheritance Query All Instances Of

Question

I'm working on a notification system, so I have a notification abstract class and sub-classes (forumPostNotification, privateMessageNotification, etc). They are stored using Single Table Inheritance, so they're all in one table with a discriminating field.

I would like to get all the notifications that apply to a user at once, instead of having to query each type of notification individually, however I'm not sure how to do this in DQL/symfony (it would be easy in SQL).

I believe this: (Doctrine 2: how to write a DQL select statement to search some, but not all the entities in a single table inheritance table) is similar to what I'd like to achieve, but I'm not sure how to query the abstract object. It's also not in the Entity directory, but in Entity/Notifications/Notification.php.

I'll add some code for clarification:

Notification.php

/**
 * Notification Class    
 *@ORM\Entity
 * @ORM\InheritanceType("SINGLE_TABLE")
 * @ORM\DiscriminatorColumn(name="type", type="string")
 * @ORM\DiscriminatorMap({
 *     "notification"="Notification",
 *     "forumPostNotification"="ForumPostNotification",
 *     ...
 * })
 * @ORM\Table(name="notification")
 */
abstract class Notification
{
  /**
   * @ORM\ManyToOne(targetEntity="Acme\MainBundle\Entity\User", inversedBy="notifications")
   * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
   */
  private $user;
  //...
}

ForumPostNotification.php

/**
 * Forum Post Notification
 * @ORM\Entity
 */
class ForumPostNotification extends Notification
{
  //..
}

PrivateMessageNotification.php

/**
 * Private Message Notification
 * @ORM\Entity
 */
class PrivateMessageNotification extends Notification
{
  //..
}

I'd like to be able to do something like this, one way or another (I understand that I can't query from Notification, since it's an abstract class. I just wrote it like this to convey what I'd like to achieve):

$notifications = $em->createQuery('
  SELECT n
  FROM AcmeMainBundle:Notification n
  WHERE n.dateDeactivated IS NULL
  ORDER BY n.dateCreated ASC
')->getResult();

simply change your abstract class to just class and you will be able to query it without any problem. or create `class QuerableNotification extends Notification` and query that. — gondo, Jul 23 '14 at 09:25

score 2 · Answer 1 · answered Jan 08 '14 at 08:09

2

we have created similar situation with orders and products. Because you can have different types of product inside one order we made one parent class Product and inherited ex. SpecialProduct, SalesProduct etc.

We were able to define a relation between Order (in your case User) and "Product" (in your case Notification), and that's all. We get every Products for the order by $order->getProducts(). The method returns us a list of well prepared products with specific classes ex

order->products[SingleProduct, SingleProduct, SingleProduct, SpecialProduct, SalesProduct, SingleProduct]

So, in conclusion. Only one thing you need to do to get all notifications per user is defining a proper relation between your user and abstract parent class.

It was simply, but... it's not so good when you're going to get only notification from specific type. The query passed in your link is not pretty. In my opinion you should create a proper queryBuilder - it's quite similar.

At the end you cannot use the $user->getNotifications(), but you have to get notifications directly from repository -

$em->get('AcmeBundle:User')->getForumPostNotifications()

Kind regards, Piotr Pasich

answered Jan 08 '14 at 08:09

Piotr Pasich

2,639
2
12
14

How did you define the relation? I believe it can only be one way (many notifications to one user). When I try to define it as a two way and add the One-to-Many "$notifications" field to the User class, I can't update the database since doctrine can't find the Notification class. I believe this is because you can't have an abstract class be the "one" side. – Jason Lin Jan 10 '14 at 01:59
one order has many products. But I think that you misunderstood the case of having this abstract class in doctrine. You cannot save it to database because doctrine cannot treat this class as an available option - it's only a base of the rest of classes. – Piotr Pasich Jan 10 '14 at 07:02
I understand that an abstract class can't be saved to the database. I also understand that One order has many products. I must be implementing something wrong, since if I do var_dump($this->getUser()->getNotificaitons()); I get a PersistentCollection with private properties: snapshot, owner, association, em, etc. It's not an ArrayCollection of Notification objects. Could you provide some sample code for me to look at from your situation? – Jason Lin Jan 10 '14 at 18:36
Typo above: var_dump($this->getUser()->getNotifications()); – Jason Lin Jan 10 '14 at 18:51

score 0 · Answer 2 · answered Oct 13 '20 at 07:38

This is in fact so simple, I'm amazed they've not documented it properly. In your repository, do:

return $this->_em->createQueryBuilder()
    ->select('notification')
    ->from(Notification::class, 'notification')
    // whatever else you need

please notice creating the query builder from entity manager, not entity repository itself

Doctrine Single Table Inheritance Query All Instances Of

2 Answers2