0

My table have these data:

ID  | company_id   | code   | description
01  |  NULL        | CD01   | Standard description CD01
02  |  NULL        | XYZU   | Standard description XYZU
03  |  1           | CD01   | Custom description CD01 for company 1
04  |  2           | CD01   | Custom description CD01 for company 2

I need to extract all 'code' from this table but showing a single product code only once.

If exists a record witht company_id <> '' I show that, instead if doesn't exists I show the record with standard description.

Starting from the sample data, if I wanted to show the articles for the company_id = 1, I expect to have this output:

ID   | company_id   | code   | description   
02   |  NULL        | XYZU   | Standard description XYZU
03   |  1           | CD01   | Custom description CD01 for company 1

Have you got some suggest to do it?

Thank you

Roberto Remondini
  • 242
  • 1
  • 6
  • 19
  • have you tried Records::whereNull('company_id')->orWhere('company_id',$companyId)->groupBy('code')->get(); – pseudoanime Jan 15 '18 at 16:55
  • I've tried just now but ths system returns an SQL error: "1055 Expression #1 of SELECT list is not in GROUP by clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Roberto Remondini Jan 15 '18 at 17:27

1 Answers1

2

for removing duplicate entries from database results by group by you should disable ONLY_FULL_GROUP_BY for mysql. don't do that in mysql and don't disable strict mode! laravel itself sets some modes.

this is the overall solution for disabling this mode.

but in laravel you should also try another thing:

go to YouProjectFolder/vendor/laravel/framework/src/Illuminate/Database/Connectors/MySqlConnector.php

at the end of the file fin strict_mode() function and just remove ONLY_FULL_GROUP_BY fr0m the string within the function. (i just saw this solution in a stackoverflow post. unfortunately i didn't find that post)

Amin.Qarabaqi
  • 661
  • 7
  • 19