I was attempting to query some content types using the query interface (in graphql), I wanted them returning in a particular order of langcode (eg fr then en), the by created date. So the final result should return the french nodes, then the english nodes in created date order.
Digging into this, i found that the sql being produced is somewhat wrong, it seems like there is a disconnect somewhere between the select and where when a sort is being applied.
in my example,
$storage = $this->entityTypeManager->getStorage('node');
$entityType = $storage->getEntityType();
$query = $storage->getQuery()
->currentRevision()
->accessCheck()
->condition('status', Node::PUBLISHED);
$query->condition($entityType->getKey('bundle'), 'resource');
if (!empty($langcode)) {
if ($langcode !== "EN") {
$langConditions = $query->orConditionGroup()
->condition('langcode', strtolower('fr')) //hard coded for debug's sake
->condition('langcode', strtolower("en"));
$query->condition($langConditions);
} else {
$query->condition('langcode', strtolower($langcode));
}
}
$query->sort('langcode', 'DESC');
$query->sort('created', 'DESC');
$query->range($offset, $limit);
$metadata->addCacheTags($entityType->getListCacheTags());
$metadata->addCacheContexts($entityType->getListCacheContexts());
return new QueryResourceConnection($query);Gets a generated sql output of
SELECT
`base_table`.`vid` AS `vid`,
`base_table`.`nid` AS `nid`,
MAX(node_field_data_3.langcode) AS `expression`,
MAX(node_field_data_3.created) AS `expression_2`
FROM
`node` `base_table`
INNER JOIN `node_field_data` `node_field_data` ON
`node_field_data`.`nid` = `base_table`.`nid`
LEFT JOIN `node_field_data` `node_field_data_2` ON
`node_field_data_2`.`nid` = `base_table`.`nid`
INNER JOIN `node__field_resource_type` `node__field_resource_type` ON
`node__field_resource_type`.`entity_id` = `base_table`.`nid`
LEFT OUTER JOIN `taxonomy_term_data` `taxonomy_term_data` ON
`taxonomy_term_data`.`tid` = `node__field_resource_type`.`field_resource_type_target_id`
INNER JOIN `taxonomy_term_field_data` `taxonomy_term_field_data` ON
`taxonomy_term_field_data`.`tid` = `taxonomy_term_data`.`tid`
LEFT JOIN `node_field_data` `node_field_data_3` ON
`node_field_data_3`.`nid` = `base_table`.`nid`
WHERE
(`node_field_data`.`status` = '1') AND
(`node_field_data`.`type` = 'resource') AND
(
(`node_field_data_2`.`langcode` = 'fr') OR
(`node_field_data_2`.`langcode` = 'en')
) AND
(`taxonomy_term_field_data`.`name` LIKE 'My Resource' ESCAPE '\\')
GROUP BY
`base_table`.`vid`,
`base_table`.`nid`
ORDER BY
`expression` DESC,
`expression_2` DESC
the issue here being that node_field_data_2 and node_field_data_3 are joins, but the MAX() is being done on node_field_data_3 and not 2, which produces the incorrect results. like this.. https://pastebin.com/QjavJWxE
I think the easiest would be to create multiple nodes in a few different languages, and then run the query's to see if you can get them sorting in the correct order. I probably need to make a more concrete small example however.
Active
10.3 ✨
database system