I want to build a query in MySQL with this statement:

SELECT CONCAT(students.FirstName, ' ', students.LastName) AS FullName FROM `students` WHERE CONCAT(students.FirstName, ' ', students.LastName)LIKE '%John%'

And now, I use Query Builder from Laravel where I can match the column using CONCAT(). Here's my code for reference:

    public function getStudent(Request $request)
    {
        $fullName = Student::select(FacadesDB::raw('SELECT CONCAT(LastName, ", ", FirstName) AS FullName'), )
            ->where(FacadesDB::raw('CONCAT(LastName, ", ", FirstName)', 'LIKE', "'% {{ $request->terms }} %'"))
            ->get();
        return response()->json($fullName);
    }

When I try to run the code, I got a result like this:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT CONCAT(LastName, ", ", FirstName) AS FullName from students where CO...' at line 1 (SQL: select SELECT CONCAT(LastName, ", ", FirstName) AS FullName from students where CONCAT(LastName, ", ", FirstName) is null)

Is there a way that I can achieve this one? It took me like almost 4 hours to solve this code and I still can't solve it. Every help would be appreciated. I'm a new Laravel user, btw.

CodePudding user response：

I would keep it simple and just phrase your query as:

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM students
WHERE FirstName LIKE '%John%' OR LastName LIKE '%John%';

Updated Laravel code:

public function getStudent(Request $request)
{
    $fullName = Student::select(FacadesDB::raw('CONCAT(LastName, ", ", FirstName) AS FullName'), )
        ->where(FirstName, 'LIKE', '%John%')
        ->orWhere(LastName, 'LIKE', '%John%')
        ->get();
    return response()->json($fullName);
}

CodePudding user response：

you can use map function:-

Student::select("firstname","lastname")
->where("firstname","like","%john%")
->orWhere("lastname","like","%john%")
->get()->map(function($query){
    $fullname = $query->firstname." ".$query->lastname
    return ["fullname" => $fullname]
 })