Laravel: Using Append Field in Where Condition

Question

I'm new using Laravel. I have a model Member with 'dateOfBirth' field and an append field called 'category' (it has information with sports category depending from age like (Under 10, Under 15, Junior, Senior, Veteran...) so it cannot be in DB because it change every sport season.

But I need filtering members having a condition depending on age (cagegory). The filter let the user filter for MORE THAN ONE CATEGORY. Is my main problem.

    $all = Member::where('status', '<>', 'pending');
    if ($category != null) {
        $all = $all->whereIn('category', $category);
    }

This is not working because I have an error telling that column doesn't exist:

Column not found: 1054 Unknown column 'category' in 'where clause'

Is there a way that I can solve it?

Answer 1

You can use switch case, which allows you to customize SQL queries based on different conditions for each category.

$members = Member::where('status', '<>', 'pending');

if ($category !== null) {
    switch ($category) {
        case 'Under 10':
            $members = $members->where('dateOfBirth', '>=', now()->subYears(10));
            break;
        case 'Under 15':
            $members = $members->where('dateOfBirth', '>=', now()->subYears(15));
            break;
        case 'Junior':
            // Conditions for Junior category
            break;
        case 'Senior':
            // Conditions for Senior category
            break;
        case 'Veteran':
            // Conditions for Veteran category
            break;
    }
}

$members = $members->get();

---

Modify: Can filter multiple categories

If you need to filter multiple categories, you can change $category to an array and iterate over the array to apply different conditions. This way the conditions of each category can be applied to the query.

This is the modified code:

$members = Member::where('status', '<>', 'pending');

if (!empty($categories)) {
    $members = $members->where(function ($query) use ($categories) {
        foreach ($categories as $category) {
            switch ($category) {
                case 'Under 10':
                    $query->orWhere('dateOfBirth', '>=', now()->subYears(10));
                    break;
                case 'Under 15':
                    $query->orWhere('dateOfBirth', '>=', now()->subYears(15));
                    break;
                case 'Junior':
                    // Add conditions for the Junior category here
                    break;
                case 'Senior':
                    // Add conditions for the Senior category here
                    break;
                case 'Veteran':
                    // Add conditions for the Veteran category here
                    break;
            }
        }
    });
}

$members = $members->get();

The orWhere() used here ensures that if any of the categories match, that member will be included in the results.

If you want all selected category conditions to be met (that is, AND logic), you should adjust it to where().