CODEWITHSUNDEEP
mysqllaravel
rockstardev
rockstardev

Reputation: 13527

How can I fix this query concat query?

This is what I want to execute, but it does not work:

$users = DB::table("users")
->select(array(
  'users.*',
  DB::raw("CONCAT (firstname, ' ', lastname) as fullName "),
))
->where("fullName", "like", $query)
->get();

I get this error, as expected:

Column not found: 1054 Unknown column 'fullName' in 'where clause'

Is there any way to make the where clause aware of fullName? I know I can do this:

$users = DB::table("users")
->select(array(
  'users.*',
  DB::raw("CONCAT (firstname, ' ', lastname) as fullName "),
))
->where(DB::raw("CONCAT (firstname, ' ', lastname) like ".$query))
->get();

But if I do it like that, then I need to sanitize $query, and I prefer it if the prepared statement deals with it for me as it would in the first example.

Any idea how to get around this?

Upvotes: 2

Views: 61

Answers (2)

Waleed Muaz
Waleed Muaz

Reputation: 802

For that type of information, if you want to get from the Database. you can use Accessor

    public function getFullNameAttribute()
    {
        return $this->first_name.' '.$this->last_name);
    }

Usage:

$user = User::find(1);
echo $user->full_name;

Upvotes: 0

Yasin Patel
Yasin Patel

Reputation: 5731

Use having() instead of where()

$users = DB::table("users")
->select(array(
  'users.*',
  DB::raw("CONCAT (firstname, ' ', lastname) as fullName "),
))
->having("fullName", "like", $query)
->get();

And change the config setting that check that DB has to run in strict mode:

in /config/database.php: do strict to false

'mysql' => [
        ----
       ----
       'strict' => true,   // <--- Change this to false
       ----
 ],

Upvotes: 5

Related Questions