How can I fix this query concat query?

Question

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?

MySQL allows referencing `SELECT` level aliases in `GROUP BY`, `ORDER BY` and `HAVING`. https://stackoverflow.com/questions/2905292/where-vs-having — Asim, Jul 08 '21 at 07:25

Yasin Patel · Accepted Answer · 2021-07-08T07:20:25.330

5

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
       ----
 ],

edited Jul 08 '21 at 07:20

answered Jul 08 '21 at 07:00

Yasin Patel

5,276
8
30
50

What are the downsides to changing strict to false? – coderama Jul 08 '21 at 07:04
2

Refer this Link: https://stackoverflow.com/questions/42104412/what-is-the-use-of-strict-in-laravel-config-database – Yasin Patel Jul 08 '21 at 07:17

score 0 · Answer 2 · answered Jul 08 '21 at 07:24

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;

How can I fix this query concat query?

2 Answers2