CODEWITHSUNDEEP
symfonydoctrine-orm
copndz
copndz

Reputation: 1104

Need some help to translate query from createQuery() to queryBuilder()

I'm used to create query with createQuery() instead of queryBuilder, but I need to translate a query in a queryBuilder one to use in a EntityType to generate a form. Here is my query :

SELECT p FROM D2ECoreBundle:Player p,
D2ECoreBundle:LadderMatch lm,
D2ECoreBundle:PlayerComposition hpc
JOIN hpc.players hp,
D2ECoreBundle:PlayerComposition fpc
JOIN fpc.players fp
WHERE (lm.homePlayerComposition = hpc AND hp = p)
OR (lm.foreignPlayerComposition = fpc AND fp = p)

and here is what i thought it would be in queryBuilder but doesnt work :

$qb->select('p')
->from('D2ECoreBundle:Player', 'p')
->from('D2ECoreBundle:LadderMatch', 'lm')
->from('D2ECoreBundle:PlayerComposition', 'hpc')
->join('hpc.players', 'hp')
->from('D2ECoreBundle:PlayerComposition', 'fpc')
->join('fpc.players', 'fp')
->where('lm.homePlayerComposition = hpc' AND 'hp = p')
->orwhere('lm.foreignPlayerComposition = fpc' AND 'fp = p');

Does anyone know what I should change to have it working? Thanx for the answers!

Upvotes: 1

Views: 1005

Answers (2)

copndz
copndz

Reputation: 1104

I eventually managed to do this. Here is my code :

$qb = $this->createQueryBuilder('lm')
    ->select('p')
    ->from('D2ECoreBundle:Player', 'p')
    ->join('lm.homePlayerComposition', 'hpc')
    ->join('hpc.players', 'hp')
    ->join('lm.foreignPlayerComposition', 'fpc')
    ->join('fpc.players', 'fp')
    ->where('lm.homePlayerComposition = hpc AND hp = p')
    ->orwhere('lm.foreignPlayerComposition = fpc AND fp = p');
return $qb;

And a very important thing, since I want to select player, but starting by the LadderMatch entity, I need to put this in LadderMatchRepository.php and not PlayerRepository.php like I did, because it affects the generat

Upvotes: 0

Jovan Perovic
Jovan Perovic

Reputation: 20201

When you query the DB with:

SELECT * FROM TableA T1, TableB T2  ....

in many implementations is the same as:

SELECT * FROM TableA T1 JOIN TableB T2 ON ... // this is INNER JOIN

So, instead of using from multiple times, do joins:

$qb->select('p')
->from('D2ECoreBundle:Player', 'p')
->join('p.ladderMatches', 'lm')
->join('p.playerComposition', 'hpc')
->join('hpc.players', 'hp')
->join('hp.playerCompossition', 'fpc')
->join('fpc.players', 'fp')
->where('lm.homePlayerComposition = hpc' AND 'hp = p')
->orwhere('lm.foreignPlayerComposition = fpc' AND 'fp = p');

Not sure if I got the relation correct but you get the point...

Upvotes: 1

Related Questions