Ambiguous column - Laravel 5.1

Question

Ambiguous column - Laravel 5.1

Navigation

#1 by (4 votes)

3

Good morning! It's the following ... I have these 2 tables in my postgresql database:

tipos_risco
--id
--nome

agentes_risco
--id
--nome
--id_tipo_risco (foreign key)

The problem is that I am not able to extract the data from them together, since both have the columns id and nome , then that error when I search:

QueryException in Connection.php line 651:
SQLSTATE[42702]: Ambiguous column: 7 ERROR: column reference "nome" is ambiguous
LINE 1: ...sco"."id" = "agentes_risco"."id_tipo_risco" where "nome" ILI...
^ (SQL: select count(*) as aggregate from "agentes_risco" left join "tipos_risco" on "tipos_risco"."id" = "agentes_risco"."id_tipo_risco" where "nome" ILIKE %frio%)

and this:

PDOException in Connection.php line 321:
SQLSTATE[42702]: Ambiguous column: 7 ERROR: column reference "nome" is ambiguous
LINE 1: ...sco"."id" = "agentes_risco"."id_tipo_risco" where "nome" ILI...

This is the query for my search function:

return DB::table('agentes_risco')
                ->leftJoin('tipos_risco', 'tipos_risco.id', '=', 'agentes_risco.id_tipo_risco')
                ->where('nome', 'ILIKE', '%'.$busca.'%')
                ->select('agentes_risco.*','tipos_risco.*')
                ->orderBy('id', 'asc')
                ->paginate(10);

How can I resolve this?

postgresql laravel laravel-5

asked by anonymous 06.01.2016 / 13:50

1 answer

Initial Value Sequences Postgresql - Laravel 5.1 Develop for x86 and x64 platforms

score 4 · Accepted Answer

The problem is in the ordering, both tables have a field called id , then specify by which id to be ordered, the same applies to the field nome there in the like clause.

->orderBy('id', 'asc') // errado
->where('nome', 'ILIKE', '%'.$busca.'%') //errado

Make:

->orderBy('tipos_risco.id', 'asc')
->where('tipos_risco.nome', 'ILIKE', '%'.$busca.'%')