Search custom query between two parameters

Question

I have a problem with a query that i want to run when i make a search between two params ( date_min & date_max ).

In my controller i use a filter to make the search in different params and i added an orWhere condition to select the result between two dates but the result of the query is really strange :

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

Connection->runQueryCallback('select count(*) as aggregate from indemnites left join structures on structures.id = indemnites.club_id where club_id = ? or indemnites.created_at between ? and ? and created_at = ? or indemnites.created_at between ? and ? and created_at = ? or indemnites.created_at between ? and ? and total != ? group by club_id', array('6', '2018-03-12', '2018-03-31', '12-03-2018', '2018-03-12', '2018-03-31', '31-03-2018', '2018-03-12', '2018-03-31', 0), object(Closure))

Here is my controller :

public function index(Request $request){

$structure = Structure::select('num_structure', 'nom_structure' , 'id')
    ->where('type_structure_id' , '4')
    ->get()
    ->mapWithKeys(function($i) {
        return [$i->id => $i->num_structure.' - '.$i->nom_structure];
    });

$query = Indemnites::query();

$filters = [
    'structure' => 'club_id',
    'dt_min' =>'created_at',
    'dt_max' =>'created_at',
];


$dt_min = $request->input('dt_min');
$dt_min = Carbon::parse($dt_min)->format('Y-m-d');
$dt_max = $request->input('dt_max');
$dt_max = Carbon::parse($dt_max)->format('Y-m-d');


foreach ($filters as $key => $column) {


    $query->when($request->has($key), function ($query) use ($column,$key, $request , $dt_min , $dt_max)  {
        $query->where($column, $request->$key)
            ->orWhereBetween('rencontre.created_at' , [$dt_min , $dt_max]);

    });
}

$indemnites = $query->leftJoin('structures' , 'structures.id' , '=' ,  'indemnites.club_id')
    ->leftJoin('rencontre' , 'rencontre.id' , '=' , 'indemnites.rencontre_id')
    ->where('total' , '!=' , 0)
    ->selectRaw('sum(total) as total , structures.nom_structure as club , club_id')
    ->groupBy('club_id')->paginate(20);

dd($indemnites);

Where i'm doing wrong ? hope someone could help me

The problem seems to come from the last query who generate the result

When i change to $indemnites = $query->paginate(20); it's working but i need to make a sum in a raw.. hope someone could help

RESOLVED (i made a mistake on the column name)

 public function index(Request $request){

        $structure = Structure::select('num_structure', 'nom_structure' , 'id')
            ->where('type_structure_id' , '4')
            ->get()
            ->mapWithKeys(function($i) {
                return [$i->id => $i->num_structure.' - '.$i->nom_structure];
            });

        $query = Indemnites::query();

        $filters = [
            'structure' => 'club_id',
            'dt_min' =>'dt_rencontre',
            'dt_max' =>'dt_rencontre',
        ];


        $dt_min = $request->input('dt_min');
        $dt_min = Carbon::parse($dt_min)->format('Y-m-d');
        $dt_max = $request->input('dt_max');
        $dt_max = Carbon::parse($dt_max)->format('Y-m-d');


        foreach ($filters as $key => $column) {


            $query->when($request->has($key), function ($query) use ($column,$key, $request , $dt_min , $dt_max)  {
                $query->where($column, $request->$key)
                    ->orWhereBetween('rencontre.dt_rencontre' , [$dt_min , $dt_max]);

            });
        }

        $indemnites = $query->leftJoin('structures' , 'structures.id' , '=' ,  'indemnites.club_id')
            ->leftJoin('rencontre' , 'rencontre.id' , '=' , 'indemnites.rencontre_id')
            ->where('total' , '!=' , 0)
            ->selectRaw('sum(total) as total , structures.nom_structure as club , club_id , rencontre.dt_rencontre')
            //->orWhereBetween('created_at' , [$dt_min , $dt_max])
            ->groupBy('club_id')->paginate(20);




        return view('indemnites/index' , compact('indemnites' , 'structure'));

    }

Romantic Dev Romantic Dev · Accepted Answer · 2018-03-12T16:55:53

Your tables in which you have a relationships both have created_at column

    foreach ($filters as $key => $column) {


        $query->when($request->has($key), function ($query) use ($column,$key, $request , $dt_min , $dt_max)  {
            $query->where($column, $request->$key)
                ->orWhereBetween('tableName.created_at' , [$dt_min , $dt_max]);

        });
    }

It can be like this

 ->orWhereBetween('structures.created_at' , [$dt_min , $dt_max]);

Hope this helps

Search custom query between two parameters

1 Answers