doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-23 22:42:18 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

Query builder is using wrong id with MEMBER OF #5401

New Issue
Open
opened 2026-01-22 15:06:40 +01:00 by admin · 12 comments
admin commented 2026-01-22 15:06:40 +01:00
Owner
Copy Link

Originally created by @bettinz on GitHub (Feb 1, 2017).

Hello, I've a problem with forms in symfony, and I can't understand if it's related to doctrine or symfony.
I've also opened an issue with symfony in order to understand where is the problem.
I have 2 entities: user and stable. I've a table called user_for_stable with user_id and stable_id

So I've created this simple form called "CowType":

->add('stable', EntityType::class, array(
                'class'=>'AppBundle\Entity\Stable',
                'multiple'=>false,
                'expanded'=>false,
                'choice_label'=>'stableName',
                'query_builder'=> function (EntityRepository $er) use ($options) {
                    return $er->createQueryBuilder('u')
                        ->where(':userobj MEMBER OF u.users')
                        ->setParameter("userobj", $options['userObj'] );
                },
            ))

In the controller I've:

$form = $this->createForm(CowType::class, $cow, array('userObj'=>$this->getUser()));

But the generated query is wrong:

SELECT s0_.id AS id_0, s0_.address AS address_1, s0_.city AS city_2, s0_.zip AS zip_3, s0_.state AS state_4, s0_.stable_name AS stable_name_5 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.id = ?)

it should be

SELECT s0_.id AS id_0, s0_.address AS address_1, s0_.city AS city_2, s0_.zip AS zip_3, s0_.state AS state_4, s0_.stable_name AS stable_name_5 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.user_id = ?)

in stable entity I've:

     /**
     * @var
     * @ORM\OneToMany(targetEntity="AppBundle\Entity\UserForStable", mappedBy="stable")
     */

    private $users;

This code is working as expected:

$q= $er->createQueryBuilder('u');
                    $q->select('s')
                        ->from('AppBundle:Stable', 's')
                        ->leftJoin('s.users', 'usersInStable', 'WHERE', 'usersInStable.user = :userObj' )
                        ->setParameter('userObj', $options['userObj']);

the generated sql is

SELECT s0_.id AS id_0, s0_.address AS address_1, s0_.city AS city_2, s0_.zip AS zip_3, s0_.state AS state_4, s0_.stable_name AS stable_name_5, s0_.tav AS tav_6, s0_.kg_for_notch AS kg_for_notch_7, s0_.number_of_meal AS number_of_meal_8 FROM stable s1_, stable s0_ LEFT JOIN user_for_stable u2_ ON s0_.id = u2_.stable_id WHERE u2_.user_id = ?

Can you help me? Thanks

Originally created by @bettinz on GitHub (Feb 1, 2017). Hello, I've a problem with forms in symfony, and I can't understand if it's related to doctrine or symfony. I've also opened an issue with symfony in order to understand where is the problem. I have 2 entities: user and stable. I've a table called user_for_stable with user_id and stable_id So I've created this simple form called "CowType": ```php ->add('stable', EntityType::class, array( 'class'=>'AppBundle\Entity\Stable', 'multiple'=>false, 'expanded'=>false, 'choice_label'=>'stableName', 'query_builder'=> function (EntityRepository $er) use ($options) { return $er->createQueryBuilder('u') ->where(':userobj MEMBER OF u.users') ->setParameter("userobj", $options['userObj'] ); }, )) ``` In the controller I've: ```php $form = $this->createForm(CowType::class, $cow, array('userObj'=>$this->getUser())); ``` But the generated query is wrong: ```sql SELECT s0_.id AS id_0, s0_.address AS address_1, s0_.city AS city_2, s0_.zip AS zip_3, s0_.state AS state_4, s0_.stable_name AS stable_name_5 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.id = ?) ``` it should be ```sql SELECT s0_.id AS id_0, s0_.address AS address_1, s0_.city AS city_2, s0_.zip AS zip_3, s0_.state AS state_4, s0_.stable_name AS stable_name_5 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.user_id = ?) ``` in stable entity I've: ```php /** * @var * @ORM\OneToMany(targetEntity="AppBundle\Entity\UserForStable", mappedBy="stable") */ private $users; ``` This code is working as expected: ```php $q= $er->createQueryBuilder('u'); $q->select('s') ->from('AppBundle:Stable', 's') ->leftJoin('s.users', 'usersInStable', 'WHERE', 'usersInStable.user = :userObj' ) ->setParameter('userObj', $options['userObj']); ``` the generated sql is ```sql SELECT s0_.id AS id_0, s0_.address AS address_1, s0_.city AS city_2, s0_.zip AS zip_3, s0_.state AS state_4, s0_.stable_name AS stable_name_5, s0_.tav AS tav_6, s0_.kg_for_notch AS kg_for_notch_7, s0_.number_of_meal AS number_of_meal_8 FROM stable s1_, stable s0_ LEFT JOIN user_for_stable u2_ ON s0_.id = u2_.stable_id WHERE u2_.user_id = ? ``` Can you help me? Thanks
admin added the BugMissing Tests labels 2026-01-22 15:06:40 +01:00
admin commented 2026-01-22 15:06:44 +01:00
Author
Owner
Copy Link

