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

Answer 1

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