SQLSTATE[42S22]: Column not found: 1054 Unknown column 'claimtrc' in 'having clause'

user_14880857 0
Viewed 174

I'm running into a weird problem when using laravel eloquent and I can't find a way to solve it. I'm running laravel 5.6

The error I'm getting is

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'claimtrc' in 'having clause' (SQL: select count(*) as aggregate from `claims` where `claims`.`id` not in (select `claim_id` from `claim_tracking` where `claim_tracking`.`claim_id` = claims.id or `claim_tracking`.`type` = Finished or `claim_tracking`.`type` = Brake) group by `claims`.`id` having `claimtrc` >= 1)

The code I'm running is:

$claimsPostponed = Claim::select(DB::raw('*, (SELECT count(*) FROM claim_tracking WHERE claim_tracking.claim_id=claims.id) as claimtrc'))
            ->wherenotin('claims.id', function ($query) {
                $query->select('claim_id')
                    ->from('claim_tracking')
                    ->where('claim_tracking.claim_id', '=', 'claims.id')
                    ->orwhere('claim_tracking.type', 'Finished')
                    ->orwhere('claim_tracking.type', 'Brake');
            })
            ->groupBy('claims.id')
            ->having('claimtrc', '>=', 1)->count();

It works fine like this and I can get the count:

$claimsPostponed = Claim::select(DB::raw('*, (SELECT count(*) FROM claim_tracking WHERE claim_tracking.claim_id=claims.id) as claimtrc'))
            ->wherenotin('claims.id', function ($query) {
                $query->select('claim_id')
                    ->from('claim_tracking')
                    ->where('claim_tracking.claim_id', '=', 'claims.id')
                    ->orwhere('claim_tracking.type', 'Finished')
                    ->orwhere('claim_tracking.type', 'Brake');
            })
            ->groupBy('claims.id')
            ->having('claimtrc', '>=', 1)->get();
        $claimsPostponed = count($claimsPostponed);

But I don't think this way is efficient because it's loading all the results into an array then counting the array.

eloquent laravel php
2 Answers

Why dont you try WhereRaw instead of Having from a Select

Something like this:

    $claimsPostponed = Claim::wherenotin('claims.id', function ($query) {
                    $query->select('claim_id')
                        ->from('claim_tracking')
                        ->where('claim_tracking.claim_id', '=', 'claims.id')
                        ->orwhere('claim_tracking.type', 'Finished')
                        ->orwhere('claim_tracking.type', 'Brake');
                })
                ->WhereRaw("(SELECT count(*) FROM claim_tracking WHERE claim_tracking.claim_id=claims.id) >= 1")
                ->groupBy('claims.id')
                ->count();

You can try something like this and let me know if that works for you.

Also keep in mind you dont need the Select, since Count will only do "SELECT COUNT(*)" anyway

Other aproach could be using Joins or WhereHas could be even better.

Asuming claim_tracking is a relation, should be something like this:

    $claimsPostponed = Claim::select('*')
                ->whereHas('claim_tracking',function($q){
                    $q->whereIn('type', ['Finished', 'Brake']);
                })
                ->WhereRaw("(SELECT count(*) FROM claim_tracking WHERE claim_tracking.claim_id=claims.id) >= 1")
                ->groupBy('claims.id')
                ->count();
user_19153600

I think it may be work

$claimsPostponed = Claim::select(DB::raw('*, (SELECT count(*) as claimtrc FROM 
claim_tracking WHERE claim_tracking.claim_id=claims.id)'))
            ->wherenotin('claims.id', function ($query) {
                $query->select('claim_id')
                    ->from('claim_tracking')
                    ->where('claim_tracking.claim_id', '=', 'claims.id')
                    ->orwhere('claim_tracking.type', 'Finished')
                    ->orwhere('claim_tracking.type', 'Brake');
            })
            ->groupBy('claims.id')
            ->having('claimtrc', '>=', 1)->count();
Admin1
Related