Symfony 4, ManyToOne entities relation, get 'parents' only if at least one child contains a specific value, Error "Expected end of string, got 'ON'"

Question

From Symfony 4, I have a ManyToOne relation between entities A and B (several B for only one A). I want select all id's A rows only if an 'child' B have the value 1 in its specificfield.

The table 'A' :

---
id 
---
0
1
2

The table 'B' :

----------------------------------
id    parent_id    specificfield 
----------------------------------
0     1            1
1     1            0
2     2            0

Result expected :

---
id 
---
1 
// because, only the A row with the id 1 have at least one 'child' in the table B with the specificfield set to 1

I tried build a query like this :

$res = $this->em->createQueryBuilder()
    ->select('A.id')
    ->from(Parent::class,'A')
    ->innerJoin(Child::class,'B',Join::ON,"A.id = B.parent_id")
    ->where('B.specificfield = 1')
    ->distinct()
    ->getQuery()
    ->getResult();

But I get the error : "Expected end of string, got 'ON'" .

The sql query equivalent works in my phpmyadmin ..

SELECT DISTINCT A.id
FROM parent A
INNER JOIN child B ON A.id = B.parent_id
WHERE e.specificfield = 1

I don't see where is my mistake in my dql query ..

Where you use the query builder are you importing `use Doctrine\ORM\Query\Expr\Join;` ? — dlondero, Jan 20 '18 at 08:39

score 2 · Answer 1 · answered Jan 20 '18 at 10:18

When using the ORM you are working on the entities not the tables directly. This requires you to think a bit differently, especially in regards to assocations. You are joining the properties of the entities, not between the tables/objects.

The query therefore look like this:

$builder
    ->select('a.id')
    ->from(A::class, 'a')
    ->innerJoin('a.children', 'b')
    ->where('b.active = 1')
    ->getQuery()
    ->getResult();

So you just specify on which property you want to join and because your mapping already specified which entity belongs there it should work. For reference this is what my entities look like:

/**
 * @ORM\Entity()
 */
class A
{
    /**
     * @ORM\Id()
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\B", mappedBy="parent")
     */
    private $children;

    ...
}
/**
 * @ORM\Entity()
 */
class B
{
    /**
     * @ORM\Id()
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\A", inversedBy="children")
     * @ORM\JoinColumn(nullable=false)
     */
    private $parent;

    /**
     * @ORM\Column(type="boolean", options={"default": false})
     */
    private $active;

    ...
}

edit for clarification: When you join B::class instead of a.children. Then you just tell the query builder that you want to fetch multiple entities at once unrelatedly. In case you want to quickly collect all As and active Bs with one query, but then you also have to select b.id.

score 1 · Accepted Answer · answered Jan 20 '18 at 10:12

1

I think you need to use "with" instead of "on" in the join...

answered Jan 20 '18 at 10:12

Mohammad ZeinEddin

1,608
14
17

Symfony 4, ManyToOne entities relation, get 'parents' only if at least one child contains a specific value, Error "Expected end of string, got 'ON'"

2 Answers2