0

I have the code below working correctly on phpMyAdmin: 

select testers.department from testers
inner join request_details
on testers.id = request_details.test_id
where request_details.request_id = '12345'

I tried converting it to DQL as below: 

$query =  Doctrine_Query::create()
->select('t.department')
->from('testers t, request_details r')
->innerJoin('t.id r')
->where('t.id = r.tester_id')
->andWhere('r.request_id = ?', 12345);

However , a var_dump() on the variable holding the query result returns NULL.

Himanshu
  • 31,810
  • 31
  • 111
  • 133
watkib
  • 347
  • 3
  • 11
  • 25

1 Answers1

1

Which Doctrine version are you using, because in Doctrine2 you should be using the QueryBuilder class and you should use class names and properties, not table names and fields in DQL. Thus, you should join to the class field name, not the table field name.

innerJoin('t.request_details', 'r') // where request_details is a propery on Tester

Also, you do not need the where that joins them (where(t.id = r.tester_id)), this is managed by Doctrine and will work provided that the entities are properly mapped.

You also do not need the request_details r in the from part, Doctrine will take care of this too.

Also, use class names in from, not table names.

EDIT (forgot the getQuery() before getResults()): In the end you query would look something like this:

$queryBuilder = EntityManager::create(//em options)->createQueryBuilder();
$queryBuilder->select('t.department')
             ->from('Tester', 't')
             ->innerJoin('t.request_details', 'r') // request details is a propery on Tester, that maps to RequestDetails
             ->where('r.request_id = ?1')
             ->setParameter(1, 123);

Doctrine will take care of turning this into SQL and joining the thing. In the end you'll also need to fetch the stuff:

$departments = $queryBuilder->getQuery()->getResult();

EDIT: for Doctrine 1, something like this should work:

$q = Doctrine_Query::create()
     ->select('t.department')
     ->from('Tester t')
     ->innerJoin('t.request_details r') // request details is a propery on Tester, that maps to RequestDetails
     ->where('r.request_id = ?', 123);
$depts = $q->fetchArray();

I am not really familiar with Doctrine 1, so take a look at this for more info: http://docs.doctrine-project.org/projects/doctrine1/en/latest/en/manual/dql-doctrine-query-language.html

Ivan Pintar
  • 1,861
  • 1
  • 15
  • 27
  • Thanks @Pinetree, wait you say "You do need the where that joins them (where(t.id = r.tester_id)), this is managed by Doctrine and will work provided that the entities are properly mapped." What do you mean? – watkib Aug 28 '12 at 09:44
  • Sorry, about that, a million edits and I made that sentence a nonsensical mess. Anyway, if a property in the main entity (the one in ->from()) is properly mapped to a related entity, then doing a join to that property does not require a join condition. That is managed internally by Doctrine. – Ivan Pintar Aug 28 '12 at 10:09
  • About that last comment, could you post you entitiy classes. The Tester and the RequestDetails, and the relationship mapping. – Ivan Pintar Aug 28 '12 at 10:11
  • If I am using `Doctrine 2' shouldn't i get autocomplete on the `CreateQueryBuilder` method, How do I find out my Doctrine version (Doctrine'd already been set up when i took up the project) – watkib Aug 28 '12 at 10:28
  • On posting `Tester` and `RequestDetails` `entity classes`, ' and their relation mapping do you mean `setUP` method where you declare `hasMany`, `hasOne` relationships? – watkib Aug 28 '12 at 10:32
  • 1
    Yes, that's what I meant. Take a look at this http://stackoverflow.com/questions/3771703/how-do-i-find-out-which-version-of-doctrine-i-am-running for version checking, but am beginning to think that you are using Doctrine 1, and I am not really familiar with that. – Ivan Pintar Aug 28 '12 at 11:38
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/15941/discussion-between-watkib-and-pinetree) – watkib Aug 29 '12 at 08:04
  • Chat's no good for me right now, as I can be pulled away any minute, we'll have to do it like this even if SO complains. Have you tried the query in my edit? – Ivan Pintar Aug 29 '12 at 21:18
  • Managed to get the result without doing a Join, but I know a join 'd be more efficient. Will try the query. Checking version says 2.0.3 yet QueryBuilder Method won't work? Maybe I have Doctrine set up wrong. – watkib Aug 30 '12 at 08:10
  • It's really interesting why this isn't working for you. Could you edit your question to show the code of the Tester and RequestDetails classes? And how did you get the result without the join? I mean, how did you filter Testers by request_id if not joining to RequestDetails? – Ivan Pintar Aug 30 '12 at 09:35
  • One more thing, I forgot that you have to do getQuery() on QueryBuilder before you get the results. See the edit in the answer – Ivan Pintar Aug 30 '12 at 09:40