Query Builder / DQL not working with INNER JOIN - Syntax Issue

Question

I know I have a syntax isse here however I cant figure it out. I'm trying to do a SELECT and INNER JOIN of 5 tables but Symfony is complaining about the Entities in the JOIN are used before being defined.

Actual error is as follows: [Semantical Error] line 0, col 121 near 'I ON C.id = ': Error: Identification Variable MySiteBundle:Items used in join path expression but was not defined before.

Here is the PHP code.

Note: I have shortened this query to two columns, two tables, and one join to keep the question simple and show my point. The actual query is much longer and is producing the same error.

$em = $this->getDoctrine()->getEntityManager();
$query = $em->createQuery(
    'select C.name as CName, I.id as IId
    FROM MySiteBundle:Categories C
    INNER JOIN MySiteBundle:Items I ON C.id = I.category_id');
$result = $query->getResult();

---

Update

As suggested I've done away with the DQL code and am using Query Builder code. I'm getting a very similiar error which says 'Categories c': Error: Class 'Categories' is not defined. My QB code is below.

$em = $this->getDoctrine()->getEntityManager();
$qb = $em->createQueryBuilder()
        ->select('c.name, i.id, i.image, i.name, i.description, m.id, m.quantity, m.value, m.qty_received, m.custom_image, m.custom_name, m.custom_description, u.user1fname, u.user1lname, u.user2fname, u.user2lname')
        ->from('Categories', 'c')
        ->innerJoin('Items', 'i', 'ON', 'c.id = i.category_id')
        ->innerJoin('MemberItems', 'm', 'ON', 'i.id = m.item_id')
        ->innerJoin('User', 'u', 'ON', 'm.memberinfo_id = u.id')
        ->where('u.id = ?', $slug)
        ->orderBy('c.id', 'ASC')
        ->getQuery();

$memberItems = $qb->getResult();

Any suggestions?

Knapsack

Answer 1

Louis posted while I was typing. Oh well.

DQL takes care of the join details for you based on your associations. In general, you only need to spell out the FROM class name. Something like:

'select C.name as CName, I.id as IId
FROM MySiteBundle:Categories C
INNER JOIN C.items');

And definitively use query builder.

=============================================================================

Here is an example of using query builder in Symfony 2.

public function getAccounts($params = array())
{
    // Build query
    $em = $this->getEntityManager();
    $qb = $em->createQueryBuilder();

    $qb->addSelect('account');
    $qb->addSelect('accountPerson');
    $qb->addSelect('person');
    $qb->addSelect('registeredPerson');
    $qb->addSelect('projectPerson');

    $qb->from('ZaysoCoreBundle:Account','account');

    $qb->leftJoin('account.accountPersons',  'accountPerson');
    $qb->leftJoin('accountPerson.person',    'person');
    $qb->leftJoin('person.registeredPersons','registeredPerson');
    $qb->leftJoin('person.projects',         'projectPerson');
    $qb->leftJoin('projectPerson.project',   'project');

    if (isset($params['accountId']))
    {
        $qb->andWhere($qb->expr()->in('account.id',$params['accountId']));
    }
    if (isset($params['projectId']))
    {
        $qb->andWhere($qb->expr()->in('project.id',$params['projectId']));
    }
    if (isset($params['aysoid']))
    {
        $qb->andWhere($qb->expr()->eq('registeredPerson.regKey',$qb->expr()->literal($params['aysoid'])));
    }
    $query = $qb->getQuery();

  //die('DQL ' . $query->getSQL());
    return $query->getResult();
}

Nomenclature

Hum.. Ok I've done away with the DQL code and now have QB code. I'm still getting 'Categories' is not defined. I've posted the full QB query in my original question above. – Knapsack

I hate to say it but you really really need to read the chapter on queries: docs.doctrine-project.org/projects/doctrine-orm/en/2.1/… Then start with a simple query with no joins and add to it. Add a symfony2 command so you can test it from the command line. If you still have problems then post your updated query. – Nomenclature

Answer 2

DQL does not use joins like that. They are a bit simplified. However I also found them to be underdocumented.

    $em = $this->getDoctrine()->getEntityManager();
    $query = $em->createQuery(
        'select C.name as CName, I.id as IId
        FROM MySiteBundle:Categories C
        INNER JOIN C.items I');
    $result = $query->getResult();

The actual relation used depends on your model.

I normally use the query builder.

    $em = $this->getEntityManager();
    $request = $em->getRepository('MySiteBundle:Categories');

    $qb = $request->createQueryBuilder('C');
    $query = $qb 
        ->select('C.name, I.id')
        ->innerJoin('C.items', 'I')
        ->getQuery();

Hornstone

Louis - thanks for the resonse, I'm still getting a very similiar error when moving from the DQL way to the QB way. I've posted that error and the full query in my original question. Any suggestions on how to resolve this would be much appreciated! Thanks in advance. – Knapsack

Yes, you are still insisting on specifying the join condition. You do not need to do that. Doctrine will figure it out from your model. Check how my query is written. Provide details about your model if you still have trouble. – Hornstone

Youre saying that Doctrine will leverage the relationships within the Entity class to determine how to do the join. The problem with that is that this DB schema was created by someone else and not all the relationships exist. When I add those relationships in the DB and then update the entity classes I break the site. I get multiple integrity constraint violation errors on multiple pages. To rewrite/fix those pages is not an option at this point so I cannot leverage the Entity class relationships at this time. This is why I'm trying to explicitly identify the tables/cols in join. Suggestions?? – Knapsack

Partial code ownership can't be good. Can't you get the relations added in the model? Otherwise you might have to fall back to SQL. I have never been stuck in that situation, I can 't help much further. – Hornstone

Just thought I would point out the Doctrine really doesn't care of the database constraints actually exist or not. You can add the relations to the Entity classes and keep the database just the way it is. Start with a simple query and work your way up while following the manual. No reason why this should not work. – Nomenclature

Answer 3

You may have to check your annotations or yml file to make sure you have your mapping setup correctly for OneToMany, ManyToMany, and ManyToOne.

In your MemberItems Controller or MemberItems Repository put this:

$em = $this->getDoctrine()->getEntityManager();
$qb = $em->createQueryBuilder()
    ->select('c.name, i.id, i.image, i.name, i.description, m.id, m.quantity, m.value, m.qty_received, m.custom_image, m.custom_name, m.custom_description, u.user1fname, u.user1lname, u.user2fname, u.user2lname')
    ->from('m')
    ->innerJoin('m.memberinfo_id', 'u')
    ->innerJoin('m.item_id', 'i')
    ->innerJoin('i.category_id', 'c')
    ->where(
    ->where('u.id = ?', $slug)
    ->orderBy('c.id', 'ASC')
    ->getQuery();

$memberItems = $qb->getResult();

Sian