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
– VIKESIR, Commented Apr 28, 2022 at 11:44
The reference you need: stackoverflow.com/questions/25800411/mysql-isnt-in-group-by — user3532758
– user3532758, Commented Apr 28, 2022 at 12:14
Also note that when you group by, the actual id (pk) of the row in essence becomes irrelevant and meaningless. — user3532758
– user3532758, Commented Apr 28, 2022 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
– miken32, Commented Apr 28, 2022 at 15:04

Joseph · Accepted Answer · 2022-04-28 14:34:51Z

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, 2022 at 14:34

Joseph

6,3273 gold badges24 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VIKESIR Over a year ago

Your answer is correct 1+ vote. But SELECT * FROM notification GROUP BY user; working in phpmyadmin. I think this not works in SQL.

MelB · Accepted Answer · 2022-04-28 11:51:03Z

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, 2022 at 11:51

MelB

115 bronze badges

2 Comments

VIKESIR Over a year ago

Same error Syntax error or access violation: 1055 'invoicestatus.notification.id' isn't in GROUP BY (SQL: SELECT id FROM notification GROUP BY user)

MelB Over a year ago

Column in your select must be on your group by (same columns for it to work bro) I don't think Asterisk will work

Collectives™ on Stack Overflow

Create variable based query to select data in laravel

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related