How to write Query using Laravel

Question

How to write Query using Laravel

Navigation

#1 by (0 votes)

1

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 I'm kind of lost.

laravel

asked by anonymous 04.09.2018 / 13:52

1 answer

Codeigniter 3.1.9 - Connection with the Database Place details when clicking on full calendar events

score 0 · Answer 1

The method join() accepts 4 arguments:

The table to be joined. In this case: regions as r .

The field of the ON clause. In this case: a.regions

The operator of the ON clause. In this case: REGEXP

The second field of the ON clause. 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")