1

I have a node view, page display with format "table" where the changed date is set as the default sorting order by the table settings. When I set "Distinct" for the view I get the above mentioned error.

This is the query which is shown to me:

SELECT DISTINCT 
 "sidejob"."id" AS "id", 
 "candidacies_mandates_sidejob__mandates"."id" AS "candidacies_mandates_sidejob__mandates_id", 
 "politician_candidacies_mandates"."id" AS "politician_candidacies_mandates_id", 
 "sidejob_organization_sidejob"."id" AS "sidejob_organization_sidejob_id" FROM {sidejob} "sidejob"
 
LEFT JOIN {sidejob__mandates} "sidejob__mandates" ON sidejob.id = sidejob__mandates.entity_id AND sidejob__mandates.deleted = :views_join_condition_0 
LEFT JOIN {candidacies_mandates} "candidacies_mandates_sidejob__mandates" ON sidejob__mandates.mandates_target_id = candidacies_mandates_sidejob__mandates.id 

LEFT JOIN {politician} "politician_candidacies_mandates" ON candidacies_mandates_sidejob__mandates.politician = politician_candidacies_mandates.id 

LEFT JOIN {sidejob_organization} "sidejob_organization_sidejob" ON sidejob.sidejob_organization = sidejob_organization_sidejob.id 

WHERE "sidejob"."id" IN (:db_condition_placeholder_1) 
ORDER BY "sidejob"."changed" DESC

I found an issue on drupal.org with a proposed solution which works most of the time but not when the field is from a joined table. The solution for most cases working is adding a custom filter plugin to the view which iterates through all the fields sortable in table display and adding it to the query:

  public function query() {
    // Force View to DISTINCT (because multiple matches makes duplicate
    // query results), and ensure all fields in DISTINCT exist in query to avoid
    // SQL error "ORDER BY clause is not in SELECT list": Table column click
    // to sort (and its default order) adds an additional field too late in the
    // game, so we preemptively add all these fields to the query to anticipate
    // it.
    $this->query->distinct = TRUE;

    // Fields the table sorting plugin might use which might not be in the query.
    $sortable_fields = array_keys(array_filter($this->view->getDisplay()->getOption('style')['options']['info'], function ($item) {
      return $item['sortable'] ?? FALSE;
    }));

    // Get field table meta and add to query:
    foreach ($sortable_fields as $field_name) {
      $field = $this->view->field[$field_name] ?? NULL;
      if ($field) {
        $this->query->addField($field->table, $field->realField);
      }
    }
  }

The remaining problem as described in the issue: when a field is used for sorting the table which comes from a joined table this solution is not working. I don't get why $this->query->addField($field->table, $field->realField); does not manage to ensure the table properly. I will ask this in another question as this might be a different problem.

The main question here: is there any other solution to avoid this SQL error when combining views table disply with sortable columns and the distinct settig for the view?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.