laravel where 子句在查询上不明确

where clause is ambiguous on a query

提问by Mathieu Mourareau

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 :

我构建了一个搜索模块来从不同的参数中获取结果！它可以工作，但是当我何时将结果导出到 csv 时，我的连接表出现了问题。例如，当我使用 catg_licence_id 进行搜索时，我会遇到如下异常：

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

SQLSTATE[23000]：违反完整性约束：where 子句中的 1052 列“catg_licence_id”不明确

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 :)

这里我的控制器获取结果并生成带有连接表的文件，以从其他表中获取值而不是简单的 ids 。希望有人可以帮助我。非常感谢提前:)

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.fras 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_emailfrom licenciesinner join activite_licencieon activite_licencie.id= licencies.activite_licencie_idinner join saisonson saisons.id= licencies.saison_idinner join payson pays.id= licencies.pays_naissance_idinner join type_licenceon type_licence.id= licencies.type_licence_idinner join structureson structures.id= licencies.structure_idinner join civiliteon civilite.id= licencies.civilite_idinner join catg_licenceon catg_licence.id= licencies.catg_licence_idwhere type_licence_id= 4 and catg_licence_id= 1)

SQLSTATE [23000]：完整性约束违规：在where子句是不明确的（1052列'type_licence_id' SQL：选择 num_licence，civilite。lb_civilite，lb_nom，lb_prenom， dt_naissance，pays。fr如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从licencies内连接activite_licencie上 activite_licencie。id= licencies。activite_licencie_id内连接saisons上saisons。id= licencies。saison_id内连接 pays上pays。id= licencies。pays_naissance_id内连接 type_licence上type_licence。id= licencies. type_licence_id内连接structures上structures。id= licencies. structure_id内连接civilite上civilite。id= licencies. civilite_id内连接catg_licence上 catg_licence。id= licencies. catg_licence_id其中 type_licence_id= 4 和catg_licence_id= 1)

回答by abr

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

当它说它不明确时，这意味着 mysql 正在连接表并且在另一个表上找到该特定字段 (catg_licence_id)。因此，当您将某些内容加入该字段时，会发生什么情况，他不知道要加入哪个表。一个解决方案是将表名放在前面，就像@user3154557 刚刚说的那样

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

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

回答by justzach

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.

您也可能在选择中遇到相同的错误。当您加入一堆表时，最好将 table.property 放在选择中而不是属性中。