php - Dynamically Select columns based on column value

Question

Hello and thanks for taking some time to help with my question.

I am trying to create a conditional if statement using laravel eloquent model.

A sample of my code is like:

$query = DB::table('example')->select('test1',
             DB::raw('IF(test2 = "myText" , test2, FALSE) AS test2_Alias'),
             DB::raw('IF(test2 = "myText" , test3, FALSE) AS test3_Alias')
         )->first();

The condition works fine and i retrieve my results but i need a different thing. Is there a way to remove completely the false statement so my column name does not appear at all?

So my output will be:

 1 array field if `test2` text is not `myText` (test1)
 3 array fields if `test2` test is `myText`    (test1,test2,test3)

Case-when did not work either for that because still there is a fallback there.

Union also does not fit me because i need to have the same number of columns in my select statements which is not something i want as explained above.

Of course as a last solution i can always retrieve all the columns and apply the logic in PHP side but i was hoping that there might be a Mysql Solution for my issue.

Edit to add more info

What i am trying to achieve is based on the value of 1 field to select more columns or exclude them from my output.

Answer 1

You can try to solve it on SQL side. But you will still need to use PHP to write that crazy query dynamically. Or worse - You will write program code in SQL. While post processing in PHP is quite simple:

// $row = DB::..

$row = (object)[ // result from DB
    'field1' => 'value1',
    'field2' => null,
    'field3' => 'value3'
];

foreach (get_object_vars($row) as $key => $val) {
    if ($val === null) {
        unset($row->{$key});
    }
}

That are effectively three lines of quite simple code. The result from var_export($row):

stdClass::__set_state(array(
   'field1' => 'value1',
   'field3' => 'value3',
))

As you see, the field with NULL is removed.

Even better: keep your query simple and just select test2 instead of IF(test2 = "myText" , test2, FALSE) AS test2_Alias. And then "dynamically" create test2_Alias if needed:

if ($row->test2 == 'myText') {
    $row->test2_Alias = $row->test2;
}

Yes - that's boring. Nothing fancy. But you will love simple code, when you try to fix some bugs.

Update

From our chat:

test1,test2,test3 are columns let me explain better in code I want to have:

Select(test1)

select(test1,test2,test3) but the second select should happen only if test2 has a specific value

Assuming your "specific value" is stored in $specificValue.

$row = DB::('example')->select('test1, test2, test3')->first();
if ($row->test2 != $specificValue) {
    unset($row->test2, $row->test3);
}

That's it. IMHO it's better than executing two queries like:

$test2 = DB::('example')->value('test2');
$select = ($test2 == $specificValue) 
    ? 'test1, test2, test3'
    : 'test1';
$row = DB::('example')->select($select)->first();