@lcobucci commented on GitHub (Feb 1, 2017):

@bettinz can you reproduce this in a test case? You can see examples here: c816d375e8/tests/Doctrine/Tests/ORM/Functional/Ticket

@lcobucci commented on GitHub (Feb 1, 2017): @bettinz can you reproduce this in a test case? You can see examples here: https://github.com/doctrine/doctrine2/tree/c816d375e8ed18964ca277d5fb178ed0b8ea2a26/tests/Doctrine/Tests/ORM/Functional/Ticket
admin commented 2026-01-22 15:06:44 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Feb 1, 2017):

@bettinz I can't understand the issue: please reduce it:

  • DQL only (no query builder)
  • No symfony in the issue

What we basically need is:

  • the DQL string
  • the generated SQL
  • the expected SQL
@Ocramius commented on GitHub (Feb 1, 2017): @bettinz I can't understand the issue: please reduce it: * DQL only (no query builder) * No symfony in the issue What we basically need is: * the DQL string * the generated SQL * the expected SQL
admin commented 2026-01-22 15:06:45 +01:00
Author
Owner
Copy Link

@bettinz commented on GitHub (Feb 1, 2017):

Hello first of all, thank for the replies, I haven't created any test so far, so please understand I've to study on how to do that 😄

Basically I've that entities:

  • Stable, with OneToMany called users and mapped by stable in Join Table "UserForStable"
  • User, with OneToMany called stables and mapped by user in Join Table "UserForStable"
  • UserForStable with
  1. ManyToOne user, connected to User, inversed by stables
  2. ManyToOne stable, connected to Stable, inversed by users

What I need to check is if a user is in the list of users for that stable.
So I've used the operator MEMBER OF: is the current user MEMBER OF stable.users?
The generated sql is:

SELECT s0_.id AS id_0 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.id = ?)

but is wrong on that part:

u1_.id=?

because need to be

u1_.user_id=?

and then

SELECT s0_.id AS id_0 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.user_id = ?)
@bettinz commented on GitHub (Feb 1, 2017): Hello first of all, thank for the replies, I haven't created any test so far, so please understand I've to study on how to do that 😄 Basically I've that entities: - **Stable**, with OneToMany called users and mapped by stable in Join Table "UserForStable" - **User**, with OneToMany called stables and mapped by user in Join Table "UserForStable" - **UserForStable** with 1. ManyToOne user, connected to User, inversed by stables 2. ManyToOne stable, connected to Stable, inversed by users What I need to check is if a user is in the list of users for that stable. So I've used the operator MEMBER OF: _is the current user MEMBER OF stable.users?_ The generated sql is: ```sql SELECT s0_.id AS id_0 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.id = ?) ``` but is wrong on that part: ```sql u1_.id=? ``` because need to be ```sql u1_.user_id=? ``` and then ```sql SELECT s0_.id AS id_0 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.user_id = ?) ```
admin commented 2026-01-22 15:06:45 +01:00
Author
Owner
Copy Link

@lcobucci commented on GitHub (Feb 1, 2017):

@bettinz it's not REALLY HARD to create tests, try to do the following:

  1. Fork the repository and clone it (and install all dependencies, sure)
  2. Copy a functional test that you're gonna use as example, like c816d375e8/tests/Doctrine/Tests/ORM/Functional/Ticket/GH6141Test.php
  3. Map your entities there
  4. Create some sample data inside of the test
  5. Set the expectation and run the query (basically $this->_em->getRepository(Entity::class)->createQueryBuilder('u')->where(':userobj MEMBER OF u.users')->setParameter("userobj", $options['userObj']);)
