0

I'm trying to get all the roles permission which has a many to many relationship. I want to get all the permissions of a single role. Here I'm trying to use groupBy but it gives me error. 

$search_role = DB::table('roles')
    ->join('roles_permissions','roles_permissions.role_id','roles.id')
    ->join('permissions','permissions.id','roles_permissions.permission_id')
    ->where('roles.name', 'like', "%$request->searcher%")
    ->orWhere('permissions.name', 'like', "%$request->searcher%")            
    ->select('roles.name as role_name', 'permissions.name as permission_name','roles_permissions.*')                                             
    ->groupBy('roles_permissions.role_id')
    ->get();

Error here: roles_permissions.permission_id isn't in groupBy.

If I add that I get another column isn't in groupBy.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Ascol Parajuli
  • 67
  • 4
  • Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) – philipxy Dec 20 '18 at 09:39
  • Hi. This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Dec 20 '18 at 09:40

1 Answers1

0

you don't have any aggregation operation in select statement. check my example

$search_role = DB::table('roles')
    ->join('roles_permissions','roles_permissions.role_id','roles.id')
    ->join('permissions','permissions.id','roles_permissions.permission_id')
    ->where('roles.name', 'like', "%$request->searcher%")
    ->orWhere('permissions.name', 'like', "%$request->searcher%")            
    ->select('role_id', DB::raw('count(*) as total'))    //example                                          
    ->groupBy('roles_permissions.role_id')
    ->get();

what you are trying to do does not makes sense. Group by does not work in that way. 

SELECT role_id, count(*)
from roles
group by role_id

is valid

SELECT roles.*, count(*)
from roles
group by role_id

is not. in the latter case, you generally need to add every column which is not aggregated to group by statement

Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72