Retrieve data based on specific date

Currently make some feature for seller able to download invoice based on month. So seller can choose which month that they want . Somehow my query doesnt work and keep taking wrong data . Anyone can help ?

$month = "3" ( STRING ) 
$seller_id = 1
// database 
posts
id | title | user_id |
1       test	1
2      test2	2
deals
id | post_id | status |updated_at 
1	1		Received	2020-03-01
2	1		Received	2020-03-01
3	2		Completed	2020-02-01
4	2		Dealing		2020-03-01
$deal = Deal::whereHas('post',function ($q) use ($seller_id){
	$q->where('user_id',$seller_id);
	})->whereMonth('updated_at',$month)->where('status','Received')->orWhere('status','Completed')->get();

expected result 
$deal = 2 ( id => 1,2 )

current result = 3  ( id => 1,2,4 )

tisuchi

6 years ago

Level 70

@fareedr

Can you try this?

$deal = Deal::whereHas('post',function ($q) use ($seller_id){
		$q->where('user_id',$seller_id);
	})
	->whereMonth('updated_at',$month)
	->where(function($q){
		$q->where('status','Received')
			->orWhere('status','Completed');
	})
	->get();

FareedR

6 years ago

Level 3

I solved it by using this method . But how can i merge with year ?

Deal::whereHas('post',function ($q) use ($seller_id){
                    $q->withoutGlobalScopes(['activePost'])->where('user_id',$seller_id);
                })->whereIn('status',['Received','Completed'])->whereMonth('updated_at',$month)->get();

tisuchi

6 years ago

Level 70

@fareedr I prefer here to follow this convention-

Use provide starting and ending month-year to fetch data.

For example- As a user, I want to fetch data from January 2020 to February 2020. Then behind the scene, you need to calculate-

The starting day of the given starting month. It will be like this 2020-01-01.
The ending day of the given ending month. It will be like this 2020-02-29.

Now, you just apply the query like this way-

...
...
->whereBetween('updated_at', [$from, $to])
...

Please or to participate in this conversation.