Select all and group by using a column value

What is the structure and data of you destinations table and what is the desired result?

@Tray2

The desired result - I want to get rid of all duplicate tenantIDs from the destination table. That is SELECT all the rows and columns in destination table which has unique tenantID.

As mentioned above, this raw sql command is giving me the desired result. It is the matter of converting it to Laravel query builder.

select * from `destination` where userID=1 group by tenantID;

In database config turn strict mode to false.

You can do like this:

$destination = DB::table('destination')->where(array('user_id'=>'1' ))->groupBy('tenantID')->get();

More info: https://laravel.com/docs/5.6/queries#raw-expressions

@jlrdw and @NoneNameDeveloper Tried both suggestions. It is still throwing the same error.

SQLSTATE[42000]: Syntax error or access violation: 1055 'database.destination.id' isn't in GROUP BY (SQL: select * from `destination` where `userID` = 1 group by `tenantID`)

@bmm , Use like this, not Group By use Order By.

$destination = DB::table('destination')->where(array('user_id'=>'1' ))->orderBy('tenantID')->get();

This should work.

@NoneNameDeveloper this will work but it won't serve my purpose. It will select all the data in the table with user_id=1. I want to omit the duplicate entries.

@bmm , ok so:

$destination = DB::table(destination )
                 ->leftJoin('users', 'destination.user_id', '=', 1)
        ->select('destination.*', 'users.*')
                ->groupBy('tenantID')
                ->get();

This is the most optimal variant.

Ok. Finally resolved it. For future reference, this error is caused due to the strict mode is enabled as @jlrdw suggested. Thanks for the suggestion!

More details are here: https://github.com/laravel/framework/issues/14997

Basically, to resolve it. Turn the sql strict mode to false in MySQL and Laravel.

1. In MySQL - SET sql_mode = ''; or any mode other than STRICT_TRANS_TABLES
2. In Laravel - go to database.php and change 
'mysql' => [ 'strict' => false]

You can also add the distinct keyword in your raw query.

SELECT DISTINCT * FROM table WHERE column1 = 1;

You just have to explicitly select the column that you are using in the group_by. By default, laravel uses select *. You just need to say select *, tenantID. Then it will work with or without strict mode. It's better to use correct sql than turn strict mode off. That's like sweeping the problem under the rug and saying you "fixed" it.

->select('*, tenantID')

Agreed, more explicit is almost always better.

@Cronix Unfortunately, it is throwing the same error with strict mode turned on. However, your suggestion will work if the strict mode is turned off.

I would kill to have a series on just MySql and the database @JeffreyWay :D

Simple solution(tested with Laravel 9 and Spatie/Permissions).

Controller:

//Get permissions group by guard name(3 in my case: web, admin and api)
$permissions = Permission::get()->groupBy('guard_name');

->groupBy('guard_name'); must be after the get method not before it that worked for me: $tree = Tree::where('status', 'approved')->orderBy('id', 'DESC')->get()->groupBy('parentId');