0

For some reason, I must be missing something when I am trying to move my query from raw SQL to Laravel DB.

At the moment, with this SQL query:

SELECT id, COUNT(id) AS count FROM agent_billings_items WHERE agentBillingBatch = 224 AND shipmentID IS NOT NULL GROUP BY shipmentID HAVING COUNT(id) > 1;

I get three (3) results back with the count return set correctly. However, when I try and write this out using Laravel DB:

    $results =  DB::table('agent_billings_items')
        ->select('id', DB::raw('count(*) as count'))
        ->where('agentBillingBatch', $this->id)
        ->whereNotNull('shipmentID')
        ->whereNull('deleted_at')
        ->groupBy('shipmentID')
        ->havingRaw('count > 1')
        ->get();

I get nothing returned. I know the raw SQL query is correct as I have manually checked it, so I know the $results in Laravel should come back with something.

I'd appreciate any help. Thanks

//UPDATE WITH DD OF QUERY//

array:1 [▼
  0 => array:3 [▼
    "query" => "select `id`, count(*) as count from `agent_billings_items` where `agentBillingBatch` = ? and `shipmentID` is not null and `deleted_at` is null group by `shipmentID`, `id` having count > 1 ◀"
    "bindings" => array:1 [▼
      0 => 224
    ]
    "time" => 41.85
  ]
]

//UPDATE AFTER REMOVING id FROM GROUP BY

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'truckin.agent_billings_items.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: selectid, count(*) as count fromagent_billings_itemswhereagentBillingBatch= 224 andshipmentIDis not null anddeleted_atis null group byshipmentIDhaving count > 1)

inuShiva
  • 173
  • 6
  • 14
  • 1
    add this before the query: DB::enableQueryLog(); and this after: dd(DB::getQueryLog()); and then run the resulting query directly in the DB. See if the query is right first, then update the question so I can help you – Indra Feb 19 '19 at 15:32
  • which data type you used for "shipmentID" in database? – Vinod Joshi Feb 19 '19 at 15:36
  • @Indra - I've updated my question with the output you requested. – inuShiva Feb 19 '19 at 15:40
  • @VinodJoshi - It's an INT column – inuShiva Feb 19 '19 at 15:40
  • When you run this directly in the database do you get the expected result? – Indra Feb 19 '19 at 15:47
  • @Indra - I found that it was the id in my groupBy that is likely causing the issue, however, after removing it, I get the error code I have placed in my updated question. Do you happen to have any suggestions? – inuShiva Feb 19 '19 at 15:52
  • Yeah, it's the strict in mysql that's causing it. Without seeing the full database and understanding result you're trying to achieve it's difficult to work around this. However check this out https://stackoverflow.com/questions/40917189/laravel-syntax-error-or-access-violation-1055-error – Indra Feb 19 '19 at 15:58
  • I see, I've made the suggestions in the answer in the related question - thank you so much for pointing that out to me! – inuShiva Feb 19 '19 at 16:05

0 Answers0