Query replaces id of main table

Question

I am trying to perform a query in the table "expenses" that has foreign key.

When performing the query. The "id" column of the "expenses" table is replaced by the "id" of "dismisse_categories"

I am doing the consultation as follows:

$despesas= Despesa::query()
        ->where('id_user', '=', "$usuario->id")
        ->join('despesa_categorias', 'despesas.id_des_cat', '=', 'despesa_categorias.id')
        ->whereYear('data_referencia', '=', "$ano")
        ->whereMonth('data_referencia', '=', "$mes")
        ->get();

My return is:

{"id":3,"id_user":1,"id_des_cat":3,"valor":42.9,"descricao":"teste","data_referencia":"2020-07-15","created_at":null,"updated_at":null,"categoria":"energia"}

Where "id" is coming from the "fare_categories" table. How can I keep the "id" of the "expenses" table when performing the query?

Answer 1

I managed to solve the problem. Even though it’s kind of obvious, it’s still something that made me spend a lot of time trying to figure it out.

As the two tables had the column with the same name the column "id" ended up being replaced, to solve I did as follows.

$despesas= Despesa::query()
        ->where('id_user', '=', "$usuario->id")
        ->join('despesa_categorias', 'despesas.id_des_cat', '=', 'despesa_categorias.id')
        ->select('despesas.*', 'despesa_categorias.categoria')
        ->whereYear('data_referencia', '=', "$ano")
        ->whereMonth('data_referencia', '=', "$mes")
        ->get();

Using select I was able to select all columns of the main table, including the id and bring to feathers the "category" column of the table "dismisse_categories".