Controller concat problems

Question

I have problems get the options for select with ajax:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'full_name' in 'where clause' (SQL: select count(*) as aggregate from drivers where full_name like %% ▶"

public function drivers(Request $request)
    {
        $q = $request->get('q');

        return Driver::select("id", "first_name", "last_name"
                ,DB::raw("CONCAT(first_name,' ',last_name) as full_name"))
            ->where('full_name', 'like', "%$q%")->paginate(null, ['id', 'full_name as text']);
    }

I do not know where is the problem, any possible solution?

Thanks

nakov · Accepted Answer · 2019-05-08T23:17:40.507

0

You cannot use a virtual column or an alias in a where clause. So try this one instead:

public function drivers(Request $request)
{
    $q = $request->get('q');

    return Driver::select("id", "first_name", "last_name",
           DB::raw("CONCAT(first_name,' ',last_name) as full_name"))
           ->having('full_name', 'like', "%$q%")->simplePaginate(10);
}

edited May 08 '19 at 23:17

answered May 08 '19 at 22:46

nakov

13,938
12
60
110

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'full_name' in 'having clause' (SQL: select count(*) as aggregate from `drivers` having `full_name` like ▶" – Mendizalea May 08 '19 at 22:49
can you remove your `paginate` call and try again. I just now noticed that. I edited my code. – nakov May 08 '19 at 22:51
tahnks you, without paginate works fine, but I need paginate – Mendizalea May 08 '19 at 22:55
You had a wrong usage of pagination, and again you changed the alias of full_name there, so try my code now. – nakov May 08 '19 at 22:57
I tried your code but same error: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'full_name' in 'having clause' (SQL: select count(*) as aggregate from `drivers` having `full_name` like ▶" – Mendizalea May 08 '19 at 22:59
And if you add a value, like 10 for example? – nakov May 08 '19 at 23:01
Same problem with value in paginate – Mendizalea May 08 '19 at 23:12
This is a bigger issue here, so there is more answers [here](https://stackoverflow.com/questions/19349397/how-to-use-paginate-with-a-having-clause-when-column-does-not-exist-in-table/20945960#20945960) but you can fix it by using `simplePaginate` instead of `paginate` as an easy fix. See my edit – nakov May 08 '19 at 23:17

score 0 · Answer 2 · answered May 09 '19 at 05:08

Why not try it like this

return Driver
    ::select("id", "first_name", "last_name",DB::raw("CONCAT(first_name,' ',last_name) as full_name"))
    ->whereRaw('full_name like %?%', [$q])
    ->paginate(null, ['id', 'full_name as text']);

https://laravel.com/docs/5.8/queries Raw Methods

Controller concat problems

2 Answers2