@lcobucci commented on GitHub (Feb 1, 2017): @bettinz it's not REALLY HARD to create tests, try to do the following: 1. Fork the repository and clone it (and install all dependencies, sure) 1. Copy a functional test that you're gonna use as example, like https://github.com/doctrine/doctrine2/blob/c816d375e8ed18964ca277d5fb178ed0b8ea2a26/tests/Doctrine/Tests/ORM/Functional/Ticket/GH6141Test.php 1. Map your entities there 1. Create some [sample data inside of the test](https://github.com/doctrine/doctrine2/blob/c816d375e8ed18964ca277d5fb178ed0b8ea2a26/tests/Doctrine/Tests/ORM/Functional/Ticket/GH6141Test.php#L36) 1. Set the [expectation and run the query](https://github.com/doctrine/doctrine2/blob/c816d375e8ed18964ca277d5fb178ed0b8ea2a26/tests/Doctrine/Tests/ORM/Functional/Ticket/GH6141Test.php#L45) (basically `$this->_em->getRepository(Entity::class)->createQueryBuilder('u')->where(':userobj MEMBER OF u.users')->setParameter("userobj", $options['userObj']);`)
admin commented 2026-01-22 15:06:46 +01:00
Author
Owner
Copy Link

@bettinz commented on GitHub (Feb 1, 2017):

Hello, I've created a simple project in symfony with the problem here:
https://github.com/bettinz/doctrineTest

@bettinz commented on GitHub (Feb 1, 2017): Hello, I've created a simple project in symfony with the problem here: https://github.com/bettinz/doctrineTest
admin commented 2026-01-22 15:06:46 +01:00
Author
Owner
Copy Link

@lcobucci commented on GitHub (Feb 1, 2017):

@bettinz thanks but it doesn't really help since it doesn't isolate the ORM... could you please try to send the test as instructed?

@lcobucci commented on GitHub (Feb 1, 2017): @bettinz thanks but it doesn't really help since it doesn't isolate the ORM... could you please try to send the test as instructed?
admin commented 2026-01-22 15:06:47 +01:00
Author
Owner
Copy Link

@bettinz commented on GitHub (Feb 1, 2017):

Honestly I tried without results; I'm still learning symfony and doctrine and test is something really new. I've created this simple project with fixtures in order to help. I hope someone can help me and create a simple test based on my simple project 😃

@bettinz commented on GitHub (Feb 1, 2017): Honestly I tried without results; I'm still learning symfony and doctrine and test is something really new. I've created this simple project with fixtures in order to help. I hope someone can help me and create a simple test based on my simple project 😃
admin commented 2026-01-22 15:06:47 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Feb 1, 2017):

See https://github.com/doctrine/doctrine2/blob/master/tests/Doctrine/Tests/ORM/Query/SelectSqlGenerationTest.php

An example project is not something we can work with: the "learning how" bit goes both ways

@Ocramius commented on GitHub (Feb 1, 2017): See https://github.com/doctrine/doctrine2/blob/master/tests/Doctrine/Tests/ORM/Query/SelectSqlGenerationTest.php An example project is not something we can work with: the "learning how" bit goes both ways
admin commented 2026-01-22 15:06:48 +01:00
Author
Owner
Copy Link

@bettinz commented on GitHub (Feb 1, 2017):

I don't know if it help or not, but I'm trying to reply your third post:
DQL:

 SELECT s.name, s.id from AppBundle:Stable s where :userobj MEMBER OF s.users

Generated sql:

SELECT s0_.name AS name_0, s0_.id AS id_1 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.id = ?)

correct sql

SELECT s0_.name AS name_0, s0_.id AS id_1 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.user_id = ?)

I've only found documentations on tests for doctrine 1.4. I'm sorry if I can't be useful, I'm really trying to create a test

@bettinz commented on GitHub (Feb 1, 2017): I don't know if it help or not, but I'm trying to reply your third post: DQL: ```sql SELECT s.name, s.id from AppBundle:Stable s where :userobj MEMBER OF s.users ``` Generated sql: ```sql SELECT s0_.name AS name_0, s0_.id AS id_1 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.id = ?) ``` correct sql ```sql SELECT s0_.name AS name_0, s0_.id AS id_1 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.user_id = ?) ``` I've only found documentations on tests for doctrine 1.4. I'm sorry if I can't be useful, I'm really trying to create a test
admin commented 2026-01-22 15:06:48 +01:00
Author
Owner
Copy Link

@Ocramius commented on GitHub (Feb 2, 2017):

That's clear now, thanks! We now need to figure out if the problem is in
the mapping definitions or in the DQL transformation

On 2 Feb 2017 00:31, "bettinz" notifications@github.com wrote:

I don't know if it help or not, but I'm trying to reply your third post:
DQL:

SELECT s.name, s.id from AppBundle:Stable s where :userobj MEMBER OF s.users

Generated sql:

SELECT s0_.name AS name_0, s0_.id AS id_1 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.id = ?)

correct sql

