Bug #70677

Sorting of foreign records in TCA type select

Added by Christian Fries over 4 years ago. Updated 12 days ago.

Status:
Resolved
Priority:
Should have
Assignee:
-
Category:
Backend API
Start date:
2015-10-14
Due date:
% Done:

100%

TYPO3 Version:
8
PHP Version:
7.1
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

Currently records of a foreign table won't be sorted in a select field if foreign_table_where doesn't contain an "ORDER BY ..." statement.

I'd suggest the following solution:
  • Introduce a new option 'foreign_table_sortby'
  • If 'foreign_table_sortby' is defined, use this statement for sorting
  • Backward compatibility: If 'foreign_table_where' contains an 'ORDER BY ...' statement and 'foreign_table_sortby' is undefined, use the 'ORDER BY ...' statement as 'foreign_table_sortby'. If 'foreign_table_sortby' is defined, ignore 'ORDER BY ...' statements in 'foreign_table_where'
  • If 'foreign_table_sortby' is not defined, use 'default_sortby' of the foreign table

In the long term this is a cleaner solution and if no sorting is defined, the records are sorted according to the default sorting.


Related issues

Related to TYPO3 Core - Bug #90127: ambiguous column name title when editing content elements Resolved 2020-01-16
Blocks TYPO3 Core - Task #88933: Cleanup foreign_table_where directives Closed 2019-08-07

Associated revisions

Revision 1d604c7e (diff)
Added by Christoph Lehmann 12 days ago

[TASK] Respect default_sortby in TCA foreign table queries

Currently an ORDER BY clause is needed in foreign_table_where
to get a sorting. This makes it obsolete in favor of the default
tables sorting through default_sortby. This concerns TCA's
select and tree fields.

Resolves: #70677
Releases: master,9.5
Change-Id: I259211f7812bfa39e9744e71265577524911082d
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/61474
Tested-by: TYPO3com <>
Tested-by: Sascha Rademacher <>
Tested-by: Jörg Bösche <>
Tested-by: Felix P. <>
Tested-by: Steffen Frese <>
Tested-by: Tobi Kretschmann <>
Reviewed-by: Jörg Bösche <>
Reviewed-by: Felix P. <>
Reviewed-by: Sascha Rademacher <>
Reviewed-by: Steffen Frese <>
Reviewed-by: Tobi Kretschmann <>

Revision 39d258b2 (diff)
Added by Christoph Lehmann 12 days ago

[TASK] Respect default_sortby in TCA foreign table queries

Currently an ORDER BY clause is needed in foreign_table_where
to get a sorting. This makes it obsolete in favor of the default
tables sorting through default_sortby. This concerns TCA's
select and tree fields.

Resolves: #70677
Releases: master,9.5
Change-Id: I259211f7812bfa39e9744e71265577524911082d
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62906
Tested-by: TYPO3com <>
Tested-by: Tobi Kretschmann <>
Reviewed-by: Tobi Kretschmann <>

Revision c2b99dd9 (diff)
Added by Daniel Siepmann 11 days ago

[BUGFIX] Prevent ambiguous column name for TCA items

When fetching items from foreign table, they might be sorted based on
foreign table configured sorting. In such case the foreign table name is
prefixed to prevent ambiguous column name.

Resolves: #90127
Related: #70677
Releases: master, 9.5
Change-Id: Iebda2bd417481ae01b1265dd9be31b67af89991d
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62929
Reviewed-by: Felix P. <>
Reviewed-by: Jörg Bösche <>
Reviewed-by: Daniel Goerz <>
Reviewed-by: Sascha Rademacher <>
Reviewed-by: Tobi Kretschmann <>
Tested-by: Jörg Bösche <>
Tested-by: TYPO3com <>
Tested-by: Sascha Rademacher <>
Tested-by: Tobi Kretschmann <>

Revision 052a4501 (diff)
Added by Daniel Siepmann 11 days ago

[BUGFIX] Prevent ambiguous column name for TCA items

When fetching items from foreign table, they might be sorted based on
foreign table configured sorting. In such case the foreign table name is
prefixed to prevent ambiguous column name.

Resolves: #90127
Related: #70677
Releases: master, 9.5
Change-Id: Iebda2bd417481ae01b1265dd9be31b67af89991d
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62937
Tested-by: TYPO3com <>
Tested-by: Tobi Kretschmann <>
Reviewed-by: Tobi Kretschmann <>

History

#1 Updated by Robert Wildling almost 2 years ago

  • Tracker changed from Feature to Bug
  • Category set to Backend API
  • Target version set to next-patchlevel
  • TYPO3 Version set to 8
  • PHP Version set to 7.1

I just came over a similar problem: the default_sortby is a date field (start_date) and it works fine, if it is used as the standard sorting mechanism of the table. But as soon as it is used as the right table of an m:m relation, things don't work as nicely any more, when the TCA does NOT define an ORDER BY. As soon as there is an "ORDER BY start_date" it works as expected - but it is "double the trouble".

I would say: if there is no ORDER BY, then the standard sorting mechanism of the table should be used.
(I am not so sure if the intrdocution of a new sorting mechanism is necessary...)

TYPO3 8.7.1, php 7.1.13, MAMP with opcache

#2 Updated by Benni Mack 9 months ago

  • Target version changed from next-patchlevel to Candidate for patchlevel

#3 Updated by Gerrit Code Review 6 months ago

  • Status changed from New 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 https://review.typo3.org/c/Packages/TYPO3.CMS/+/61474

#4 Updated by Christoph Lehmann 6 months ago

  • Related to Task #88933: Cleanup foreign_table_where directives added

#5 Updated by Christoph Lehmann 6 months ago

  • Related to deleted (Task #88933: Cleanup foreign_table_where directives)

#6 Updated by Christoph Lehmann 6 months ago

  • Blocks Task #88933: Cleanup foreign_table_where directives added

#7 Updated by Gerrit Code Review 6 months ago

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

#8 Updated by Gerrit Code Review 6 months ago

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

#9 Updated by Gerrit Code Review 4 months ago

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

#10 Updated by Gerrit Code Review 12 days ago

Patch set 1 for branch 9.5 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/62906

#11 Updated by Christoph Lehmann 12 days ago

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

#12 Updated by Benni Mack 11 days ago

  • Related to Bug #90127: ambiguous column name title when editing content elements added

Also available in: Atom PDF