4

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.

Adam Stacey
  • 2,833
  • 4
  • 26
  • 38

1 Answers1

8

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.

Steven Mercatante
  • 24,757
  • 9
  • 65
  • 109
  • That looks great Arms, thanks for that. One question on it: do you know if there is a way to do a table.* in the ResultSetMapping, so that it take the whole object rather than set fields. The reason I ask is that I have extended some of the object classes and would like to utilise them. – Adam Stacey Mar 28 '12 at 09:53
  • I haven't tried this myself, so I'm not sure if `table.*` will work - sorry. – Steven Mercatante Mar 28 '12 at 13:08
  • 1
    Note that you can now (as of Doctrine 2.4) do arbitrary joins in DQL using syntax like `SELECT u FROM User u JOIN Blacklist b WITH u.email = b.email`. I'm not sure if you can use the QueryBuilder though... (which is how I stumbled across this thread). – caponica Oct 17 '14 at 00:17
  • @caponica Just tried it with querybuilder and it would appear not! :) Time for some raw SQL methinks... – Darragh Enright Jul 25 '16 at 14:29
  • You can still use DQL - no need to use raw SQL unless you really want to. – caponica Jul 26 '16 at 13:59
  • By the way, you can do it with the querybuilder too: `$qb->join('Your\Class', 'yc', Join::WITH, 'foo.yc_id = yc.id');` You just need to do some work to properly hydrate the results returned. – caponica Oct 08 '16 at 22:58
  • http://docs.doctrine-project.org/projects/doctrine-orm/en/2.0.x/reference/native-sql.html Page not found – pedram shabani Jul 07 '19 at 09:04