Query Builder SingleValuedAssociationField expected #5825

New Issue

Open

opened 2026-01-22 15:18:58 +01:00 by admin · 6 comments

admin commented 2026-01-22 15:18:58 +01:00

Owner

Copy Link

Originally created by @shubaivan on GitHub (Dec 28, 2017).

I have native sql query with left join when have on with or condition, how to represent it in query builder ?

 $query = "  SELECT  te.id
            FROM    task_executions AS te
            INNER JOIN tasks AS t ON t.id = te.task_id
            LEFT JOIN cost_objects AS co ON co.id = t.cost_object_id
            LEFT JOIN cost_object_managers AS com ON com.cost_object_id = co.id OR com.cost_object_id = co.parent_id

and I need represent it in query builder but in User entity I have ManyToMany relation, without separate table and when I try left join with condition I have error SingleValuedAssociationField expected.

User entity

class User
{
...
/**
 * @ORM\ManyToMany(targetEntity="CostObject", mappedBy="users")
 */
private $costObjects;
}

CostObject entity

class CostObject
{
    /**
 * @var CostObject
 *
 * @ORM\ManyToOne(targetEntity="CostObject", inversedBy="children")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="parent_id", referencedColumnName="id", onDelete="CASCADE")
 * })
 */
private $parent;

    /**
 * @var ArrayCollection
 *
 * @ORM\ManyToMany(targetEntity="User", inversedBy="costObjects")
 * @ORM\JoinTable(name="cost_object_managers",
 *      joinColumns={@ORM\JoinColumn(name="cost_object_id", referencedColumnName="id", onDelete="CASCADE")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id", onDelete="CASCADE")}
 * )
 */
private $users;

and my query builder without condition

        $qb->select('te')
        ->from('AppBundle:TaskExecution', 'te')
        ->innerJoin('te.task', 't')

        ->leftJoin('t.costObject', 'co')
        ->leftJoin('co.users', 'com')

this is $query->getSQL()

SELECT some_name FROM task_executions t0_ INNER JOIN tasks t1_ ON t0_.task_id = t1_.id LEFT JOIN cost_objects c2_ ON t1_.cost_object_id = c2_.id LEFT JOIN cost_object_managers c4_ ON c2_.id = c4_.cost_object_id LEFT JOIN users u3_ ON u3_.id = c4_.user_id ORDER BY t0_.execution_start DESC

and I need change it like in nativbe query but when I add condition to left join I get error

query with condition

        $qb->select('te')
        ->from('AppBundle:TaskExecution', 'te')
        ->innerJoin('te.task', 't')

        ->leftJoin('t.costObject', 'co')
        ->leftJoin(
            'co.users',
            'com',
            Join::WITH,
            $qb->expr()->orX
                (
                    'com.costObjects = co.id',
                    'com.costObjects = co.parent'
                )
            )

and error

[Semantical Error] line 0, col 121 near 'costObjects =': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

I try add IDENTITY like reсomended me in answer

