laravel join query return two identical ids for two different data

user_11824731 0
Viewed 483

I'm not sure if this is a bug in laravel or im doing something wrong.

I have two tables clothes and clothes_category.

The SQL I have is very simple I select all from clothes and join clothes_category where clothes.category_id maps with clothes_category.id. This should return only clothes that have mapped with clothes_categor.id. Simple and easy.

Laravel for some weird reason instead of returning the clothes id. its returning the clothes_category id, therefore all the data found in clothes are returned with the clothes_category.id.

Data returned:

[
{
  "id":1,
  "name":"clothes 1", NOTICE this shows that the clothes data is actually different
  "category_id":1,
  "category":"hoodie",
},
{
  "id":1,
  "name":"clothes10", NOTICE this shows that the clothes data is actually different
  "category_id":1,
  "category":"hoodie",
}
]

Laravel Query

return Clothes::join('clothes_category', 'clothes_category.id', '=', 'clothes.category_id')
            ->where('clothes_category.category', $cat)->get();

Using normal SQL:

SELECT *
FROM clothes
INNER JOIN clothes_category ON clothes_category.id = clothes.category_id
WHERE clothes_category.category = 'hoodie';

[
{
  "id":1, NOTICE id is different now
  "name":"clothes 1", 
  "category_id":1,
  "category":"hoodie",
},
{
  "id":2, NOTICE id is different now
  "name":"clothes10", 
  "category_id":1,
  "category":"hoodie",
}
]

I cant see why this is happeening. Does anyone knows why this happens using laravel?

laravel mysql sql php
2 Answers

You just need to group the results by clothes.

return Clothes::join('clothes_category', 'clothes_category.id', '=', 'clothes.category_id')
            ->where('clothes_category.category', $cat)
            ->groupBy('clothes.id')
            ->get();
user_14472670

You are selecting all columns, so you have both id column from clothes and clothes_category.

Add a select to your query

return Clothes::join('clothes_category', 'clothes_category.id', '=', 'clothes.category_id')
    ->where('clothes_category.category', $cat)
    ->select('clothes.*')
    ->get();
user_62122940
Related