$LastWeek = DB::table('group')
->where('group.region','=',$regionName)
->whereBetween('date',[$date1, $date2])
->join('sale_number', 'group.sales_person_id', '=', 'sale_number.id')
->selectRaw("sale_number.id,SUM(number) as sum_prev_week")
->groupBy('sale_number.id')
->get();
group table
region,
sales_person_id
sale_number table
id,
date
number
Now I am using the above query work fine.
I am trying to use Laravel hasMany model.
class Group extends Model
{
use HasFactory;
public function sale_number()
{
return $this->hasMany(SaleNumber::class,'id','sales_person_id');
}
$lastWeek = Group::where('group.region','=',$regionName)
->with('sale_number')
->selectRaw("sale_number.channel,SUM(number) as sum_prev_week")
->groupBy('sale_number.id')
->get();
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sale_number.id' in 'field list'
SELECT
sale_number.id,
SUM(number) AS sum_prev_week
FROM
`group`
WHERE
`group`.`region` = Midwest
GROUP BY
`sale_number`.`id`