querybuilder join query in Doctrine2 for Symfony2

Question

Could somebody convert this query for me in querybuilder?

SELECT m.id,n.unitid 
  FROM mappaths m JOIN unitids n on (m.id=n.id) where n.databaseid=1

I am using this query but it gives me all the values of mm.unitid, while my requirement is to get only one value that is defined by test=1 variable

    $query=$qb->select('mm.unitid')
      ->from('ApiMapBundle:Mappaths','m')
      ->from('ApiMapBundle:Unitids','mm')
     // ->leftJoin('m','u')
       ->leftJoin('m.refUnitids1','u','WITH','m.id = u')
     //  ->leftJoin('m.refUnitids2','v')   
      ->where('m.id=:test')
      ->setParameter('test',1)
      ->getQuery()->getResult();

Why are you using leftJoin in queryBuilder?

Isky
– Isky

2016-01-07 13:08:52 +00:00
Commented Jan 7, 2016 at 13:08 — Isky
– Isky, Commented Jan 7, 2016 at 13:08

Tomasz Madeyski · Accepted Answer · 2016-01-07 14:12:20Z

1

Try following:

$query = $qb->select('mm.unitid')
      ->from('ApiMapBundle:Mappaths','m')
      ->innerJoin('m.refUnitids1','mm','WITH','m.id = mm.FIELD') //you need to specify on which field of mm join should be done
      ->where('m.id=:test')
      ->setParameter('test',1)
      ->getQuery()
      ->getResult();

You need to specify field of Unitids which should be used to join to Mappaths. The best way would be to define this relation in Entity definition, then you can use just ->innerJoin('m.refUnitids1','mm') without additional join parameters.

Also, in this case, it is better to use innerJoin instead of leftJoin

answered Jan 7, 2016 at 14:12

Tomasz Madeyski

10.9k3 gold badges54 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zahid Over a year ago

Tomasz thanks for your response. one more question if we would have two foreign keys then how would be the query in this case for example the query like this in sql= select * FROM mappaths m JOIN unitids n on( m.ref_unitids2 = n.id or m.ref_unitids1 = n.id) where m.id=2

zahid Over a year ago

i already define these forign-- primary relationship in meta files. basically this mappaths table (class) contain two (many to one) relationships to unitds table. or class..

Collectives™ on Stack Overflow

querybuilder join query in Doctrine2 for Symfony2

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related