How do you join two tables in Doctrine2 using Query Builder if table relationships are not configured?

Question

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

The problem is that all of my annotated objects do not have relationship settings between tables. At some point I will pay attention to this, but at the same time I need to try and get around this.

Basically, I have two tables: the product table and the product_description table. The product table stores the basic information, and then I have the product_description table, which stores the description information. Due to language, a product may have one or more descriptions.

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

I am currently 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 someone point me in the right direction? I am going to do it differently if there is an alternative.

Answer 1

Not having a specific relationship, I don't think you can join tables. This is due to the fact that when using DQL, you request an object, not a table, and if the objects do not know about each other, you cannot join them.

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

A NativeQuery lets you execute your own SELECT SQL statements, displaying the results according to your specifications. A specification that describes how a SQL result set maps to a Doctrine result is provided by ResultSetMapping. It describes how each column of a database result should be mapped to Doctrine in terms of a graph of objects. This allows you to map arbitrary SQL codes to objects such as SQL or stored procedures with an optimized vendor.

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

Hope this helps.