Laravel - Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous in Laravel Filter

user_11352561 0
Viewed 3750

I am using Laravel 5.8 to filter by date and name. While I clicked on search, I got this error:

"SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in where clause is ambiguous (SQL: select count(*) as aggregate from services inner join cloudsubscriptions on services.name = cloudsubscriptions.service_name where created_at between 2019-05-31 and 2019-06-29 and name like %Learnersway% group by DATE(cloudsubscriptions.created_at), services.id, services.name) ◀"

Here is my code:

public function revenueReport(Request $request)
{
$data['title'] = 'Revenue Report';

$revenuereports = DB::table('cloudsubscriptions as c')
 ->select(
       'c.service_name', 
        DB::raw('COUNT(*) as total_users'), 
        DB::raw('SUM(c.amount) as total_amount'),
        DB::raw('DATE(c.created_at)as subscription_date') 
  ) 
 ->groupBy(DB::raw('DATE(c.created_at)'), 'service_name')                                                          
 ->orderByRaw('c.created_at DESC');

    $render=[];
    if(isset($request->start_date) && isset($request->end_date))
    {
        $revenuereports=$revenuereports->whereBetween('created_at',[$request->start_date,$request->end_date]);
        //$revenuedetails=$revenuedetails->whereBetween(date('users.created_at'),[$request->start_date,$request->end_date]);
        $render['start_date']=$request->start_date;
        $render['end_date']=$request->end_date;
    }elseif(isset($request->start_date))
    {
        $revenuereports=$revenuereports->where('created_at',$request->start_date);

        $render['start_date']=$request->start_date;
    }        
    if(isset($request->service_name))
    {
        $revenuereports=$revenuereports->where('service_name','like','%'.$request->service_name.'%');
        $render['service_name']=$request->service_name;
    }

    //dd($revenuedetails->toSql());
    $revenuereports= $revenuereports->orderBy('created_at','DESC');
    $revenuereports= $revenuereports->paginate(15);
    $revenuereports= $revenuereports->appends($render);
    $data['revenuereports'] = $revenuereports;

return view('report.revenueReport',$data);
}
laravel
2 Answers

specify from which table for example c.created_at or users.created_at

user_92662040

in your code you haven't specified which table created_at you want the filter so the issue is coming.

to solve issue you need to specify a created_at column with the table name.

Update to the following code, it will work.

public function revenueReport(Request $request)
{
$data['title'] = 'Revenue Report';

$revenuereports = DB::table('cloudsubscriptions as c')
 ->select(
       'c.service_name', 
        DB::raw('COUNT(*) as total_users'), 
        DB::raw('SUM(c.amount) as total_amount'),
        DB::raw('DATE(c.created_at)as subscription_date') 
  ) 
 ->groupBy(DB::raw('DATE(c.created_at)'), 'service_name')                                                          
 ->orderByRaw('c.created_at DESC');

    $render=[];
    if(isset($request->start_date) && isset($request->end_date))
    {
        $revenuereports=$revenuereports->whereBetween('subscription_date',[$request->start_date,$request->end_date]);
        //$revenuedetails=$revenuedetails->whereBetween(date('users.created_at'),[$request->start_date,$request->end_date]);
        $render['start_date']=$request->start_date;
        $render['end_date']=$request->end_date;
    }elseif(isset($request->start_date))
    {
        $revenuereports=$revenuereports->where('subscription_date',$request->start_date);

        $render['start_date']=$request->start_date;
    }        
    if(isset($request->service_name))
    {
        $revenuereports=$revenuereports->where('service_name','like','%'.$request->service_name.'%');
        $render['service_name']=$request->service_name;
    }

    //dd($revenuedetails->toSql());
    $revenuereports= $revenuereports->orderBy('subscription_date','DESC');
    $revenuereports= $revenuereports->paginate(15);
    $revenuereports= $revenuereports->appends($render);
    $data['revenuereports'] = $revenuereports;

return view('report.revenueReport',$data);
}
user_37825430
Related