Bug #27057

Can't query on properties of child objects of the same class

Added by Björn Steinbrink over 8 years ago. Updated about 2 years ago.

Status:
Closed
Priority:
Must have
Category:
Extbase
Target version:
Start date:
2011-05-26
Due date:
% Done:

100%

TYPO3 Version:
7
PHP Version:
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

Given a domain model that has a connection to another object of the same class like this:

class Tx_Whatever_Domain_Model_Foo {
  /**
   * @var Tx_Whatever_Domain_Model_Foo
   */
   protected $parent;
}

And given this repository method:

class Tx_Whatever_Domain_Repository_FooRepository {
  public findByParentTitle($title) {
    $query = $this->createQuery();
    return $query->matching($query->equals('parent.title', $title))->execute();
  }
}

Running $repository->findByParentTitle('test');, extbase generates invalid SQL and crashes.

An error occured. Exception thrown:
Tx_Extbase_Persistence_Storage_Exception_SqlError
Error message: Not unique table/alias: 'tx_whatever_domain_model_foo':

SELECT DISTINCT tx_whatever_domain_model_foo.*
FROM tx_whatever_domain_model_foo
LEFT JOIN tx_whatever_domain_model_foo ON tx_whatever_domain_model_foo.parent=tx_whatever_domain_model_foo.uid
WHERE tx_whatever_domain_model_foo.parent = 'test' AND ...


Related issues

Related to TYPO3 Core - Bug #62151: addUnionStatement does not handle multiple fields with same related table Closed 2014-10-10
Related to TYPO3 Core - Bug #58511: addUnionStatement does not respect MM_match_fields Closed 2014-05-05
Duplicated by TYPO3 Core - Feature #55959: Joins on SubClasses New 2014-02-13

Associated revisions

Revision e1bd13b2 (diff)
Added by Alexander Schnitzler almost 7 years ago

[BUGFIX] Prevent sql errors with unsupported relation

Using passthrough as relation causes sql errors
in the addUnionStatement of Typo3DbBackend. To
prevent this an Exception is introduced which will
be thrown instead.

Fixes: #27057
Releases: 6.0
Change-Id: I3e57d2b8c290e8f8b445278a3a25ffef15adbeb5
Reviewed-on: http://review.typo3.org/16537
Reviewed-by: Anja Leichsenring
Tested-by: Anja Leichsenring
Reviewed-by: Tomasz Grzemski
Tested-by: Tomasz Grzemski
Reviewed-by: Alexander Schnitzler
Tested-by: Alexander Schnitzler

Revision 755a948b (diff)
Added by Alexander Stehlik about 4 years ago

[FEATURE] Relations to the same table in Extbase

This patch introduces a mapping for properties to table aliases in
the Extbase Typo3DbQueryParser. This allows relations between
identical classes / tables.

Additionally the configured match fields of a table are now
respected in UNION statement (MM_match_fields,
foreign_match_fields or foreign_table_field).

Releases: master
Resolves: #27057
Change-Id: If1e88c994b8a1179f6babf2bdfc1da3e61b2c658
Reviewed-on: http://review.typo3.org/33956
Reviewed-by: Stephan Großberndt <>
Tested-by: Stephan Großberndt <>
Reviewed-by: Alexander Opitz <>
Tested-by: Alexander Opitz <>

History

#1 Updated by Alexander Schnitzler almost 7 years ago

  • Status changed from New to Accepted
  • Assignee set to Alexander Schnitzler
  • Target version set to 1843
  • Has patch set to No

Can be reproduced, try to fix this.

#2 Updated by Gerrit Code Review almost 7 years ago

  • Status changed from Accepted to Under Review

Patch set 1 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/16535

#3 Updated by Gerrit Code Review almost 7 years ago

Patch set 2 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/16535

#4 Updated by Gerrit Code Review almost 7 years ago

Patch set 1 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/16537

#5 Updated by Alexander Schnitzler almost 7 years ago

  • Target version changed from 1843 to Extbase 6.0

#6 Updated by Anonymous almost 7 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

Applied in changeset commit:36203cc17d46ac07088ddd17f21440c00a46a969.

#7 Updated by Mathias Brodala over 6 years ago

This error still appears in the current stable release (6.1.1) with this simple setup:

Domain model

namespace TEST\RelationQueryTest\Domain\Model;
class Element extends \TYPO3\CMS\Extbase\DomainObject\AbstractEntity {
  /**
   * @var \string
   * @validate NotEmpty
   */
  protected $title;
  /**
   * @var \TEST\RelationQueryTest\Domain\Model\Element
   */
  protected $relatedElement;
  /* getters/setters ommitted */
}

Domain repository

