Laravel data to show with specific value, and specific value have specific date?-CodePudding

I have data in table like this

id	name	status	created_at
1	ABC	1	2022-01-01
2	ABC	2	2022-01-01
3	XXD	3	2022-01-01
5	ABC	3	2022-01-01
6	JON	5	2022-01-01
7	WER	5	2022-06-01
8	ABC	5	2022-06-01

And I want results like this. I want to show data if status >= 2, but if status = 5, I only want to show, data with created_at >=2022-06-01 AND NAME='ABC'

id	name	status	created_at
2	ABC	2	2022-01-01
3	ABC	3	2022-01-01
7	ABC	5	2022-06-01

I tried in SQL like below

WHERE name='ABC' and status BETWEEN 3 AND 4  OR status > 5 OR
      (status = 5 AND created_at >= '2022-01-01');

It works in SQL, and show as per expected. But I am confused on how to use it in Laravel controller. It still shows all data.

I tried

$query= TABLE::where('status','>=','3')->where('status','<=','4')->orWhere('status','5'5)->where('created_at','>=','2022-01-01)->where('name','ABC')->get();

Thank you in advance.

CodePudding user response：

I would phrase this as:

SELECT *
FROM yourTable
WHERE status BETWEEN 2 AND 4 OR status > 5 OR
      (status = 5 AND created_at >= '20220601');

This admits records having a status value from 2 to 4, strictly greater than 5, or equal to 5 with a created at date on or after June 1, 2022.

Your Laravel code might look like:

$query = TABLE::whereBetween('status', [2, 4])
    ->orWhere('status', '>', 5)
    ->orWhere(function($q) {
        $q->where('status', 5)
          ->where('created_at', '>=', '20220601');
    })
    ->get();

CodePudding user response：

As I understand that you want all data with status between 2-4 and if status is 5 than check if created_at >=2022-06-01 AND NAME='ABC'.

    $query = TableName::whereBetween('status', [2, 4])
    ->orWhere(function($q) {
        $q->where('status', 5)
          ->whereDate('created_at', '=', date('2022-06-01'));
    })
    ->get();