        ->leftJoin('t.costObject', 'co')
        ->leftJoin(
            'co.users',
            'com',
            Join::WITH,
            $qb->expr()->orX
                (
                    'IDENTITY(com.costObjects) = co.id',
                    'IDENTITY(com.costObjects) = co.parent'
                )
            )

but still have error

[Semantical Error] line 0, col 130 near 'costObjects)': Error: Invalid PathExpression. Must be a SingleValuedAssociationField.

Originally created by @shubaivan on GitHub (Dec 28, 2017). I have native sql query with left join when have on with or condition, how to represent it in query builder ? $query = " SELECT te.id FROM task_executions AS te INNER JOIN tasks AS t ON t.id = te.task_id LEFT JOIN cost_objects AS co ON co.id = t.cost_object_id LEFT JOIN cost_object_managers AS com ON com.cost_object_id = co.id OR com.cost_object_id = co.parent_id and I need represent it in query builder but in `User` entity I have ManyToMany relation, without separate table and when I try left join with condition I have error `SingleValuedAssociationField expected.` User entity class User { ... /** * @ORM\ManyToMany(targetEntity="CostObject", mappedBy="users") */ private $costObjects; } CostObject entity class CostObject { /** * @var CostObject * * @ORM\ManyToOne(targetEntity="CostObject", inversedBy="children") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="parent_id", referencedColumnName="id", onDelete="CASCADE") * }) */ private $parent; /** * @var ArrayCollection * * @ORM\ManyToMany(targetEntity="User", inversedBy="costObjects") * @ORM\JoinTable(name="cost_object_managers", * joinColumns={@ORM\JoinColumn(name="cost_object_id", referencedColumnName="id", onDelete="CASCADE")}, * inverseJoinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="id", onDelete="CASCADE")} * ) */ private $users; and my query builder without condition $qb->select('te') ->from('AppBundle:TaskExecution', 'te') ->innerJoin('te.task', 't') ->leftJoin('t.costObject', 'co') ->leftJoin('co.users', 'com') this is `$query->getSQL()` SELECT some_name FROM task_executions t0_ INNER JOIN tasks t1_ ON t0_.task_id = t1_.id LEFT JOIN cost_objects c2_ ON t1_.cost_object_id = c2_.id LEFT JOIN cost_object_managers c4_ ON c2_.id = c4_.cost_object_id LEFT JOIN users u3_ ON u3_.id = c4_.user_id ORDER BY t0_.execution_start DESC and I need change it like in nativbe query but when I add condition to left join I get error query with condition $qb->select('te') ->from('AppBundle:TaskExecution', 'te') ->innerJoin('te.task', 't') ->leftJoin('t.costObject', 'co') ->leftJoin( 'co.users', 'com', Join::WITH, $qb->expr()->orX ( 'com.costObjects = co.id', 'com.costObjects = co.parent' ) ) and error [Semantical Error] line 0, col 121 near 'costObjects =': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected. I try add `IDENTITY` like reсomended me in answer ->leftJoin('t.costObject', 'co') ->leftJoin( 'co.users', 'com', Join::WITH, $qb->expr()->orX ( 'IDENTITY(com.costObjects) = co.id', 'IDENTITY(com.costObjects) = co.parent' ) ) but still have error [Semantical Error] line 0, col 130 near 'costObjects)': Error: Invalid PathExpression. Must be a SingleValuedAssociationField.

admin commented 2026-01-22 15:19:00 +01:00

Author

Owner

Copy Link

@Majkl578 commented on GitHub (Dec 28, 2017):

Since com.costObjects seems to be to-many collection, you should use MEMBER OF. This should work:

co MEMBER OF com.costObjects OR co.parent MEMBER OF com.costObjects

@Majkl578 commented on GitHub (Dec 28, 2017): Since `com.costObjects` seems to be to-many collection, you should use `MEMBER OF`. This should work: ``` co MEMBER OF com.costObjects OR co.parent MEMBER OF com.costObjects ```

admin commented 2026-01-22 15:19:00 +01:00

Author

Owner

Copy Link

@shubaivan commented on GitHub (Dec 28, 2017):

@Majkl578 how should I apply this approach to my query ? How should look orX in my leftJoin ?

@shubaivan commented on GitHub (Dec 28, 2017): @Majkl578 how should I apply this approach to my query ? How should look `orX` in my `leftJoin` ?

admin commented 2026-01-22 15:19:00 +01:00

Author

Owner

Copy Link

@Majkl578 commented on GitHub (Dec 28, 2017):

It's orX(), so:

$qb->expr()->orX(
    'co MEMBER OF com.costObjects',
    'co.parent MEMBER OF com.costObjects'
)

@Majkl578 commented on GitHub (Dec 28, 2017): It's orX(), so: ``` $qb->expr()->orX( 'co MEMBER OF com.costObjects', 'co.parent MEMBER OF com.costObjects' ) ```

admin commented 2026-01-22 15:19:01 +01:00

Author

Owner

Copy Link

@shubaivan commented on GitHub (Dec 28, 2017):

@Majkl578
I debuged it and got sql, this looks

SELECT name FROM task_executions t0_ INNER JOIN tasks t1_ ON t0_.task_id = t1_.id LEFT JOIN cost_objects c2_ ON t1_.cost_object_id = c2_.id LEFT JOIN cost_object_managers c4_ ON c2_.id = c4_.cost_object_id 

exactly:

LEFT JOIN users u3_ ON u3_.id = c4_.user_id AND (EXISTS (SELECT 1 FROM cost_object_managers c5_ INNER JOIN cost_objects c6_ ON c5_.cost_object_id = c6_.id WHERE c5_.user_id = u3_.id AND c6_.id IN (c2_.id)) OR EXISTS (SELECT 1 FROM cost_object_managers c5_ INNER JOIN cost_objects c6_ ON c5_.cost_object_id = c6_.id WHERE c5_.user_id = u3_.id AND c6_.id IN (c2_.parent_id)))

do you yhink this is equals to ?

LEFT JOIN cost_object_managers AS com ON com.cost_object_id = co.id OR com.cost_object_id = co.parent_id

@shubaivan commented on GitHub (Dec 28, 2017): @Majkl578 I debuged it and got sql, this looks SELECT name FROM task_executions t0_ INNER JOIN tasks t1_ ON t0_.task_id = t1_.id LEFT JOIN cost_objects c2_ ON t1_.cost_object_id = c2_.id LEFT JOIN cost_object_managers c4_ ON c2_.id = c4_.cost_object_id exactly: LEFT JOIN users u3_ ON u3_.id = c4_.user_id AND (EXISTS (SELECT 1 FROM cost_object_managers c5_ INNER JOIN cost_objects c6_ ON c5_.cost_object_id = c6_.id WHERE c5_.user_id = u3_.id AND c6_.id IN (c2_.id)) OR EXISTS (SELECT 1 FROM cost_object_managers c5_ INNER JOIN cost_objects c6_ ON c5_.cost_object_id = c6_.id WHERE c5_.user_id = u3_.id AND c6_.id IN (c2_.parent_id))) do you yhink this is equals to ? LEFT JOIN cost_object_managers AS com ON com.cost_object_id = co.id OR com.cost_object_id = co.parent_id

admin commented 2026-01-22 15:19:02 +01:00

Author

Owner

Copy Link

@shubaivan commented on GitHub (Dec 29, 2017):

@Majkl578
Because WITH this is additional condition. I said about change ON condition for leftJoin this is different case
Now sql look like

LEFT JOIN users u3_ ON u3_.id = c4_.user_id AND (EXISTS (SELECT 1 FROM cost_object_managers c5_ INNER JOIN cost_objects c6_ ON c5_.cost_object_id = c6_.id WHERE c5_.user_id = u3_.id AND c6_.id IN (c2_.id)) OR EXISTS (SELECT 1 FROM cost_object_managers c5_ INNER JOIN cost_objects c6_ ON c5_.cost_object_id = c6_.id WHERE c5_.user_id = u3_.id AND c6_.id IN (c2_.parent_id)))

I want change this relation users u3_ ON u3_.id = c4_.user_id

by

LEFT JOIN cost_object_managers AS com ON com.cost_object_id = co.id OR com.cost_object_id = co.parent_id

@shubaivan commented on GitHub (Dec 29, 2017): @Majkl578 Because `WITH` this is additional condition. I said about change `ON` condition for `leftJoin` this is different case Now sql look like ``` LEFT JOIN users u3_ ON u3_.id = c4_.user_id AND (EXISTS (SELECT 1 FROM cost_object_managers c5_ INNER JOIN cost_objects c6_ ON c5_.cost_object_id = c6_.id WHERE c5_.user_id = u3_.id AND c6_.id IN (c2_.id)) OR EXISTS (SELECT 1 FROM cost_object_managers c5_ INNER JOIN cost_objects c6_ ON c5_.cost_object_id = c6_.id WHERE c5_.user_id = u3_.id AND c6_.id IN (c2_.parent_id))) ``` I want change this relation `users u3_ ON u3_.id = c4_.user_id` by ``` LEFT JOIN cost_object_managers AS com ON com.cost_object_id = co.id OR com.cost_object_id = co.parent_id ```

admin commented 2026-01-22 15:19:02 +01:00

Author

Owner

Copy Link

@shubaivan commented on GitHub (Dec 29, 2017):

@Majkl578
But when I changed WITH to ON

