symfony querybuilder query need to write

Question

I have the following tables in mysql with given relationships and same has been define in the symfony2. I want to achieve the following query which I wrote it down below. can some one help me how can I write it in querybuilder?

 unitids(id,unitid,databaseid)

 Mappaths(id,ref_unitid2(forigen key for unitids id)  ,ref_unitid1 (forigen key for unitids id),nomal value)

 traveltime(id,ref_map_path(forigen key for mappaths id),  traveltime,noobjs,ondate)

my mysql query is like this :

SELECT t.ID,t.ondate, un.databaseid as source1, 
 un1.databaseid as  desitnation, t.traveltime, t.noobjs 
 FROM test.traveltime t
 left join test.mappaths p on t.ref_map_path = p.ID
 left join test.unitids un on (p.ref_unitids1 = un.id )
 left join test.unitids un1 on (p.ref_unitids2= un1.id)
 where un.databaseid=50 and un1.databaseid =1 limit 1;

which give me each one row of source and destination of and objects etc like this : in symfony2 when i run this query

 $query = $em->createQueryBuilder();
      $results = $query->select('un.databaseid,un1.databaseid')
      ->from('ApiMapBundle:Traveltime', 't')
            ->leftJoin('t.refMapPath', 'p')
            ->leftJoin('p.refUnitids2', 'un')
            ->leftJoin('p.refUnitids1', 'un1')
            ->where('un.databaseid = :bdatabaseid1')
            ->setParameter('bdatabaseid1', 2)
            ->andwhere('un1.databaseid = :bdatabaseid2')
             ->setParameter('bdatabaseid2',1)
            //->setMaxResults(1)
            ->getQuery()
            ->getResult();

it give me output like

 Array ( [0] => Array ( [databaseid] => 1 ) [1] => Array ( [databaseid] => 1 ))

but instead it should give me

  Array ( [0] => Array ( [databaseid] => 1 ) [1] => Array ( [databaseid] => 2 ))

How can i achieved this above output?????

You means the answer of the query.. It is just I mentioned the output which give me by the above MySQL query... but i think it does not matter. The important is how I can right left join with same two tables multiple times with different columns has it is two foreign key with one table.... — zahid, Feb 05 '16 at 09:49

Jimish Gamit · Accepted Answer · 2016-02-05T12:36:14.107

1

Assumed that you are writing code in Repository and you also defined relations in your Entities

$em = $this->getEntityManager();
$query = $em->getRepository('BUNDLE:traveltime')->createQueryBuilder('t');
$query->select('t.Id,..')
   ->leftJoin('t.ref_map_path','p')
   ->leftJoin('p.ref_unitids1','un')
   ->leftJoin('p.ref_unitids2','un1')
   ->where('un.databaseid = :bdatabaseid')
   ->setParameter('bdatabaseid',1)
   ->orWhere('un1.databaseid = :bdatabaseid1')
   ->setParameter('bdatabaseid1',2);

edited Feb 05 '16 at 12:36

answered Feb 05 '16 at 09:53

Jimish Gamit

1,014
5
15

let me to try it. i try some thing like that before but it did not work. You write the where condition above. – zahid Feb 05 '16 at 09:55
when i run this $query = $em->createQueryBuilder(); $results = $query->select('un1.databaseid,un.databaseid') ->from('ApiMapBundle:Traveltime', 't') ->leftJoin('t.refMapPath', 'p') ->innerJoin('p.refUnitids2', 'un') ->innerJoin('p.refUnitids1', 'un1') ->where('un.databaseid = :bdatabaseid1') ->andwhere('un1.databaseid = :bdatabaseid2') ->setParameter('bdatabaseid1', 1) ->setParameter('bdatabaseid2',2) ->getQuery() ->getResult() when i run it it give me this out put it give me output like "Array ( [0] => Array ( [databaseid] => 1 ) [1] => Array ( [databaseid] => 1 ) )".it should give me 1 and 2 – zahid Feb 05 '16 at 11:18
use `orWhere` instead of `andWhere` – Jimish Gamit Feb 05 '16 at 11:47
$query = $em->createQueryBuilder(); $results = $query->select('un.databaseid,un1.databaseid') ->from('ApiMapBundle:Traveltime', 't') ->leftJoin('t.refMapPath', 'p') ->leftJoin('p.refUnitids2', 'un') ->leftJoin('p.refUnitids1', 'un1') ->where('un.databaseid = :bdatabaseid1') ->setParameter('bdatabaseid1', 1) ->orwhere('un1.databaseid = :bdatabaseid2') ->setParameter('bdatabaseid2',2) ->getQuery() ->getResult(); it give me same output Array ( [0] => Array ( [databaseid] => 2 ) [1] => Array ( [databaseid] => 2 ) [2] => Array ( [databaseid] => 2 ) ) – zahid Feb 05 '16 at 12:36
can you please post sample data? – Jimish Gamit Feb 05 '16 at 12:38
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/102704/discussion-between-zahid-and-jimish-gamit). – zahid Feb 05 '16 at 12:39

symfony querybuilder query need to write

1 Answers1