1

I have the table like that

|----|--------|------------|--------|
| id | UserID |  ExpDate   | isUsed |
|----|--------|------------|--------|
| 1  |  1265  | 2019-09-08 |   0    |
|----|--------|------------|--------|
| 2  |  1265  | 2019-08-28 |   0    |
|----|--------|------------|--------|
| 3  |  1265  |    null    |   0    |
|----|--------|------------|--------|
| 4  |  1265  |    null    |   1    |
|----|--------|------------|--------|
| 5  |  1582  | 2019-09-07 |   0    |
|----|--------|------------|--------|
  .      .          .          .
  .      .          .          .
  .      .          .          .

I want to select rows that User = 1265 and isUsed = 0 and ( ExpDate > 2019-09-05 or ExpDate = null)

How to make these selection using laravel eloquent?

I've tried below but it selects all ExpDate is bigger than 2019-09-05 rows on table. Not filtered by UserID

       Hak::whereNull('ExpDate')
            ->orWhere('ExpDate', '>', '2019-09-05')
            ->where('UserID', 23)
            ->get()
1

4 Answers 4

6

You can try with below.

Hak::where(function($query){
    $query->whereNull('ExpDate')->orWhere('ExpDate', '>', '2019-09-05');
})->where(['User'=>1265, 'isUsed' => 0])->get();
Sign up to request clarification or add additional context in comments.

1 Comment

This helps for me. Thanks
3

You can use sub query to achieve this i haven't tested it but you can give it a try.

Hak::where('UserID', 23)->where('isUsed', 0)->where(function ($q){
   $q->whereNull('ExpDate')
   ->orWhere('ExpDate', '>', '2019-09-05');        
})->get();

1 Comment

This helps for me. Thanks
0

i think i would write the SQL as followes as OR optimizes badly in MySQL..
Pretty sure fully supported Parallel Query Execution will change that in the future.
As they are working on this feature if you want you can read MySQL 8.0.14: A Road to Parallel Query Execution is Wide Open!

The SQL result might be wrong as you didn't provide example data and expected result see Why should I provide a Minimal Reproducible Example for a very simple SQL query? for providing those.

Query

SELECT 
 *
FROM 
 your_table
WHERE 
   UserID = 23
 AND
   isUsed = 0
 AND
  ExpDate >  '2019-09-05'


UNION ALL

SELECT 
 *
FROM 
 your_table
WHERE 
   UserID = 23
 AND
   isUsed = 0
 AND 
   ExpDate IS NULL

Laravel code

To warn you i didn't program in Laravel for some time now so this code might be wrong.

<?php
 $first = DB::table('your_table')
            ->select('your_table.*')
            ->where('UserID ', '=', 1)
            ->andWhere('isUsed ', '=', 0)
            ->andWhere('ExpDate ', '>', '2019-09-05')  
 ;

 $second = DB::table('your_table')
             ->select('your_table.*')
            ->where('UserID ', '=', 1)
            ->andWhere('isUsed', '=', 0)
            ->whereNull('ExpDate') 

            ->union($first)
            ->get();
?> 

Comments

-1

It can be confusing coming from SQL, but you'll need two orWhere in Eloquent to achieve this. So basically:

Hak::orWhereNull('ExpDate')
        ->orWhere('ExpDate', '>', '2019-09-05')
        ->where('UserID', 23)
        ->get()

EDIT: Haven't tested it though, but you might need a subquery to filter the userId as well.

1 Comment

This gets all rows from table, not filter UserID. Thanks for helping

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.