How do you join two tables in Doctrine2 using Quer

Question

I am currently using Symfony2 and Doctrine2 and am trying to join two tables together using query builder.

The problem I have is that all my annotated entities do not have the table relationships setup. I will at some point address this, but in the mean time I need to try and work round this.

Basically I have two tables: a product table and a product_description table. The product table stores the basic information and then I have a product_description table that stores the description information. A product can have one or more descriptions due to language.

I want to use query builder, so I can retrieve both the product and product_description results as objects.

At the moment I am using the following code:

// Get the query builder
$qb = $em->createQueryBuilder();

// Build the query
$qb->select(array('p, pd'));
$qb->from('MyCompanyMyBundle:Product', 'p');
$qb->innerJoin('pd', 'MyCompanyMyBundle:ProductDescription', 'pd', 'ON', $qb->expr()->eq('p.id', 'pd.departmentId'));
$query = $qb->getQuery();
$products = $query->getResult();

This gives me the following error:

[Syntax Error] line 0, col 71: Error: Expected Doctrine\ORM\Query\Lexer::T_DOT, got 'MyCompanyMyBundle:ProductDescription'

Can anyone point me in the right direction? I am up for doing it differently if there is an alternative.

Answer 1

Without having the relationships defined, I don't think you can join the tables. This is because when you use DQL, you're querying an object rather than a table, and if the objects are unaware of each other, you can't join them.

I think you should look at using a NativeQuery. From the docs:

A NativeQuery lets you execute native SELECT SQL statements, mapping the results according to your specifications. Such a specification that describes how an SQL result set is mapped to a Doctrine result is represented by a ResultSetMapping. It describes how each column of the database result should be mapped by Doctrine in terms of the object graph. This allows you to map arbitrary SQL code to objects, such as highly vendor-optimized SQL or stored-procedures.

Basically, you write raw SQL, but tell Doctrine how to map the results to your existing entities.

Hope this helps.