namespace TEST\RelationQueryTest\Domain\Repository;
class ElementRepository extends \TYPO3\CMS\Extbase\Persistence\Repository {
  /**
   * @param \string $title
   * @return \TYPO3\CMS\Extbase\Persistence\QueryResultInterface|array
   */
  public function findByRelatedElementTitle($title) {
    $query = $this->createQuery();
    return $query->matching($query->equals('relatedElement.title', $title))->execute();
  }
}

TCA configuration

$TCA['tx_relationquerytest_domain_model_element'] = array(
  /* ctrl, interface, types omitted */
  'columns' => array(
    'title' => array(
      'exclude' => 0,
      'label' => 'Title',
      'config' => array(
        'type' => 'input',
        'size' => 30,
        'eval' => 'trim,required'
      ),
    ),
    'related_element' => array(
      'exclude' => 0,
      'label' => 'Related element',
      'config' => array(
        'type' => 'inline',
        'foreign_table' => 'tx_relationquerytest_domain_model_element',
        'minitems' => 0,
        'maxitems' => 1,
        'appearance' => array(
          'collapseAll' => 0,
          'levelLinksPosition' => 'top',
          'showSynchronizationLink' => 1,
          'showPossibleLocalizationRecords' => 1,
          'showAllLocalizationLink' => 1
        ),
      ),
    ),
  ),
);

The error again:

#1247602160: Not unique table/alias: 'tx_relationquerytest_domain_model_element':
SELECT COUNT(DISTINCT tx_relationquerytest_domain_model_element.uid)
FROM tx_relationquerytest_domain_model_element
LEFT JOIN tx_relationquerytest_domain_model_element
ON tx_relationquerytest_domain_model_element.related_element=tx_relationquerytest_domain_model_element.uid
WHERE tx_relationquerytest_domain_model_element.title = 'A'
AND tx_relationquerytest_domain_model_element.deleted=0
AND tx_relationquerytest_domain_model_element.t3ver_state<=0
AND tx_relationquerytest_domain_model_element.pid<>-1
AND tx_relationquerytest_domain_model_element.hidden=0
AND tx_relationquerytest_domain_model_element.starttime<=1374245400
AND (tx_relationquerytest_domain_model_element.endtime=0
  OR tx_relationquerytest_domain_model_element.endtime>1374245400
)
AND (tx_relationquerytest_domain_model_element.sys_language_uid IN (0,-1))
AND tx_relationquerytest_domain_model_element.pid IN (7)

There is no passthrough in the TCA configuration anywhere as mentioned in the commit message. How can one fix this error?

#8 Updated by Gerrit Code Review almost 5 years ago

  • Status changed from Resolved to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/33956

#9 Updated by Alexander Stehlik almost 5 years ago

I stumbled over the same issue and as far as I can see the patch from Alex did not resolve it.

This can be easily reproduces with a functional test (see first patch in the review system).

@Mathias: does the patch from http://review.typo3.org/33956 resolve the issue for you?

#10 Updated by Gerrit Code Review almost 5 years ago

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/33956

#11 Updated by Gerrit Code Review almost 5 years ago

Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/33956

#12 Updated by Alexander Opitz almost 5 years ago

  • Project changed from Extbase MVC Framework to TYPO3 Core
  • Category changed from Extbase: Generic Persistence to Extbase
  • Target version changed from Extbase 6.0 to 7.0
  • TYPO3 Version set to 6.2
  • Is Regression set to No

#13 Updated by Gerrit Code Review almost 5 years ago

Patch set 4 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/33956

#14 Updated by Gerrit Code Review almost 5 years ago

Patch set 5 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/33956

#15 Updated by Mathias Schreiber almost 5 years ago

  • Target version changed from 7.0 to 7.1 (Cleanup)

#16 Updated by Gerrit Code Review over 4 years ago

Patch set 6 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/33956

#17 Updated by Benni Mack over 4 years ago

  • Target version changed from 7.1 (Cleanup) to 7.4 (Backend)

#18 Updated by Susanne Moog about 4 years ago

  • Target version changed from 7.4 (Backend) to 7.5

#19 Updated by Gerrit Code Review about 4 years ago

Patch set 7 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/33956

#20 Updated by Benni Mack about 4 years ago

  • Target version deleted (7.5)

#21 Updated by Gerrit Code Review about 4 years ago

Patch set 8 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/33956

#22 Updated by Gerrit Code Review about 4 years ago

Patch set 9 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/33956

#23 Updated by Stephan Großberndt about 4 years ago

  • Target version set to 7 LTS
  • TYPO3 Version changed from 6.2 to 7

#24 Updated by Gerrit Code Review about 4 years ago

Patch set 10 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/33956

#25 Updated by Gerrit Code Review about 4 years ago

Patch set 11 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/33956

#26 Updated by Alexander Stehlik about 4 years ago

  • Status changed from Under Review to Resolved

#27 Updated by Riccardo De Contardi about 2 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF