When sorting by post date in a taxonomy term view, duplicates are returned. This is caused by the 'created' column from the taxonomy_index table being added to the SELECT statement of the query.
Here is an example of my taxonomy_index table:
mysql> select * from taxonomy_index where tid = '267';
+-----+-----+--------+--------+------------+
| nid | tid | status | sticky | created |
+-----+-----+--------+--------+------------+
| 703 | 267 | 1 | 0 | 1556900391 |
| 621 | 267 | 1 | 0 | 1536762360 |
| 606 | 267 | 1 | 0 | 1533758079 |
| 514 | 267 | 1 | 0 | 1524601353 |
| 513 | 267 | 1 | 0 | 1524601137 |
| 512 | 267 | 1 | 0 | 1524600793 |
+-----+-----+--------+--------+------------+Note that there are multiple entries for this tid because it appears on multiple nodes.
My views query is as follows:
SELECT DISTINCT taxonomy_index.created AS taxonomy_index_created, taxonomy_term_field_data.tid AS tid
FROM taxonomy_term_field_data taxonomy_term_field_data
LEFT JOIN taxonomy_term__field_initiatives taxonomy_term__field_initiatives ON taxonomy_term_field_data.tid = taxonomy_term__field_initiatives.entity_id AND taxonomy_term__field_initiatives.deleted = '0'
LEFT JOIN taxonomy_index taxonomy_index ON taxonomy_term_field_data.tid = taxonomy_index.tid
WHERE ((taxonomy_term__field_initiatives.field_initiatives_target_id = '222')) AND ((taxonomy_term_field_data.vid IN ('project')) AND (taxonomy_term_field_data.status = '1'))
ORDER BY taxonomy_index_created ASCThis query produces the following results:
+------------------------+-----+
| taxonomy_index_created | tid |
+------------------------+-----+
| NULL | 268 |
| NULL | 270 |
| NULL | 284 |
| 1524600793 | 267 |
| 1524601137 | 267 |
| 1524601353 | 267 |
| 1533758079 | 267 |
| 1536762360 | 267 |
| 1556900391 | 267 |
+------------------------+-----+You can see that tid 267 is retrieved multiple times. The DISTINCT has no effect because the taxonomy_index_created field is included in the SELECT statement.
When a sort is added to the view. I don't think it's necessary to add that column to the SELECT statement. For example, the following query, which omits taxonomy_index_created from the SELECT, returns without duplicates:
SELECT DISTINCT taxonomy_term_field_data.tid AS tid
FROM
taxonomy_term_field_data taxonomy_term_field_data
LEFT JOIN taxonomy_term__field_initiatives taxonomy_term__field_initiatives ON taxonomy_term_field_data.tid = taxonomy_term__field_initiatives.entity_id AND taxonomy_term__field_initiatives.deleted = '0'
LEFT JOIN taxonomy_index taxonomy_index ON taxonomy_term_field_data.tid = taxonomy_index.tid
WHERE ((taxonomy_term__field_initiatives.field_initiatives_target_id = '222')) AND ((taxonomy_term_field_data.vid IN ('project')) AND (taxonomy_term_field_data.status = '1'))
ORDER BY taxonomy_index.created ASCThe above query correctly returns without duplicates:
+-----+
| tid |
+-----+
| 268 |
| 270 |
| 284 |
| 267 |
+-----+Postponed: needs info
11.0 🔥
Last updated
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.
@ Thank you for reporting this problem. We rely on issue reports like this one to resolve bugs and improve Drupal core.
There has been no activity here for 4 and the description of the problem uses a Drupal 7 database.
Is this still relevant for a currently supported version of Drupal?
If there is no confirmation that this problem exists in Drupal 10 it may be closed after three months.
Thanks!