laravel join query return two identical ids for two different data

Question

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?

score 3 · Answer 1 · answered Dec 20 '20 at 12:14

3

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();

answered Dec 20 '20 at 12:14

HTMHell

5,761
5
37
79

Although your solution seems correct. it didnt work for me it throws up this error ```Syntax error or access violation: 1055```. But I did found a solution I just needed to add before join() a select('clothes.*'). that fixed the issue. I will still upvote your answer as I believe is also one way to fix it. I just couldnt get it working on my environment. – Luke Dec 20 '20 at 12:26
You can solve this by looking at https://stackoverflow.com/questions/40917189/laravel-syntax-error-or-access-violation-1055-error – HTMHell Dec 20 '20 at 12:29

SEYED BABAK ASHRAFI · Accepted Answer · 2020-12-20T12:35:31.347

2

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();

edited Dec 20 '20 at 12:35

answered Dec 20 '20 at 12:28

SEYED BABAK ASHRAFI

4,093
4
22
32

laravel join query return two identical ids for two different data

2 Answers2