How to query column type in Laravel 7 and retrieve boolean values?

Question

Is there a way to be able to tell that a certain column in a given database table is of type "boolean" and if it is - convert the "0" or "1" into "No" and "Yes"?

I have a table called "gearitems" that has basic general info about hiking gear items (like price, weight, description etc) then I have nearly 60 other "one to one" relation tables (ex: "backpacks", "sleepingbags", "tents") that all have specific "special features" according to the type of gear that it is. For example, if it's a backpack it has the capacity in Liters but if it's a tent, the number of stakes required etc etc.

The problem I have, is that many of these small "special features" tables have boolean type columns with different names (like 'waterproof' in "backpacks" or 'attachable' in "sleepingbags" ) and when I want to show the user a specific gear piece, I have a function that will join the general "gearitems" table to the corresponding specific gear type table.

For all column types there is no problem displaying the data as it is inserted in the database but the boolean one returns "1" or "0"... I can't just convert all "1" and "0" into "yes" or "no" as other values might be 1 or 0 which ARE NOT boolean. So I figured, that if I could check whether the column type IS of boolean type data I could then safely convert the values into yes or no but how can I do so without specifically targeting the column's name? (as the list of all boolean-type columns will be too long and hard to edit in the future)

I have created a table called "specialfeatures" that contains a list of all the names of the special features (like "waterproof", "stakes_required" "capacity_l" etc) in each table of every gear type ("backpacks", sllepingbags" etc). I thought about adding a column called "boolean" next to the special_features_name column to indicate if that feature is boolean or not but it seems like a very crude and non elegant way to do it. Surely there's a better way?

If it helps, here are the relevant parts in my code:

My controller:

public function show(Manufacturer $manufacturer, GearItem $gearItem, GearCategory $gearCategory)
    { 
        // Get the item's special features Model's name:
        $featureModel = SpecialFeature::where('sub_categories_id', $gearItem->sub_categories_id)->value('model_name');
        $specialFeatures =  'App\\'.$featureModel;

        // Get the manufacturer's name and homepage: 
        $manufacturer->name = $manufacturer::where('id', $gearItem->manufacturer_id)->value('name');
        $manufacturer->homepage = $manufacturer::where('id', $gearItem->manufacturer_id)->value('homepage');

        // Get all the item's special features: 
        $gearItem->features = $specialFeatures::where('gear_items_id', $gearItem->id)->get();
        
        // Iterator for the spacial features names:
        $featureNames = SpecialFeature::where('sub_categories_id', $gearItem->sub_categories_id)->pluck('feature_name');
        $featureNames->title = SpecialFeature::where('sub_categories_id', $gearItem->sub_categories_id)->pluck('feat_html');
        
        return view('gearitem.show', compact(['gearCategory', 'manufacturer', 'gearItem', 'featureNames']));
    }

My view:

   @if($gearItem->url === null)
                <strong>{{$gearItem->name}}</strong> 
                @else
                <strong> <a target="_blank" href="{{$gearItem->url}}">{{$gearItem->name}}</a></strong>
                @endif
                by <strong><a target="_blank" href="{{$manufacturer->homepage}}">{{ $manufacturer->name }}</a></strong>

                <div class="specialFeatures">

                    <div class="row">
                        <div class="col-6">
                            <table class="table">

                                @foreach ($featureNames->title as $title)
                                <tr>
                                    <td>{!! $title !!}</td>
                                </tr>
                                @endforeach

                            </table>
                        </div>

                        <div class="col-6">
                            <table class="table">

                                @foreach ($featureNames as $featureName)
                                <tr>
                                    <td>{{  $gearItem->features[0][$featureName]}}</td>
                                </tr>
                                @endforeach

                            </table>
                        </div>
                    </div>
                </div>

Answer 1

So I found a solution for this problem:

In the model where there are boolean values that need to be queried add the name of the boolean feature as a key and 'boolean' as the value like so:

 protected $casts = [
        'waterproof' => 'boolean'
    ];

Then, in the controller:

// Get all the item's special features and "clean" them: 
        $specialFeaturesValues = $specialFeatures::where('gear_items_id', $gearItem->id)->get();
        $specialFeaturesRejects = ['id' => 'xy', 'gear_items_id' => 'xy', 'created_at' => 'xy', 'updated_at' => 'xy'];
        $specialFeaturesClean = array_diff_key($specialFeaturesValues[0]->getAttributes(), $specialFeaturesRejects);

  $booleanFeatures = array_keys($specialFeaturesValues[0]->getCasts(), 'boolean');

        foreach($booleanFeatures as $booleanFeature){
            if ($specialFeaturesClean[$booleanFeature] > 0){
                $specialFeaturesClean[$booleanFeature] = 'Yes';
            } elseif($specialFeaturesClean[$booleanFeature] === 0){
                $specialFeaturesClean[$booleanFeature] = 'No';
            } else {
                $specialFeaturesClean[$booleanFeature] = 'Unknown';
            };

        }

(don't forget to "send" the $specialFeaturesClean to the view with compact())

in the blade view, this simple foreach loop will display the "clean" values, replacing "1" (or any other truthy value) by "Yes", "0" by "No" and "Null" by "Unknown". (see picture for example)

I hope this is helpful... ;-)