How to write exists subquery DQL for many to many association

Question

Given the entities below, would someone please help me understand how to write the DQL equivalent of the following SQL? I can't seem to find a good example of a DQL subquery that translates to a select on a pivot table. Thank you!

select *
from event a
where exists (
          select *
          from event_category b
          where b.event_id = a.id
            and b.category_id = 1
      )

Entities:

/**
 * @Entity
 * @Table(name="event")
 */
class Event
{
    /**
     * @Column(type="integer")
     * @Id
     */
    protected $id;

    /**
     * @JoinTable(
     *   inverseJoinColumns={
     *     @JoinColumn(name="category_id", referencedColumnName="id")
     *   },
     *   joinColumns={
     *     @JoinColumn(name="event_id", referencedColumnName="id")
     *   },
     *   name="event_category"
     * )
     * @ManyToMany(targetEntity="Category")
     */
    protected $categories;
}

/**
 * @Entity
 * @Table(name="category")
 */
class Category
{
    /**
     * @Column(type="integer")
     * @Id
     */
    protected $id;
}

Answer 1

Please have a look at Doctrine Query Language

Your example could be written :

SELECT event FROM Event event
WHERE EXISTS (
    SELECT cat FROM Category cat 
    WHERE IDENTITY(cat.event) = event.id
    AND cat.id = 1
)

Now I might be wrong but I don't think you need a subquery here.

If you want events that have a given category :

SELECT event FROM Event event JOIN event.category WHERE category.id = 1