Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

cooperino's avatar

How to add "fictional" column to query?

I am using Yajra Datatables and from what I see, it's not possible to perform a search query on an added column that's not on the SQL query.

Right now I have the query:

$model::select('column1','column2','column3')...

Is it possible to add non-existing column and then I will manipulate it?

0 likes
13 replies
tykus's avatar

Is it possible to add non-existing column

Do you mean you want to use a raw expression? Something like:

$model::selectRaw('column1, column2, column3, COUNT(*) as total, CONCAT(first_name, last_name) as fullname')...
1 like
cooperino's avatar

@michaloravec @tykus My case is slightly different (I used the above approach in another table)

But now I have an added column that maps codes to full names and is not fetched from the database directly

    ->addColumn('full_string', function ($data) {
        return $this->setFullString($data);
    })


    protected function setFullString($data)
    {
        $string_list = // getting string list from some package
        return $string_list[$data->string_code];
    }
tykus's avatar

@cooperino so what are you trying to achieve; filter/search by the full_string value(s)?

1 like
MichalOravec's avatar

@cooperino Just a hint

$model::selectRaw('column1, column2, column3, string_code as full_string')

->filterColumn('full_string', function($query, $keyword) {
    $string_list = // getting string list from some package
    
    $key = array_search($keyword, $string_list);
    
    $query->where('string_code', $key);
})
1 like
cooperino's avatar

@MichalOravec But I also need the original string_code in the table (So I have both string_code and full_string columns)

Maybe I will try to select same column twice and one of them with alias?

MichalOravec's avatar

@cooperino And that's a problem?

$model::selectRaw('column1, column2, column3, string_code, string_code as full_string')
1 like
cooperino's avatar

@MichalOravec I just realized that it won't help either. Because we are searching by the key, but not by the full name. Even if I use raw SQL to alias the column, it would still only search by the code.

so if the data is for example "test" => "some test text", then if I search "some test text" it won't find anything. Only find by the keyword "test"

Not sure it's possible to do it with Yajra (searching on an Added column)

cooperino's avatar

@MichalOravec Not sure it can work. First of all array_search is for exact matches, Yajra search should match partial strings. Second, it looks like it would only work for single match? (Or I'm wrong?) Because you get a single $key returned. But what if there can be multiple results?

I might give whereIn a try but not sure it works with Yajra

cooperino's avatar

@MichalOravec After checking again, it doesn't work. It only filters according to the original string_code, not full_string:

 return $model::select('string_code', DB::raw("string_code as full_string"), 'column1', 'column2')...

So ok, my custom query works, but only when I use:

->filterColumn('string_code', function ($query, $keyword) {

but when I change it to

->filterColumn('full_string', function ($query, $keyword) {

the logic doesn't work. it ignores the raw column.

**I tried both your way of selectRaw and DB::raw

Please or to participate in this conversation.