Laravel Eloquent Chaining many Where (and) with a subsection of Where (ors)

I'm trying to build a request that returns all users that are a subset of a specific user ID (via connected_id), who are also between one or many age ranges (eg "20-30", "50-60"). The list of ages ranges are presented via another array ($ageRangeTable). User with ID 5 is ignored.

The user table only contains a date of birth, therefore the age has to be calculated on the fly.

Here's what I have so far, but the exported SQL is ignoring the OR section. (I'm using "explode" to separate the min and max age for now, as the actual string "x-y" might change soon to something else).

 $ageRangeTable = ['20-30','50-60'];
 $result = Users::where('connected_id', Auth::user()->id)
        ->where('id' , '!=',  5)
        ->where(
            function ($query) use ($ageRangeTable) {
                $count = 0;
                foreach($ageRangeTable as $agerange){
                    list($min,$max) = explode( "-", $agerange);
                    if($count == 0){
                        $query->where(`TIMESTAMPDIFF( YEAR, 'dateofbirth', CURDATE() )`, `between`, `{$min} AND {$max}`);
                    }else{
                        $query->orWhere(`TIMESTAMPDIFF( YEAR, 'dateofbirth', CURDATE() )`, `between`, `{$min} AND {$max}`);
                    }
                    $count++;
                }
                return $query;
            }
        );
        //->get(); don't use this yet as we just want to review SQL for now...

        dd($result->toSql());

The resulting SQL looks like this:

  "select * from `users` where `connected_id` = ? and (`id` != ?)"

...which ignores all the "ors". I expected this_

  "select * from `users` where `connected_id` = ? and (`id` != ?) and ((`TIMESTAMPDIFF( YEAR, 'dateofbirth', CURDATE() )` BETWEEN ? AND ?) OR (`TIMESTAMPDIFF( YEAR, 'dateofbirth', CURDATE() )` BETWEEN ? AND ?))

What can I try next?

Solution

You have several issues here:

Backticks should go around the column names, quotes around the strings
Single quotes won't expand the min and max variables

There are also a few small tweaks you can use to utilize the QueryBuilder more

use whereBetween for the queries
Use DB::raw for the columns

Your query will then look like this:

$result = Users::where('connected_id', Auth::user()->id)
    ->where('id', '!=', 5)
    ->where(
        function($query) use ($ageRangeTable) {
            $count = 0;
            foreach($ageRangeTable as $agerange) {
                [$min, $max] = explode("-", $agerange);
                if($count == 0) {
                    $query->whereBetween(DB::raw('TIMESTAMPDIFF( YEAR, `dateofbirth`, CURDATE() )'), [$min, $max]);
                } else {
                    $query->orWhereBetween(DB::raw('TIMESTAMPDIFF( YEAR, `dateofbirth`, CURDATE() )'), [$min, $max]);
                }
                $count++;
            }
            return $query;
        }
    );