CODEWITHSUNDEEP
phpmysqllaravel
draw134
draw134

Reputation: 1187

Laravel how to do group concat from two columns using querybuilder

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 enter image description here

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"
},
...

Upvotes: 0

Views: 596

Answers (2)

Chirag Chhuchha
Chirag Chhuchha

Reputation: 395

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."%"))

Upvotes: 1

TsaiKoga
TsaiKoga

Reputation: 13394

You just need to select like this:

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

Upvotes: 3

Related Questions