How to do orWhere correctly?

I have a query I that is a bit confusing and I am not sure its working correctly.

I have Jobs that are connected to Companies by one of two fields. And I am querying a an MsSql db

So for each company, I have a list of ids that the jobs can be connected to. My query looks like:

// Kick off a job per company
            $results = DB::connection('sqlsrv')->table('dbo.tabJob')
                ->where('TimeNow', '>', $twoWeeksAgo .' 00:00:00')
                ->whereIn('ID3rdPty', $ids)
                ->orWhere(function($query) use($twoWeeksAgo, $ids){
                    $query->where('TimeNow', '>', $twoWeeksAgo .' 00:00:00')
                        ->whereIn('ShprID', $ids);
                })
                ->get()->toArray();

What I am trying to do is get all Jobs from this db where they are newer than 2 weeks old AND where one of two field values exists in an array of ids.

Am I doing this right?

LaryAI

3 years ago

Level 58

The query looks correct. It will return all jobs that are newer than 2 weeks old and where either the ID3rdPty or ShprID is in the $ids array.

However, if you want to make sure that both conditions are met (i.e. the job is newer than 2 weeks old and either the ID3rdPty or ShprID is in the $ids array), you should use nested where clauses instead of orWhere.

Here's an example:

$results = DB::connection('sqlsrv')->table('dbo.tabJob')
    ->where('TimeNow', '>', $twoWeeksAgo .' 00:00:00')
    ->where(function($query) use($ids){
        $query->whereIn('ID3rdPty', $ids)
              ->orWhereIn('ShprID', $ids);
    })
    ->get()->toArray();

This will return all jobs that are newer than 2 weeks old and where either the ID3rdPty or ShprID is in the $ids array.

Snapey

3 years ago

Level 122

Your query should read like

WHERE the tabJob has TimeNow in the last two weeks

AND

(WHERE ID3rdPty in $ids OR WHERE ShprID in $ids)

Since you need brackets around the second clause, your query should probably look like

            $results = DB::connection('sqlsrv')->table('dbo.tabJob')
                ->where('TimeNow', '>', today()->subWeeks(2) )
				->where(function(Builder $query) use($ids) {
					$query->whereIn('ID3rdPty', $ids)
						->orWhereIn('ShprID', $ids);
				})
				->get()
				->toArray();

Please or to participate in this conversation.