            ->leftJoin(
                'co.users',
                'com',
                Join::ON,
                $qb->expr()->orX(
                    'co MEMBER OF com.costObjects',
                    'co.parent MEMBER OF com.costObjects'
                )
            )

I have error

[Syntax Error] line 0, col 112: Error: Expected end of string, got 'ON'

@shubaivan commented on GitHub (Dec 29, 2017): @Majkl578 But when I changed `WITH` to `ON` ``` ->leftJoin( 'co.users', 'com', Join::ON, $qb->expr()->orX( 'co MEMBER OF com.costObjects', 'co.parent MEMBER OF com.costObjects' ) ) ``` I have error `[Syntax Error] line 0, col 112: Error: Expected end of string, got 'ON'`

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

3.6.x

2.20.x

dependabot/github_actions/2.20.x/doctrine/dot-github/dot-github/workflows/composer-lint.yml-14.0.0

3.7.x

4.0.x

2.21.x

old-prototype-3.x

3.6.2

3.6.1

3.6.0

2.20.9

3.5.8

3.5.7

3.5.6

3.5.5

2.20.8

3.5.4

3.5.3

2.20.7

3.5.2

2.20.6

3.5.1

3.5.0

3.4.4

3.4.3

3.4.2

2.20.5

3.4.1

3.4.0

3.3.4

2.20.4

3.3.3

2.20.3

3.3.2

2.20.2

3.3.1

2.20.1

3.3.0

3.2.3

2.20.0

2.19.8

3.2.2

2.19.7

3.2.1

2.19.6

3.2.0

3.1.4

3.1.3

2.19.5

3.1.2

2.19.4

3.1.1

2.19.3

2.19.2

2.19.1

3.1.0

2.19.0

3.0.3

2.18.3

3.0.2

2.18.2

3.0.1

2.18.1

3.0.0

2.18.0

2.17.5

3.0.0-RC1

2.17.4

2.17.3

2.17.2

2.17.1

3.0.0-beta2

2.17.0

2.16.3

3.0.0-beta1

2.16.2

2.16.1

2.16.0

2.15.5

2.15.4

2.15.3

2.15.2

2.15.1

2.15.0

2.14.3

2.14.2

2.14.1

2.14.0

2.13.5

2.13.4

2.13.3

2.13.2

2.13.1

2.13.0

2.12.4

2.12.3

2.12.2

2.12.1

2.12.0

2.11.3

2.11.2

2.11.1

2.11.0

2.10.5

2.10.4

2.10.3

2.10.2

2.10.1

2.10.0

2.9.6

2.9.5

2.9.4

2.9.3

2.9.2

2.9.1

2.9.0

2.8.5

2.8.4

2.8.3

2.8.2

2.8.1

2.8.0

2.7.5

2.7.4

v2.7.3

v2.7.2

v2.7.1

v2.7.0

v2.6.6

v2.6.5

v2.6.4

v2.6.3

v2.6.2

v2.6.1

v2.6.0

v2.5.14

v2.5.13

v2.5.12

v2.5.11

v2.5.10

v2.5.9

v2.5.8

v2.5.7

v2.5.6

v2.5.5

v2.5.4

v2.5.3

v2.5.2

v2.4.8

v2.5.1

v2.5.0

v2.5.0-RC2

v2.5.0-RC1

v2.5.0-beta1

v2.5.0-alpha2

v2.5.0-alpha1

v2.4.7

v2.4.6

v2.4.5

v2.4.4

v2.4.3

v2.3.6

v2.4.2

2.3.5

v2.4.1

v2.4.0

2.4.0-RC2

2.4.0-RC1

2.4.0-BETA2

2.3.4

2.3.3

2.4.0-BETA1

2.3.2

2.3.1

2.3.0

2.3.0-RC4

2.3.0-RC3

2.3.0-RC2

2.2.3

2.3.0-RC1

2.3.0-BETA1

2.1.7

2.2.2

2.2.1

2.1.6

2.2.0

2.2.0-RC1

2.2.0-BETA2

2.2.0-BETA1

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.0.7

2.1.0

2.1.0RC3

2.1.0RC2

2.1.0RC1

2.0.6

2.1.0BETA1

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

2.0.0RC2

2.0.0RC1

2.0.0-BETA4

2.0.0-BETA3

2.0.0-BETA2

2.0.0-BETA1

Labels

Clear labels

BC Break

Bug

Can't Fix

Deprecation

Documentation

DQL

Duplicate

Failing Test

Hacktoberfest

Improvement

Improvement

Incomplete

Invalid

Missing Tests

New Feature

PR Created

pull-request

Mirrored from GitHub Pull Request

Question

Regression

Status: Waiting feedback

Waiting feedback

WIP

Won't Fix

No Label

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

admin

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5825