Contrib.social

Cannot select multi fields when using group by in the default configuration of MySQL

Open on Drupal.org →
Created on 1 August 2018, over 6 years ago
Updated 13 March 2023, almost 2 years ago

Hello,

I'm not able to select more than one field when using groupBy.

Example:

This Works

    $query = $this->database->select('taxonomy_term_field_data', 'ttdf');
    $query->fields('ttdf', ['tid'])
      ->groupBy('ttdf.tid');

    $data = $query->execute();
    $results = $data->fetchAll();

This doesn't work:

    $query = $this->database->select('taxonomy_term_field_data', 'ttdf');
    $query->fields('ttdf', ['tid', 'name'])
      ->groupBy('ttdf.tid');

    $data = $query->execute();
    $results = $data->fetchAll();

ERROR:

The website encountered an unexpected error. Please try again later.
Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1055 database.ttdf.name isn't in GROUP BY: SELECT ttdf.tid AS tid, ttdf.name AS name
FROM 
{taxonomy_term_field_data} ttdf
GROUP BY ttdf.tid; Array
(
)

This works:
(Adding the other field to the groupby)

    $query = $this->database->select('taxonomy_term_field_data', 'ttdf');
    $query->fields('ttdf', ['tid', 'name'])
      ->groupBy('ttdf.tid, ttdf.name');

    $data = $query->execute();
    $results = $data->fetchAll();

What if we need to select multiple fields. Are we required to add them all to the groupBy?

NOTE: If I execute a query againts mysql, I can achieve selecting all desired fields without having to include all of them in the groupBy. So this is not a MySQL limitation.

Thanks in advance.

💬 Support request
Status

Active

Version

9.5

Component
Database  →

Last updated 3 days ago

  • Maintained by
  • 🇳🇱Netherlands @daffie
Created by

🇦🇷Argentina sharif.elshobkshy

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • Comment almost 2 years ago →
    🇺🇸United States Clarentina

    I experienced the same issue on D9.5 even with the addition of #6. This worked fine on D9.4.

    After a bit of debugging, I was able to solve this issue by adding each groupBy field separately to the query.

    For example,

    $query->groupBy('ttdf.tid, ttdf.name');

    now became,

    $query->groupBy('ttdf.tid');
$query->groupBy('ttdf.name');
  • Status changed to Postponed: needs info 6 months ago
  • Comment 6 months ago →
    🇳🇿New Zealand quietone

    This has been closed as works as designed (by the current subsystem maintainer) and has been a bug report and a support request. As a support request, the Drupal Core issue queue is not the ideal place for support. There are several support options listed on our support page → (Community > Support at the top of Drupal.org) and there is Drupal Slack → . Drupal Slack and the Forums, which are our two main support mechanisms in the Drupal community.

    Shall we close this so the discussion can happen in a more suitable place? If we don't receive additional information to help with the issue, it may be closed after three months.

  • Status changed to Closed: outdated 30 days ago
  • Comment 30 days ago →
    🇳🇿New Zealand quietone

    Closing per the previous comment.

    If you are experiencing this problem on a supported version of Drupal reopen the issue, by setting the status to 'Active', and provide complete steps to reproduce the issue → starting from "Install Drupal core".

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024