where clause is ambiguous on a query

Question

I built a search module to get results form different params ! it"s work but when i when to export the result in csv i'm getting problems with my join table. for exemple when i search with a catg_licence_id i get an exception like :

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'catg_licence_id' in where clause is ambiguous

here my controller to get the result and generate the file with the join tables to get the value from the other tables and not simple ids . hope someone could help me. thanks a lot in advance :)

public function exportLicencesExcelWithParam(Request $request){

        $type_licence = Type_licence::pluck('lb_type' , 'id');
        $activite = ActiviteLicencie::pluck('lb_activite' , 'id');
        $catg_licence = CatgLicence::pluck('lb_catg_lic' , 'id');
        $structure = Structure::select('num_structure', 'nom_structure' , 'id')
            ->get()
            ->mapWithKeys(function($i) {
                return [$i->id => $i->num_structure.' - '.$i->nom_structure];
            });


        $query = Licencies::query();

        $filters = [

            'type_licence' => 'type_licence_id',
            'activite_licencie' => 'activite_licencie_id',
            'assurance' => 'lb_assurance_etat',
            'catg_licence' => 'catg_licence_id',
            'structure' => 'structure_id',

        ];


        foreach ($filters as $key => $column) {
            if ($request->has($key)) {
                $query->where($column, $request->{$key});

            }
        }

        $action = Input::get('action', 'none');

        if($action =='send'){

//HERE I WANT TO GENERATE THE CSV FILE BUT I NEED TO GET THE JOIN TABLES TO DISPLAY THE RESULT 

            $licencies = $query->join('activite_licencie', 'activite_licencie.id', '=', 'licencies.activite_licencie_id')
                ->join('saisons', 'saisons.id', '=', 'licencies.saison_id')
                ->join('pays', 'pays.id', '=', 'licencies.pays_naissance_id')
                ->join('type_licence', 'type_licence.id', '=', 'licencies.type_licence_id')
                ->join('structures', 'structures.id', '=', 'licencies.structure_id')
                ->join('civilite', 'civilite.id', '=', 'licencies.civilite_id')
                ->join('catg_licence', 'catg_licence.id', '=', 'licencies.catg_licence_id')
                ->select('num_licence', 'civilite.lb_civilite', 'lb_nom', 'lb_prenom', 'dt_naissance', 'pays.fr as pays', 'activite_licencie.lb_activite', 'catg_licence.lb_catg_lic', 'type_licence.lb_type', 'saisons.lb_saison', 'lb_surclassement', 'structures.nom_structure', 'structures.num_structure', 'lb_assurance', 'cd_dept_naissance', 'lb_ville_naissance', 'lb_adresse', 'tel_fix_licencie', 'tel_port_licencie', 'adresse_email')
                ->get();


            $licencies->map(function ($licencie) {

                $licencie['dt_naissance'] = \Carbon\Carbon::parse($licencie['dt_naissance'])->format('d/m/Y');

                $licencie['lb_nom'] = strtoupper($licencie['lb_nom']);

                $licencie['lb_prenom'] = ucfirst(strtolower($licencie['lb_prenom']));

                if ($licencie['num_structure'] == 662883) {

                    $licencie['lb_activite'] = 'Super League';

                } elseif ($licencie['num_structure'] == 311197) {

                    $licencie['lb_activite'] = 'ChampionShip';


                } else {

                    //do nothing
                }

                if ($licencie['lb_activite'] == 'Tricolore LER' or $licencie['lb_activite'] == 'Tricolore - Autres Divisions') {

                    $licencie['lb_activite'] = 'Tricolore';

                }


                if ($licencie['lb_type'] == 'Membre') {


                    $licencie['lb_catg_lic'] = '';

                }

                return $licencie;

            });

            $date = Carbon::now('Europe/Paris')->format('d-m-Y h:m:s');

            $file = Excel::create('' . $date . '', function ($excel) use ($licencies) {
                $excel->sheet('Excel', function ($sheet) use ($licencies) {
                    $sheet->fromArray($licencies);

                });

            })->string('csv');

            Storage::disk('local')->put('licencies_export_'.$date.'.csv' , $file);

            return back()->with('status', "Fichier Exporté");

        }else{




        }


        return view('export/licences' , compact('type_licence' , 'structure' , 'structures' , 'licencies' , 'activite' , 'catg_licence'));

}

here the full exception:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'type_licence_id' in where clause is ambiguous (SQL: select num_licence, civilite.lb_civilite, lb_nom, lb_prenom, dt_naissance, pays.fr as pays, activite_licencie.lb_activite, catg_licence.lb_catg_lic, type_licence.lb_type, saisons.lb_saison, lb_surclassement, structures.nom_structure, structures.num_structure, lb_assurance, cd_dept_naissance, lb_ville_naissance, lb_adresse, tel_fix_licencie, tel_port_licencie, adresse_email from licencies inner join activite_licencie on activite_licencie.id = licencies.activite_licencie_id inner join saisons on saisons.id = licencies.saison_id inner join pays on pays.id = licencies.pays_naissance_id inner join type_licence on type_licence.id = licencies.type_licence_id inner join structures on structures.id = licencies.structure_id inner join civilite on civilite.id = licencies.civilite_id inner join catg_licence on catg_licence.id = licencies.catg_licence_id where type_licence_id = 4 and catg_licence_id = 1)

Answer 1

When it says it's ambiguous, what it means is that the mysql is joining tables and that specific field (catg_licence_id) is found on another table. So what happens is when you're joining something to this field, he doesn't know what table to join with. A solution would be to place the table name before, something like @user3154557 just said

->join('tablename', 'tablename.field', 'othertablename.field')

Answer 2

You're not joining the 'licencies' table anywhere.

->join('catg_licence', 'catg_licence.id', '=', 'licencies.catg_licence_id')

That line is your problem.

You might also get the same error in your select. It's better to put the table.property in the select rather than the property when you're joining a bunch of tables.