In my task I need to display the results based on "the newest first". I perform a query:

$query = \Drupal::database()->select('node_field_data', 'node');
$query->innerJoin('node__field_ils_apps', 'app', 'app.entity_id = node.nid AND app.field_ils_apps_target_id = '.$node->id());
$query->addField('app', 'entity_id', 'nid');

$query->orderBy('created', 'DESC');

But it returns me an error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 

Could you please tell me what I do wrong?

  • This is an SQL question rather than Drupal - you need to add selected columns to the group by clause
    – Clive
    Sep 10 '18 at 12:03
  • Maybe you can add the SQL that you want to translate to Drupal's DB layer. I cant see what you're trying to do. Sep 10 '18 at 12:25
  • It says exactly what you need to do, you need to add created column also into the grouping clause. Dec 14 '21 at 23:56

Try this out

use Drupal\Core\Database\Database;

$connection = Database::getConnection();
  //- Step one get all referenced nids
  $query = $connection->select('node_field_data', 'nfd')
    ->fields('nfd', ['nid', 'created']);
   $query->innerJoin('node__field_ils_apps', 'app', 'nfd.nid = app.entity_id');
  $query->orderBy('created', 'DESC');
  $result = $query->execute()->fetchAll(PDO::FETCH_ASSOC);

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.