orm Filter on JOIN

Hi, I'm having a problem filtering entities when retrieved through a join.

This is my case (simplified): Let's say I have a User entity, and a Car entity. Both entities are soft-deletable (whether they're a good pattern or bad, I use them). When I find a User, the filter is applied, as well as with the Car entity. However, when I find a User and include the Car in a join, the filter is only applied to the User entity, and it returns the deleted Car entities as well.

I could not find any documentation as to whether this is expected (and wanted) behaviour, or a "bug". Shouldn't the filters also be applied to fetched relations?

Can anyone elaborate?

Edit: upon better search, this might be related to #6037

Mar 15 '17 14:03 yp28

From the docs:

The filter functionality works on SQL level. Whether a SQL query is generated in a Persister, during lazy loading, in extra lazy collections or from DQL.

So this would seem like a bug. Did you try running the query with XDebug?

As for the PR you linked - do you have eager fetch as well?

Apr 18 '17 20:04 szymach

@szymach, we do have eager fetch configured for both entities that were involved.

I did not run it with XDebug enabled, we just reversed the queried the related entity as it was more important than the one we were query-ing.

Apr 19 '17 07:04 yp28

I can confirm combining fetch="EAGER" and SQLFilter does not work well. SQLFilter is not aplied in this scenario.

Fix would be nice:D

Apr 29 '17 07:04 mssimi

@mssimi we're doing the best we can with the contributors that we have but since the failing test to reproduce it is available anyone could send a patch to fix the issue 😉

Apr 29 '17 10:04 lcobucci

I confirm this on one-to-one bidirectional association. After owning side is soft deleted. When i get inversed side entity by simple select it also join owning side without filter applied. Example:

    /**
     * Document original association
     *
     * @var \AppBundle\Entity\Document
     * @ORM\OneToOne(targetEntity="Document", inversedBy="draftDocument")
     * @ORM\JoinColumn(onDelete="CASCADE")
     */
    private $draftOf = null;

    /**
     * Document draft association
     *
     * @var \AppBundle\Entity\Document
     * @ORM\OneToOne(targetEntity="Document", mappedBy="draftOf", cascade={"persist","remove"})
     */
    private $draftDocument = null;

This class has also SoftDeleteable extension applied from Atlantic18/DoctrineExtensions which adds SQL filters.

And when i try to select single entity i get SQL:

SELECT t0.title AS title_1, t0.body AS body_2, t0.slug AS slug_3, t0.version AS version_4, t0.draft AS draft_5, t0.version_info AS version_info_6, t0.id AS id_7, t0.created_at AS created_at_8, t0.updated_at AS updated_at_9, t0.created_by AS created_by_10, t0.updated_by AS updated_by_11, t0.deleted_at AS deleted_at_12, t0.clinic_id AS clinic_id_13, t0.parent_id AS parent_id_14, t0.draft_of_id AS draft_of_id_15, t16.title AS title_17, t16.body AS body_18, t16.slug AS slug_19, t16.version AS version_20, t16.draft AS draft_21, t16.version_info AS version_info_22, t16.id AS id_23, t16.created_at AS created_at_24, t16.updated_at AS updated_at_25, t16.created_by AS created_by_26, t16.updated_by AS updated_by_27, t16.deleted_at AS deleted_at_28, t16.clinic_id AS clinic_id_29, t16.parent_id AS parent_id_30, t16.draft_of_id AS draft_of_id_31 FROM app_document t0 LEFT JOIN app_document t16 ON t16.draft_of_id = t0.id WHERE t0.slug = ? AND ((t0.deleted_at IS NULL)) LIMIT 1

You can see: LEFT JOIN app_document t16 ON ... without AND (t0.deleted_at IS NULL) but WHERE has valid filter on t0 in the same time.

Jun 30 '17 12:06 lukaszjakubek

@lukaszjakubek could you please send us a failing test case that reproduces that behaviour? It would help us a lot to identify and fix the issue you're describing.

You can find examples on https://github.com/doctrine/doctrine2/tree/388afb46d0cb3ed0c51332e8df0de9e942c2690b/tests/Doctrine/Tests/ORM/Functional/Ticket

Aug 06 '17 17:08 lcobucci

Here is connected issue in Atlantic18/DoctrineExtensions with explanation https://github.com/Atlantic18/DoctrineExtensions/issues/1463

and failing test case https://github.com/Atlantic18/DoctrineExtensions/pull/1550

Oct 19 '17 10:10 seyfer

Any news from this?

Nov 30 '19 15:11 fuser82

Are there any news on this issue? I'm facing exactly the same problem after upgrading from v2.4.8 to v2.5.14. SQL-Filter seems to be ignored for Eager Fetch Joins of Relations.

Is this Bug fixed in newer versions or is it still remaining in the follow up versions of Doctrine?