how to fetch from db with laravel using CONCAT and COUNT

February 6, 2022

In database I have table Users like this:

// id // name  // street  // house
--------------------------
// 1 // name1  // street1 // 1
// 2 // name2  // street1 // 1
// 3 // name3  // street1 // 1
// 4 // name4  // street2 // 2
// 5 // name5  // street3 // 3
// 6 // name6  // street4 // 4
// 7 // name7  // street5 // 5

I need to make a selection where I will get the full address and the number of records with that address in descending order for Users in users_ids array. The result should look like this:

 address     number
'street1 1'   3
'street2 2'   1
'street3 3'   1
'street4 4'   1
'street5 5'   1

in laravel i use query:

        $recordsByAddresses = DB::table('users')
            ->whereIn('id', $users_ids)
            ->select(DB::raw("CONCAT(street, ' ', house) AS address"), DB::raw("COUNT(CONCAT(street, ' ', house)) AS number"))
            ->orderBy('number', 'desc')
            ->get();

As a result I get only one first address and the number of all addresses:

 address     number
'street1 1'   7

>Solution :

It’s untested but this might work:

$recordsByAddresses = DB::table('users')
    ->whereIn('id', $userIds)
    ->select(
        DB::raw('CONCAT(street, " ", house) AS address'),
        DB::raw('count(*) as total')
    )
    ->groupBy('address')
    ->orderBy('total', 'desc')
    ->get();