Laravel how to do group concat from two columns using querybuilder

Question

I got a params which is projectname and it accepts any string based on the column of my db. What I want is when I search a keyword which is like this BEDOK, then shows me the concatinated list of the columns in my db. Like this BEDOK-20,BEDOK-22.

My db is like this

My controller code i tried

    public function getNames(Request $request){
        $result =  DB::connection('mysql2')
                    ->table('xp_pn_resale')
                    ->select('town')
                    ->whereRaw(DB::raw("CONCAT(town, ' ', street_name,'',block,'')LIKE '%$request->projectname%' "))
                    ->limit($request->limit)   
                    ->get();
                    // ->toSql();

        return response()->json($result);            
    }

And i got response like this

"town": "BEDOK"
},
{
"town": "BEDOK"
},
{
"town": "BEDOK"
},
...

What I want is like this

[
"town": "BEDOK-44"
},
{
"town": "BEDOK-540"
},
{
"town": "BEDOK-702"
},
...

TsaiKoga · Accepted Answer · 2020-01-07 05:56:29Z

3

You just need to select like this:

->selectRaw("CONCAT(town, '-', block) AS town")

edited Jan 7, 2020 at 5:56

answered Jan 7, 2020 at 5:52

TsaiKoga

13.4k2 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

draw134 Over a year ago

I should replace the select with your answer?

Chirag Chhuchha · Accepted Answer · 2020-01-07 06:08:42Z

1

I have faced this issue before and I solved it with a DB::raw only, you can try changing your whereRaw method like this

->where(DB::raw("CONCAT(`town`, '-', `block`)", "LIKE", "%".$request->projectname."%"))

answered Jan 7, 2020 at 6:08

Chirag Chhuchha

3952 silver badges13 bronze badges

Collectives™ on Stack Overflow

Laravel how to do group concat from two columns using querybuilder

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related