I need to load all my columns from db

Level 54

@smoketm

->select('country', DB::raw('COUNT(country) AS occurrences'))

you are only selecting 2 columns!

add an asterisk

->select('*',''country', DB::raw('COUNT(country) AS occurrences'))

1 like

Level 10

@automica Isnt working I am getting the following exception

Syntax error or access violation: 1055 'db.qr_codes.id' isn't in GROUP BY

Tray2

Level 74

@SmokeTM I don't know how many times this question comes up here in the forum, but it's one of the most frequent ones.

To use an aggregate function like you are doing, you need to tell the database which columns you should group by, and every column that you want to fetch needs to be in the group by.

There are some that suggests turning off the strict group by setting in MySQL, but that is a really shitty idea, learn to write proper SQL instead, you will thank me for it later.

Anyways our friend @sinnbeck has written a post about it here https://sinnbeck.dev/posts/laravel-groupby-error

2 likes

Snapey

Level 122

your query gets the most frequent country (1 record)

what other columns could you get from this query? You can't get data from the individual records in the same 1 row.

1 like

Level 11

If, for example, your "qr_codes" table has columns col1, col2, col3, then this should work:

QrCode::where('user_id', auth()->id())
                        ->leftJoin('qr_code_trackings', 'qr_code_id', '=', 'qr_codes.id')
                        ->select('country', 'qr_codes.col1', 'qr_codes.col2', 'qr_codes.col3',  DB::raw('COUNT(country) AS occurrences'))
                        ->groupBy('country', 'qr_codes.col1', 'qr_codes.col2', 'qr_codes.col3')
                        ->orderBy('occurrences', 'DESC')
                        ->limit(1)
                        ->paginate(20);

So you need to add those columns manually in the select and groupBy

1 like

Level 10

Thanks for your replies. What I did now is to go through the collection and sort it to get the most engaged country but I think thats also not the way to go.

$qrCodes = QrCode::where('user_id', auth()->id())->whereHas('ingredient', function ($query) {
                            $query->where('wine', 'like', '%' . $this->search . '%')
                                ->orWhere('vintage', 'like', '%' . $this->search . '%')
                                ->orWhere(DB::raw('concat(wine," ",vintage)') , 'like', '%' . $this->search . '%')
                                ;
                        })
                        ->with(['ingredient', 'qr_code_trackings'])
                        ->latest()
                        ->paginate(20);
    
        $qrCodes->map(function($item) {
            $item['cities'] = $item->qr_code_trackings->countBy('city')->sort()->reverse();
            $item['countries'] = $item->qr_code_trackings->countBy('country')->sort()->reverse();
        });

Will try it @povilaskorop thank you. How to handle the relationships then?

Level 11

@SmokeTM if you need some relationship fields, specify them in the select/grouping, too.

Level 10

@PovilasKorop The problem I get if I do include other fields in the group by the result changes. Unexpected behaviour

Level 11

@SmokeTM can't really comment if you don't provide the full query and full difference in results.

Level 10

@PovilasKorop Here is my query.

QrCode::where('user_id', auth()->id())
                        ->whereHas('ingredient', function ($query) {
                            $query->where('wine', 'like', '%' . $this->search . '%')
                                ->orWhere('vintage', 'like', '%' . $this->search . '%')
                                ->orWhere(DB::raw('concat(wine," ",vintage)') , 'like', '%' . $this->search . '%')
                                ;
                        })
                        ->leftJoin('qr_code_trackings', 'qr_code_id', '=', 'qr_codes.id')
                        ->select('country', 'qr_codes.views_count', 'qr_codes.users_count', 'qr_codes.created_at',  DB::raw('COUNT(country) AS occurrences'))
                        ->groupBy('country', 'qr_codes.views_count', 'qr_codes.users_count', 'qr_codes.created_at')
                        ->orderBy('occurrences', 'DESC')
                        ->limit(1)
                        ->latest()
                        ->paginate(20);

I am getting 5 QrCodes instead of 3. If I am including the relationship like so

QrCode::where('user_id', auth()->id())
                        ->whereHas('ingredient', function ($query) {
                            $query->where('wine', 'like', '%' . $this->search . '%')
                                ->orWhere('vintage', 'like', '%' . $this->search . '%')
                                ->orWhere(DB::raw('concat(wine," ",vintage)') , 'like', '%' . $this->search . '%')
                                ;
                        })
                        ->leftJoin('qr_code_trackings', 'qr_code_id', '=', 'qr_codes.id')
                        ->select('country', 'qr_codes.views_count', 'qr_codes.users_count', 'qr_codes.created_at', 'qr_codes.ingredient.wine',  DB::raw('COUNT(country) AS occurrences'))
                        ->groupBy('country', 'qr_codes.views_count', 'qr_codes.users_count', 'qr_codes.created_at', 'qr_codes.ingredient.wine')
                        ->orderBy('occurrences', 'DESC')
                        ->limit(1)
                        ->latest()
                        ->paginate(20);

I am getting Column not found: 1054 Unknown column 'qr_codes.ingredient.wine' in 'field list'

Level 11

@SmokeTM a few things.

You need to load the relationship, so join('ingredient') - you're just doing a whereHas() by it as a filter but it doesn't actually join that table.
As for why it returns 5 instead of 3 rows, it's hard to say but the fact that you're doing country and count(country) in the same select, it telling me that you're grouping in somehow the wrong way. The same field shouldn't be in grouped and aggregated.

Sorry don't have more time to debug than this comment.

1 like