Question 290584 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel Eloquent Chaining many Where(and) with a subsection of Where(or)'s

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.
(please note, 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 ?))

Can anyone help?

Tags:

2

Answers


  1. 0

    try using a raw query – whereRaw

    $ageRangeTable = ['20-30', '50-60'];

$sqlRaw = [];

foreach ($ageRangeTable as $range) {
    [$min, $max] = explode( '-', $range);

    $sqlRaw[] = sprintf('YEAR(DATE_SUB(NOW(), INTERVAL TO_DAYS(dateofbirth) DAY)) BETWEEN %d AND %d', $min, $max);
}

$result = Users::query()
    ->where('connected_id', Auth::user()->id)
    ->where('id' , '!=',  5)
    ->whereRaw(implode(' AND ', $sqlRaw))
    ->get();
    Login or Signup to reply.
  2. 0

    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;
        }
    );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search