How to write Query using Laravel

Question

How to write this Query in Laravel?

SELECT
        a.id, a.name, a.email, a.role, a.percentage, a.remaining_tax, a.min_peoples_per_table ,a.regions,
        GROUP_CONCAT( r.name SEPARATOR ", ") as regions_name
    FROM administrator as a
    INNER JOIN regions as r
        ON a.regions REGEXP CONCAT("[[:<:]]", r.id, "[[:>:]]")
    GROUP BY a.id
    ORDER BY a.id desc;

Code under construction:

$list = DB::table('administrator as a')
        ->select("a.id" , "a.name", "a.email", "a.role", "a.percentage", "a.remaining_tax", "a.min_peoples_per_table" ,"a.regions")
        ->groupBy("a.id")
        ->orderBy("a.id", "desc")
        ->get();
 return $list;

In the part of GROUP_CONCAT and JOIN that I’m half lost.

Answer 1

The method join() accepts 4 arguments:

1. The table that will be made the Join. In this case: regions as r.
2. The field of the clause ON. In this case: a.regions
3. The operator of the clause ON. In this case: REGEXP
4. The second field of the clause ON. In this case we can use DB::raw() to add the code. Ex.: DB::raw('CONCAT("[[:<:]]", r.id, "[[:>:]]")')

Then it would look like this:

<?php

$list = DB::table('administrator as a')
            ->select("*")
            ->join(
                'regions as r',
                'a.regions',
                'REGEXP',
                DB::raw('CONCAT("[[:<:]]", r.id, "[[:>:]]")')
            )
            ->groupBy("a.id")
            ->orderBy("a.id", "desc")