I am new to laravel. I have three tables in my DB

1. dsp_account with fields id,name_display,short_name.

2. dsp with fields id,dsp_account_id.

.

3. rtbcfg_region_dsp with fields dsp_id,status.

.

dsp table connected to dsp_account table via dsp_account_id field.

dsp table connected to rtbcfg_region_dsp via dsp_id.

I try to create this sql query:

select distinct dsp_account.id,dsp_account.name_display,dsp_account.short_name
from dsp_account
inner join dsp on dsp.dsp_account_id = dsp_account.id
AND dsp.id 
IN(SELECT dsp_id
FROM rtbcfg_region_dsp
group by dsp_id, status
having count(*)>0 and status = 1) order by dsp_account.name_display ASC;

this is my code:

$all_list6 = DspAccount::select('DspAccount.id', 'DspAccount.name_display','DspAccount.short_name')
            ->join('dsp', 'dsp.dsp_account_id', '=', 'dspAccount.id')
            ->whereIn('DSP.id',function($query)
            {
                $query->select('dsp_id')
                ->from('rtbcfg_region_dsp')
                ->groupBy('dsp_id','status')
                ->havingRaw('COUNT(*) > 0 AND status = 1');
        
            })
            ->get();

this is the error that I got: what I am missing:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'DspAccount.id' in 'field list' (SQL: select `DspAccount`.`id`, `DspAccount`.`name_display`, `DspAccount`.`short_name` from `dsp_account` inner join `dsp` on `dsp`.`dsp_account_id` = `dspAccount`.`id` where `DSP`.`id` in (select `dsp_id` from `rtbcfg_region_dsp` group by `dsp_id`, `status` having COUNT(*) > 0 AND status = 1) and `dsp_account`.`deleted_at` is null) 

CodePudding user response：

$all_list6 = DspAccount::select('dsp_account.id', 'dsp_account.name_display','dsp_account.short_name')
        ->join('dsp', 'dsp.dsp_account_id', '=', 'dsp_account.id')
        ->whereIn('dsp.id',function($query)
        {
            $query->select('dsp_id')
            ->from('rtbcfg_region_dsp')
            ->groupBy('dsp_id','status')
            ->havingRaw('COUNT(*) > 0 AND status = 1');
    
        })
        ->distinct()->get();

You should use original table name dsp_account instead of DspAccount as it is model class name. I haven't debug the query but i answered about only syntax error. Thanks:)