title: convert mysql query to eloquent query issue
body: <p>I have build below query in mysql. and trying a lot to convert to laravel but not success.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I am trying to above query in to laravel datatabel query but it gives errors as below. </p>
  
  <p><strong>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, <code>composite_inventories</code>.<code>id</code>, <code>composite_inventories</code>.<code>purchase_item_name</code>, <code>composite_inventories</code>.<code>sales_item_name</code>, <code>us</code>.<code>type</code> from <code>composite_inventories</code> left join <code>composite_has_inventories</code> as <code>us</code> on <code>composite_inventories</code>.<code>id</code> = <code>us</code>.<code>composite_inventory_id</code> left join <code>inventories</code> as <code>s</code> on <code>US</code>.<code>inventory_id</code> = <code>s</code>.<code>id</code> and <code>us</code>.<code>type</code> = 'sale' left join <code>inventories</code> as <code>p</code> on <code>US</code>.<code>inventory_id</code> = <code>p</code>.<code>id</code> and <code>us</code>.<code>type</code> = 'purchase' group by <code>composite_inventories</code>.<code>id</code>)</strong></p>
  
  <p>I have tried below laravel query.</p>
  
  <pre><code>$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'
          ])-&gt;leftJoin('composite_has_inventories as us', 'composite_inventories.id', '=', 'us.composite_inventory_id')
              -&gt;leftJoin('inventories as s', function($join)
                   {
                      $join-&gt;on('US.inventory_id', '=', 's.id');
                      $join-&gt;on('us.type','=',DB::raw("'sale'"));
                   })
              -&gt;leftJoin('inventories as p', function($join)
                   {
                      $join-&gt;on('US.inventory_id', '=', 'p.id');
                      $join-&gt;on('us.type','=',DB::raw("'purchase'"));
                   })
          -&gt;groupBy('composite_inventories.id')-&gt;get();
  </code></pre>
  
  <p>I just tried below query </p>
  
  <pre><code>$row = DB::table('composite_inventories as u')-&gt;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')
          ])-&gt;leftJoin('composite_has_inventories as us', 'u.id', '=', 'us.composite_inventory_id')
              -&gt;leftJoin('inventories as s', function($join)
                   {
                      $join-&gt;on('US.inventory_id', '=', 's.id');
                      $join-&gt;on('us.type','=',DB::raw("'sale'"));
                   })
              -&gt;leftJoin('inventories as p', function($join)
                   {
                      $join-&gt;on('US.inventory_id', '=', 'p.id');
                      $join-&gt;on('us.type','=',DB::raw("'purchase'"));
                   })
          -&gt;groupBy('u.id', 'u.purchase_item_name','u.sales_item_name');
  </code></pre>
  
  <p>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 </p>
  
  <blockquote>
    <p>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 <code>u</code>.<code>id</code>, <code>u</code>.<code>purchase_item_name</code>, <code>u</code>.<code>sales_item_name</code>, GROUP_CONCAT(p.itemcode) as purchase_items, GROUP_CONCAT(s.itemcode) as sales_items from <code>composite_inventories</code> as <code>u</code> left join <code>composite_has_inventories</code> as <code>us</code> on <code>u</code>.<code>id</code> = <code>us</code>.<code>composite_inventory_id</code> left join <code>inventories</code> as <code>s</code> on <code>US</code>.<code>inventory_id</code> = <code>s</code>.<code>id</code> and <code>us</code>.<code>type</code> = 'sale' left join <code>inventories</code> as <code>p</code> on <code>US</code>.<code>inventory_id</code> = <code>p</code>.<code>id</code> and <code>us</code>.<code>type</code> = 'purchase' where (LOWER(<code>composite_inventories</code> as <code>u.purchase_item_name</code>) LIKE %a% or LOWER(<code>composite_inventories</code> as <code>u.purchase_items</code>) LIKE %a% or LOWER(<code>composite_inventories</code> as <code>u.sales_item_name</code>) LIKE %a% or LOWER(<code>composite_inventories</code> as <code>u.sales_items</code>) LIKE %a%) group by <code>u</code>.<code>id</code>, <code>u</code>.<code>purchase_item_name</code>, <code>u</code>.<code>sales_item_name</code>) count_row_table)</p>
  </blockquote>