Query 2 rows conditions in one result

Hello Everyone!

I need help to make a query to get the ids where the values for a specific month are between the start and end dates.

The table format is (I can't change, this is not mine):

id	| attribute_id | value
1	| 24		 | '2016-03-01'
1	| 25		 | '2020-03-01'
...

24 = start
25 = end

I try this:

Model::select('id')
      ->whereIn('id', $Ids)
      // Condition A (Since)
      ->where(function ($conditionA) use ($date) {
        $conditionA->where('attribute_id', 24)
          ->where(function ($q) use ($date) {
            $q->where('value', '<=', $date)->orWhereNull('value');
          })
      })
      // Condition B (Until)
      ->where(function ($conditionB) use ($date) {
        $conditionB->where('attribute_id', 25)
          ->where(function ($q) use ($date) {
            $q->where('value', '>=', $date)->orWhereNull('value');
          })
      })
      ->groupBy('id')
      ->get()
      ->pluck('id');

I expect a result like this:

$result = [1, 3, 5...];

ids that satisfy both the start and end conditions.

When I use just one of that conditions it's works.

But together it's not work.

Thanks for your time!!!

automica

5 years ago

Best Answer

Level 54

@rafaelgrube you need to nest the Condition A & Condition B so it checks both those conditions.

if I've got the nesting right, then this should be:

        Model::select('id')
            ->whereIn('id', $Ids)
            // Condition A (Since)
            ->where(function ($query) use ($date) {
                $query
                    ->where(function ($conditionA) use ($date) {
                    $conditionA->where('attribute_id', 24)
                        ->where(function ($q) use ($date) {
                            $q->where('value', '<=', $date)->orWhereNull('value');
                        });
                }) // Condition B (Until)
                    ->where(function ($conditionB) use ($date) {
                    $conditionB->where('attribute_id', 25)
                        ->where(function ($q) use ($date) {
                            $q->where('value', '>=', $date)->orWhereNull('value');
                        });
                });
            })
            ->groupBy('id')
            ->get()
            ->pluck('id');

1 like

RafaelGrube

5 years ago

Level 10

Thanks @automica. I've tried to do that, but maybe I left something behind.

automica

5 years ago

Level 54

@rafaelgrube Is the above not working then?

if not, lets debug:

$sql = Model::select('id')
    ->whereIn('id', $ids)
    // Condition A (Since)
    ->where(function ($query) use ($date) {
        $query
            ->where(function ($query) use ($date) {
                $query
                    ->where('attribute_id', 24)
                    ->where(function ($q) use ($date) {
                        $q->where('value', '<=', $date)->orWhereNull('value');
                    });
            }) // Condition B (Until)
            ->where(function ($query) use ($date) {
                $query
                    ->where('attribute_id', 25)
                    ->where(function ($q) use ($date) {
                        $q->where('value', '>=', $date)->orWhereNull('value');
                    });
            });
    })->toSql();

drop the above code in and that will return the sql of the query we're running.

Please or to participate in this conversation.