Mapping and DQL

Question

I have 3 tables - user, area, and contacts. A contact can belong to a user or an area. A user can belong to many areas.

I want to pull all the contacts that belong to a user (as specifically defined in the DB), as well as all contacts that belong to the same area as the user.

Can I get a fresh set of eyes on my Database mapping, and the query I need to write in DQL to get what I want. Am I doing something wrong in my database mapping?

I'm definitely a SQL person, and am able to easily fetch what I want in plain SQL. In plain SQL, here's what I want to do:

 select c.* from contact c LEFT JOIN user_area ua ON c.area_id=ua.area_id where (ua.user_id=XXX OR c.user_id=XXX);

USER

/**
 * @ORM\ManyToMany(targetEntity="area", inversedBy="areas")
 * @ORM\JoinTable(name="user_area",
 *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="area_id", referencedColumnName="id")}
 *      )
 */
private $areas;

/**
 * @ORM\OneToMany(targetEntity="Contact", mappedBy="user")
 */
private $contacts;

CONTACT

/**
 * @ORM\ManyToOne(targetEntity="Area")
 * @ORM\JoinColumn(name="area_id", referencedColumnName="id")
 */
private $area;

/**
 * @ORM\ManyToOne(targetEntity="User", inversedBy="Contacts")
 * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
 */
private $user;

AREA

/**
 * @ORM\ManyToMany(targetEntity="User", mappedBy="users")
 */
private $users;

/**
 * @ORM\OneToMany(targetEntity="Contact", mappedBy="area")
 */
private $contacts;

The main problem I'm running into is that DQL really wants you to query an object, and it's just plain easier in SQL to query the user/area relationship table to get what I want. I tried to write an query that pulls areas from contacts, then users from contacts, and then users from areas but I get an error message that "users" isn't a defined index in my areas object. Again, I'm a Doctrine newbie, so I'm probably doing something wrong.

Here's my attempt at a query, from the User object in Symfony:

    $qb = $em->createQueryBuilder()
        ->addSelect('c')
        ->from('MyBundle:Contact', 'c')
        ->leftJoin('c.area', 'ca')
        ->leftJoin('c.user', 'cu')
        ->leftJoin('ca.users', 'cau')
        ->add('where', 'c.user = ?1 OR cau.id = ?1')
        ->add('orderBy', 'c.name')
        ->setParameter(1, $this->getId());

Answer 1

Someone should have slapped me for providing that previous answer. While it got the job done, I was absolutely right, it was not optimized. Queries using that method were taking 3 seconds to go back and forth to the database (3 seconds!). Clearly, there were plenty of other things going on in my world that took away from performance as a requirement for getting this done, but things have changed. I managed to break down this query into two smaller (Doctrine generated) ones, each taking maybe 0.2 or 0.3s.

    $areas = $user->getAreas();

    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb->select('c')
        ->from('MyBundle:Contact', 'c')
        ->where($qb->expr()->in('c.area', '?1'))
        ->orWhere('c.user = ?2')
        ->setParameter(1, $areas->toArray())
        ->setParameter(2, $user);
    $query = $qb->getQuery();
    $result = $query->getResult();
    return $result;

The fact that I have to call $user->getAreas() adds a database query (if Doctrine doesn't already have that information), but this code, using Query Builder expressions, works much better (0.3s vs. 3s is 10% of the original query time!).

I think the main concept I was missing back then was that the Query Builder wants to work with your objects (Entities), and the properties you've defined in your entities. Coming from a strong SQL background, and knowing the specific SQL query I wanted Doctrine to produce, I wasn't approaching the problem properly.

Hope this update to an 8-month old question helps somebody!

Answer 2

So it turns out you can't fetch objects of objects in DQL. I needed to fetch all "Area"s (an object of "Contact") and then fetch all of that Area's "User"s.

In DQL, you can specify multiple "from()" helper methods, and this was what I needed to get the job done.

$qb = $em->createQueryBuilder()
    ->addSelect('c')
    ->from('MyBundle:Contact', 'c')
    ->from('MyBundle:User', 'u')
    ->leftJoin('c.area', 'ca')
    ->leftJoin('c.user', 'cu')
    ->leftJoin('u.areas', 'ua')
    ->add('where', 'c.user = ?1 OR (ua.id=ca.id AND u.id = ?1')
    ->add('orderBy', 'c.name')
    ->setParameter(1, $this->getId());

The resulting SQL generated from Doctrine doesn't seem particularly optimized, but it gets the job done. If anyone has any thoughts on getting Doctrine to better optimize the following query, I'd love to hear opinions.

SELECT m0_.id AS id0, m0_.name AS name1, m0_.email AS email2, m0_.media_area_id AS media_area_id3, m0_.user_id AS user_id4 FROM contact m0_ LEFT JOIN user u1_ ON m0_.user_id = u1_.id LEFT JOIN area m2_ ON m0_.area_id = m2_.id, user u3_ LEFT JOIN user_area u5_ ON u3_.id = u5_.user_id LEFT JOIN area m4_ ON m4_.id = u5_.area_id WHERE u1_.id = ? OR (m4_.id = m2_.id AND u3_.id = ? );