Project

General

Profile

Actions
Copy link

Bug #32539

closed

Using a constraint or ordering on a property on the right side of a m:n relation causes records from the left side with no related elements (0 cardinality) to not show

Added by Jose Antonio Guerra over 12 years ago. Updated about 4 years ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Extbase
Target version:
-
Start date:
2011-12-14
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
6.2
PHP Version:
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

On the following model:

  • Model1
  • Model2
  • Model1.related is a m:n relation to Model2. Cardinality is 0..n

Example data:

  • Model 1{a} -> "name" => "some value", "related" => Model2{a}
  • Model 1{b} -> "name" => "some value"
  • Model 2{a} -> "name" => "some value"

If you run a query on Extbase that runs a constraint on any property of Model2 through Model1:

(inside Model1 repository class)

$query = $this->createQuery();
$query->matching(
  $query->logicalOr(
        $query->like('name', 'some value'),
        $query->like('related.name', 'some value'),
    )
)->execute();

You will get only Model1 results which are related to matching Model2 rows, but according to the "logicalOr" you must get also Model1 results matching the criteria which are not related to Model2.

You currently get:

  • Model 1{a}

But you should get:

  • Model 1{a}
  • Model 1{b}

By analysis of the generated SQL the problem is caused by the "enableFields" (the joins are ok):

The current generated SQL query part from the enableFields: AND tx_myext_domain_model_model2.deleted=0 AND [....]
...which forces elements from Model2 to be present on the relation.

This would be the right way: AND (tx_myext_domain_model_model2.uid IS NULL OR (tx_myext_domain_model_model2.deleted=0 AND [....]))

In the latter case we check wether the LEFT JOIN is not getting any values from Model2 and in that case don't apply the enableFields for Model2 on that row.

Files

dummy21755.tgz (503 KB) dummy21755.tgz Dummy-extension for testing Stefan Neufeind, 2015-07-17 00:27

Related issues 2 (0 open — 2 closed)

Is duplicate of TYPO3 Core - Bug #88919: QueryBuilder with DefaultRestrictionContainer effectivly transforms outer joins to inner joins because the restrictions are applied to all join tables unconditionallyClosed2019-08-05

Actions
Is duplicate of TYPO3 Core - Bug #86385: QueryBuilder restrictions break leftJoinClosed2018-09-26

Actions
Actions
Copy link

Also available in: Atom PDF