Mapping and DQL

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

Solution

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!