CODEWITHSUNDEEP
phpsymfonydoctrine-ormquery-builder
The50
The50

Reputation: 1166

Symfony Doctrine QueryBuilder add where clause on Join

I have a query builder with the following select:

$starterRepository = $this->getDoctrine()
                          ->getManager()
                          ->getRepository('CatalogBundle:Starter');

$query = $starterRepository->createQueryBuilder('s')
                           ->where('s.active = 1')
                           ->orderBy('s.voltage, s.power, s.serial');

It selects the table "starters", but inside Starter.php I have an association "references" like this:

 /**
 * @var ArrayCollection
 *
 * @ORM\ManyToMany(targetEntity="StarterReference", inversedBy="starters")
 * @ORM\JoinTable(name="starters_references")
 */
protected $references;

So inside my query results I have both "starters" and "starters_references" tables. 1 starter has many starter references. Now the problem is that I need to select not all of the starter references, but only the ones which has some value in column named "ref_usage".

So I need to write where clause in my query, I am trying this:

->where('reference.ref_usage = 1')

But this way I get only one "starter" item with all the references it has included. And I need all the starter items, but with only the references with ref_usage 1.

Any ideas?

Here is the full files and functions I am using.

Controller function: http://pastebin.com/0tTEcQbn

Entity "Starter.php": http://pastebin.com/BFLpKtec

Entity "StarterReference.php": http://pastebin.com/Kr9pEMEW

EDIT: This is the query I get if I use:

->where('reference.ref_usage = 1')

SELECT COUNT(*) AS dctrn_count FROM (SELECT DISTINCT id0 FROM (SELECT s0_.id AS id0, s0_.serial AS serial1, s0_.voltage AS voltage2, s0_.power AS power3, s0_.rotation AS rotation4, s0_.teeth AS teeth5, s0_.module AS module6, s0_.b_terminal AS b_terminal7, s0_.comment AS comment8, s0_.commenten AS commenten9, s0_.commentru AS commentru10, s0_.commentpl AS commentpl11, s0_.commentde AS commentde12, s0_.commentes AS commentes13, s0_.type AS type14, s0_.adaptation AS adaptation15, s0_.alternative_product_1 AS alternative_product_116, s0_.alternative_product_2 AS alternative_product_217, s0_.alternative_product_3 AS alternative_product_318, s0_.alternative_product_4 AS alternative_product_419, s0_.active AS active20 FROM starters s0_ INNER JOIN starters_references s2_ ON s0_.id = s2_.starter_id INNER JOIN starter_reference s1_ ON s1_.id = s2_.starterreference_id WHERE s0_.active = 1 AND s1_.ref_usage = 1 ORDER BY s0_.voltage ASC, s0_.power ASC, s0_.serial ASC) dctrn_result) dctrn_table

As you can see it adds ref_usage = 1 to where clause. But the problem is that I don't need it here, I only need to check ref_usage when I inner join my references.

Upvotes: 2

Views: 3126

Answers (1)

Francesco Donzello
Francesco Donzello

Reputation: 773

You should join the references in your query and then add the where clause.

$query = $starterRepository->createQueryBuilder('s')
                       ->join('s.references', 'reference')
                       ->where('s.active = 1')
                       ->andwhere('reference.ref_usage = 1')
                       ->orderBy('s.voltage, s.power, s.serial');

Upvotes: 4

Related Questions