Create variable based query to select data in laravel

Question

I created a helper function for count all notification in navbar, For this I am storing clause in a single variable.

I am accessing it by calling-

countData("notification","WHERE seen_status = '0'")

My function is-

function countData($table,$clause) {
    $result = DB::select("SELECT * FROM $table $clause");
    return count($result);
}

It working fine, but getting error in-

countData("projects","GROUP BY user")

I can use groupBy('user') but problem is, I don't want to pass too many variable inside my function. So, is there any option to run my custom query by using single variable?

Syntax error or access violation: 1055 'invoicestatus.notification.id' isn't in GROUP BY (SQL: SELECT * FROM notification GROUP BY user) — VIKESIR, Apr 28 '22 at 11:44
The reference you need: https://stackoverflow.com/questions/25800411/mysql-isnt-in-group-by — user3532758, Apr 28 '22 at 12:14
Also note that when you group by, the actual id (pk) of the row in essence becomes irrelevant and meaningless. — user3532758, Apr 28 '22 at 12:19
This is wasteful because you should just be using the `COUNT()` function, dangerous because you aren't doing any escaping, and probably redundant because you should be able to use built-in functions to do this. — miken32, Apr 28 '22 at 15:04

score 1 · Answer 1 · answered Apr 28 '22 at 14:34

1

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

So the problem here is that you need to use an aggregate function first then group it

answered Apr 28 '22 at 14:34

Joseph

5,644
3
18
44

1

Your answer is correct 1+ vote. But SELECT * FROM notification GROUP BY user; working in phpmyadmin. I think this not works in SQL. – VIKESIR Apr 29 '22 at 11:50

score 0 · Answer 2 · answered Apr 28 '22 at 11:51

0

I think your group by must have a specific column in your select.

try that


function countData($columns,$table,$clause) {
    $result = DB::select("SELECT $columns FROM $table $clause");
    return count($result);
}

countData("user","projects","GROUP BY user")

or you can simply make one variable for all your selects

function countData($query) { $result = DB::select($query); return count($result); }

answered Apr 28 '22 at 11:51

MelB

11
5

Same error Syntax error or access violation: 1055 'invoicestatus.notification.id' isn't in GROUP BY (SQL: SELECT id FROM notification GROUP BY user) – VIKESIR Apr 28 '22 at 11:56
Column in your select must be on your group by (same columns for it to work bro) I don't think Asterisk will work – MelB Apr 29 '22 at 12:27

Create variable based query to select data in laravel

2 Answers2