Question 55652 in Laravel Question posted in
The official Laravel Documentation can be found here.

SQL query works in mySQL, borks in Laravel query builder

I have a SQL SELECT query that works in mySQL, both from PHPmyadmin and from the console. However, when it was converted to Laravel query builder, it borked with the following error message:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not
in GROUP BY clause and contains nonaggregated column 'wcce_dev.client_contacts.family_name'
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with 
sql_mode=only_full_group_by

The following is the SQL SELECT query:

SELECT `clients`.name, `clients`.address_1, `client_contacts`.family_name,
  `client_contacts`.given_name, `client_contacts`.office_country,
  `client_contacts`.office_city, `client_contacts`.office_number, `client_contacts`.position, `contact_titles`.description
FROM `clients`
INNER JOIN `client_contacts`
ON `clients`.entry = `client_contacts`.client
INNER JOIN `contact_titles`
ON `client_contacts`.title = `contact_titles`.code
GROUP BY `clients`.entry
ORDER BY `clients`.entry ASC;

The following is the Laravel query builder statement:

DB::table('clients')
->select('clients.name', 'clients.address_1', 'client_contacts.family_name',
  'client_contacts.given_name', 'client_contacts.office_country', 
  'client_contacts.office_city', 'client_contacts.office_number', 
  'client_contacts.position', 'contact_titles.description')
->join('client_contacts','clients.entry','=','client_contacts.client')
->join('contact_titles','client_contacts.title','=','contact_titles.code')
->groupBy('clients.entry')
->orderBy('clients.entry','asc')
->get();

It’s very strange. Any help is greatly appreciated.

Tags:

2

Answers


  1. 0

    The problem is that your related table client_contacts can have more than one row which corresponds to a row in the clients table. Even if it won’t happen in reality, the db doesn’t know that and throws an error.

    You have two options

    1. Turn off sql_mode=only_full_group_by so that it can pick one row at random and display that
    2. Precede each aggregate column with MAX() so that it just gets one of them.

    The resulting query would look like this:

    SELECT `clients`.name, `clients`.address_1, 
  MAX(`client_contacts`.family_name),
  MAX(`client_contacts`.given_name), 
  MAX(`client_contacts`.office_country),
  MAX(`client_contacts`.office_city), 
  MAX(`client_contacts`.office_number),   
  MAX(`client_contacts`.position), 
  MAX(`contact_titles`.description)
FROM `clients`
INNER JOIN `client_contacts`
ON `clients`.entry = `client_contacts`.client
INNER JOIN `contact_titles`
ON `client_contacts`.title = `contact_titles`.code
GROUP BY `clients`.entry
ORDER BY `clients`.entry ASC;
    Login or Signup to reply.
  2. 0

    Try this command in mysql, phpmyadmin then try

    SET GLOBAL sql_mode=(SELECT CONCAT(@@sql_mode, ‘,ONLY_FULL_GROUP_BY’));

    And if you doing group by then group by column must be in select query
    "’clients.entry’"

    DB::table('clients')
->select('clients.name', 'clients.address_1', 'client_contacts.family_name',
  'client_contacts.given_name', 'client_contacts.office_country', 
  'client_contacts.office_city', 'client_contacts.office_number', 
  'client_contacts.position', 'contact_titles.description', 'clients.entry')
->join('client_contacts','clients.entry','=','client_contacts.client')
->join('contact_titles','client_contacts.title','=','contact_titles.code')
->groupBy('clients.entry')
->orderBy('clients.entry','asc')
->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search