Ambiguous column - Laravel 5.1

Question

Good morning! Here’s the thing... 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 I’m not getting the data out of them together, because they both have the columns id and nome, woe gives that mistake when I do a 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 that:

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 of 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 fix this?

Answer 1

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

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

Do:

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