convert mysql query to eloquent query issue

Question

I have build below query in mysql. and trying a lot to convert to laravel but not success.

SELECT u.id, u.purchase_item_name,u.sales_item_name, us.type,
GROUP_CONCAT(p.itemcode) AS purchase_items,
GROUP_CONCAT(s.itemcode) as sales_items 
FROM composite_inventories as u 
LEFT JOIN composite_has_inventories as us ON u.id = us.composite_inventory_id 
LEFT JOIN inventories as s ON US.inventory_id = s.id AND us.type='sale'
LEFT JOIN inventories as p ON US.inventory_id = p.id AND us.type='purchase'
GROUP BY u.id

I am trying to above query in to laravel datatabel query but it gives errors as below.

SQLSTATE[42000]: Syntax error or access violation: 1055 'saas.composite_inventories.purchase_item_name' isn't in GROUP BY (SQL: select GROUP_CONCAT(p.itemcode) as purchase_items, GROUP_CONCAT(s.itemcode) as sales_items, composite_inventories.id, composite_inventories.purchase_item_name, composite_inventories.sales_item_name, us.type from composite_inventories left join composite_has_inventories as us on composite_inventories.id = us.composite_inventory_id left join inventories as s on US.inventory_id = s.id and us.type = 'sale' left join inventories as p on US.inventory_id = p.id and us.type = 'purchase' group by composite_inventories.id)

I have tried below laravel query.

$result = Compositeinventory::select([
            DB::raw('GROUP_CONCAT(p.itemcode) as purchase_items'),
            DB::raw('GROUP_CONCAT(s.itemcode) as sales_items'),
            'composite_inventories.id',
            'composite_inventories.purchase_item_name',
            'composite_inventories.sales_item_name',
            'us.type'
        ])->leftJoin('composite_has_inventories as us', 'composite_inventories.id', '=', 'us.composite_inventory_id')
            ->leftJoin('inventories as s', function($join)
                 {
                    $join->on('US.inventory_id', '=', 's.id');
                    $join->on('us.type','=',DB::raw("'sale'"));
                 })
            ->leftJoin('inventories as p', function($join)
                 {
                    $join->on('US.inventory_id', '=', 'p.id');
                    $join->on('us.type','=',DB::raw("'purchase'"));
                 })
        ->groupBy('composite_inventories.id')->get();

I just tried below query

$row = DB::table('composite_inventories as u')->select([
            'u.id',
            'u.purchase_item_name',
            'u.sales_item_name',
            DB::raw('GROUP_CONCAT(p.itemcode) as purchase_items'),
            DB::raw('GROUP_CONCAT(s.itemcode) as sales_items')
        ])->leftJoin('composite_has_inventories as us', 'u.id', '=', 'us.composite_inventory_id')
            ->leftJoin('inventories as s', function($join)
                 {
                    $join->on('US.inventory_id', '=', 's.id');
                    $join->on('us.type','=',DB::raw("'sale'"));
                 })
            ->leftJoin('inventories as p', function($join)
                 {
                    $join->on('US.inventory_id', '=', 'p.id');
                    $join->on('us.type','=',DB::raw("'purchase'"));
                 })
        ->groupBy('u.id', 'u.purchase_item_name','u.sales_item_name');

above query works when display data table. but when i try to search filter it gives below error because of purchase_items and sales_items field are not present in database but that are just aliases. error is

Exception Message:↵↵SQLSTATE[42000]: Syntax error or access violation: 1583 Incorrect parameters in the call to native function 'LOWER' (SQL: select count as aggregate from (select u.id, u.purchase_item_name, u.sales_item_name, GROUP_CONCAT(p.itemcode) as purchase_items, GROUP_CONCAT(s.itemcode) as sales_items from composite_inventories as u left join composite_has_inventories as us on u.id = us.composite_inventory_id left join inventories as s on US.inventory_id = s.id and us.type = 'sale' left join inventories as p on US.inventory_id = p.id and us.type = 'purchase' where (LOWER(composite_inventories as u.purchase_item_name) LIKE %a% or LOWER(composite_inventories as u.purchase_items) LIKE %a% or LOWER(composite_inventories as u.sales_item_name) LIKE %a% or LOWER(composite_inventories as u.sales_items) LIKE %a%) group by u.id, u.purchase_item_name, u.sales_item_name) count_row_table)

M Khalid Junaid · Accepted Answer · 2018-04-20 09:16:33Z

3

This isn't a laravel's issue your query is invalid because its contains some columns which are not aggregated and not mentioned in group clause. To correct this you need to update your group by clause as

group by u.id, u.purchase_item_name,u.sales_item_name, us.type

In query builder

->groupBy('composite_inventories.id', 'composite_inventories.purchase_item_name','composite_inventories.sales_item_name', 'composite_has_inventories.type')

If there are multiple type(s) per composite_inventories then i guess you need GROUP_CONCAT for this also and remove this column from group by clause.

Update for syntax error, you are not using lower function correctly just pass your column name for which you want to convert the value to lower case like

WHERE (
      LOWER(u.purchase_item_name) LIKE '% a %'
      OR LOWER(u.purchase_items) LIKE '% a %'
      OR LOWER(u.sales_item_name`) LIKE '% a %' 
      OR LOWER(u.sales_items) LIKE '% a %'
)

If your database collations are not set to case sensitive then there is no need to use lower() function for more information have a look at B.5.4.1 Case Sensitivity in String Searches

Also if you are using mysql fucntions in query you might need to raw() method of laravel's query builder

edited Apr 20, 2018 at 9:16

answered Apr 20, 2018 at 5:47

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Code Embassy Over a year ago

when display databse it works but if i try to search filter it gives error : "Exception Message:↵↵SQLSTATE[42000]: Syntax error or access violation: 1583 Incorrect parameters in the call to native function 'LOWER' (SQL: select count as aggregate from (select u.id, u.purchase_item_name, u.sales_item_name, GROUP_CONCAT(p.itemcode) as purchase_items, GROUP_CONCAT(s.itemcode) as sales_items from composite_inventories.........

M Khalid Junaid Over a year ago

@CodeEmbassy update your question and add complete query which produces above error

Code Embassy Over a year ago

i have updated question with complete detail with error.

Code Embassy Over a year ago

hello i have check your updated answer but that will not work. it is not issue of lower function. lower function is by default generated when laravel fires query. issue is because purchase_iteams and sales_items ares aliases and we are using datatable search on that thats why it gives error.

Code Embassy Over a year ago

we need to use having caluse in filterColumn but i have tried but it is not working. may be i do not know exactly how ti put two fields in one having caluse.

|

Sylar · Accepted Answer · 2023-03-27 13:02:53Z

0

I have come across the same problem and after a lot of digging, there must be some conflict with the datatable component of yajra, the solution is to convert the query from eloquent to query builder and problem solved.

answered Mar 27, 2023 at 13:02

Sylar

3052 silver badges20 bronze badges

Collectives™ on Stack Overflow

convert mysql query to eloquent query issue

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related