SELECT s0_.name AS name_0, s0_.id AS id_1 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.user_id = ?)

I've only found documentations on tests for doctrine 1.4. I'm sorry if I
can't be useful, I'm really trying to create a test

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/doctrine/doctrine2/issues/6270#issuecomment-276817949,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAJakCwfxqcCpnrS4EAN1xSLDbwB2Yg2ks5rYRXlgaJpZM4LzWt4
.

@Ocramius commented on GitHub (Feb 2, 2017): That's clear now, thanks! We now need to figure out if the problem is in the mapping definitions or in the DQL transformation On 2 Feb 2017 00:31, "bettinz" <notifications@github.com> wrote: > I don't know if it help or not, but I'm trying to reply your third post: > DQL: > > SELECT s.name, s.id from AppBundle:Stable s where :userobj MEMBER OF s.users > > Generated sql: > > SELECT s0_.name AS name_0, s0_.id AS id_1 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.id = ?) > > correct sql > > SELECT s0_.name AS name_0, s0_.id AS id_1 FROM stable s0_ WHERE EXISTS (SELECT 1 FROM user_for_stable u1_ WHERE s0_.id = u1_.stable_id AND u1_.user_id = ?) > > I've only found documentations on tests for doctrine 1.4. I'm sorry if I > can't be useful, I'm really trying to create a test > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub > <https://github.com/doctrine/doctrine2/issues/6270#issuecomment-276817949>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/AAJakCwfxqcCpnrS4EAN1xSLDbwB2Yg2ks5rYRXlgaJpZM4LzWt4> > . >
admin commented 2026-01-22 15:06:48 +01:00
Author
Owner
Copy Link

@HeahDude commented on GitHub (Feb 8, 2017):

Hey @bettinz, sorry for the late reply. Maybe you just need to handle User and Stable as entity in your code.

Unless you have a specific need for it, you'd better not have an object in your code to materialize this relation, https://github.com/bettinz/doctrineTest/blob/master/src/AppBundle/Entity/UserForStable.php#L13 should not be needed.

class User
{
    // ...

    /**
     *@ManyToMany(targetEntity="Stables")
     */
    private $stables;

    public function __construct()
    {
        $this->stables = new ArrayCollection();
    }
}

With only this mapping, and without creating a $users property in the Stable class, the database would still create a join table https://doctrine-orm.readthedocs.io/en/latest/reference/association-mapping.html#many-to-many-unidirectional, and it's up to you to choose to have a bi-directionnal relation.

@HeahDude commented on GitHub (Feb 8, 2017): Hey @bettinz, sorry for the late reply. Maybe you just need to handle `User` and `Stable` as entity in your code. Unless you have a specific need for it, you'd better not have an object in your code to materialize this relation, https://github.com/bettinz/doctrineTest/blob/master/src/AppBundle/Entity/UserForStable.php#L13 should not be needed. ```php class User { // ... /** *@ManyToMany(targetEntity="Stables") */ private $stables; public function __construct() { $this->stables = new ArrayCollection(); } } ``` With only this mapping, and without creating a `$users` property in the `Stable` class, the database would still create a join table https://doctrine-orm.readthedocs.io/en/latest/reference/association-mapping.html#many-to-many-unidirectional, and it's up to you to choose to have a [bi-directionnal relation](https://doctrine-orm.readthedocs.io/en/latest/reference/association-mapping.html#many-to-many-bidirectional).
admin commented 2026-01-22 15:06:49 +01:00
Author
Owner
Copy Link

@bettinz commented on GitHub (Feb 8, 2017):

Hello @HeahDude , I usually prefer a "Many-To-One<->One-To-Many" relation with an entity in the middle as explained here http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/association-mapping.html#many-to-many-unidirectional

Why are many-to-many associations less common? Because frequently you want to associate additional attributes with an association, in which case you introduce an association class. Consequently, the direct many-to-many association disappears and is replaced by one-to-many/many-to-one associations between the 3 participating classes.

In this particular case I also need some extra fields in the middle entity, and I think I can't do the work with a Many To Many.

@bettinz commented on GitHub (Feb 8, 2017): Hello @HeahDude , I usually prefer a "Many-To-One<->One-To-Many" relation with an entity in the middle as explained here [http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/association-mapping.html#many-to-many-unidirectional](url) > Why are many-to-many associations less common? Because frequently you want to associate additional attributes with an association, in which case you introduce an association class. Consequently, the direct many-to-many association disappears and is replaced by one-to-many/many-to-one associations between the 3 participating classes. In this particular case I also need some extra fields in the middle entity, and I think I can't do the work with a Many To Many.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
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 Bug Missing Tests
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5401
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?