Return duplicate data when i use Join Query in Laravel 5.8

Question

When i try to use inner join query in laravel 5.8, returned duplicate datas..

There are 2 groups and 8 products in the database. When I want to print group names from database, instead of printing 2 group names, it prints the number of products to screen (8 groups names).

I tried Laravel ->distinct() and some different methods but doesn't work.

That's my controller codes:

$user_id = Auth::id();
$captions = DB::table('ab_captions')->where('ab_groups.user_id', $user_id)->where('ab_captions.user_id', $user_id)->join('ab_groups', 'ab_captions.group_id', '=', 'ab_groups.id')->distinct()->get();
    if ($captions == !NULL) {
        return view('default.captions', ['captions' => $captions]);
    } else {
        return view('default.captions');
    }

And blade codes:

@if(!empty($captions)) 
    @foreach($captions as $showcaptions)
        <div class="list-body" style="margin-top: 5px;">
            <a href="{{ url('captions/'.$showcaptions->groupname) }}" class="item-title _500">{{ $showcaptions->groupname }}</a>
        </div>
    @endforeach
@endif

And output like this:

Groupname-1

Groupname-2

As i said, i have 2 group names (Groupname-1 and Groupname-2)

I know it's a simple problem, but I haven't been able to solve it even though I've researched a lot. Really thank you for your help.

If you need only groups, you can simply get it from the `ab_groups` table. In this case, you do not need any join. — Zeshan, Jul 13 '19 at 20:11

score 2 · Accepted Answer · answered Jul 13 '19 at 19:51

2

You need to use GROUP BY clause, to group your result according to group_id or group_name

$captions = DB::table('ab_captions')->where('ab_groups.user_id', $user_id)->where('ab_captions.user_id', $user_id)->join('ab_groups', 'ab_captions.group_id', '=', 'ab_groups.id')->groupBy('ab_captions.group_id')->get();

answered Jul 13 '19 at 19:51

MAZux

911
1
15
28

Sorry, how exactly can I do this? – Fazıl Akbulut Jul 13 '19 at 19:53
Error.. SQLSTATE[42000]: Syntax error or access violation: 1055 'autobot.ab_captions.id' isn't in GROUP BY (SQL: select * from `ab_captions` inner join `ab_groups` on `ab_captions`.`group_id` = `ab_groups`.`id` where `ab_groups`.`user_id` = 1 and `ab_captions`.`user_id` = 1 group by `ab_groups`.`id`) – Fazıl Akbulut Jul 13 '19 at 19:56
I think this is a different problem, check this [answer](https://stackoverflow.com/questions/40917189/laravel-syntax-error-or-access-violation-1055-error) please. – MAZux Jul 13 '19 at 20:00

Return duplicate data when i use Join Query in Laravel 5.8

1 Answers1