Laravel query with date range and orWhere returns wrong results-CodePudding

need help in formulating eloquent query, I have this query which uses orWhere however I can't get the correct results when I wanted to added date range in the query.

comment table

id	content	user_id	post_id	created_at	is_owner
1	test	1	1	2022-07-09T04:28:50	false
2	test 1	2	2	2022-07-10T04:28:50	true
3	test 2	2	3	2022-07-11T04:28:50	true
4	test 3	2	2	2022-07-11T04:28:50	false
5	test 4	3	3	2022-07-12T04:28:50	true
6	test 5	2	2	2022-07-14T04:28:50	false
7	test 6	4	2	2022-07-14T04:28:50	false
8	test 7	5	1	2022-07-15T04:28:50	false

Assuming I have the table above with it's data and the login user is the owner of the comment.

Code

$comment = Comment::where(function ($query) use ($postIds, $userId) {
        $query->whereIn('post_id', $postIds)
            ->where('user_id', $userId);
    }
)

if ($isCommentOwner) {
    $comment->orWhere(function ($query) {
         ->where('is_owner', true);
    }); 
}

Using the code above I got the corrects results however when I tried to filter it out by date I can't get correct results.

The code above generate below query.

SELECT
   *
FROM
  `comments`
WHERE
(
    (
        `post_id` in (1, 2)
        AND `user_id` = 2
    )
     OR (`is_owner` = 1)
)
AND `document_issues`.`deleted_at` IS NULL  
ORDER BY `created_at` DESC

I wanted to filter the comment created from the given date range, I tried adding the ff. code.

$comment->whereDate('created_at', '>=', '2022-07-13')
$comment->whereDate('created_at', '<=', '2022-07-15');

However, I can't get the correct filtered results.

Filtering with date do work when I remove orWhere

Can someone help me with this?

Thank you.

CodePudding user response：

use whereBetween try this query

    $from ="2022-07-09";
    $to = "2022-07-12";
    $postIds = [1,2];
    $comment = Comment::where(function ($query) use ($postIds, $userId,$from, $to) {
        $query->whereIn('post_id', $postIds)
            ->where('user_id', $userId)
            ->whereBetween('created_at', [$from, $to]);
    }
    )->get();

CodePudding user response：

I missed an important thing. You need to provide an array to whereDate method:

// whereDate need a query as first parameter
// i think the method is usually used in closures.
$comment->whereDate($comment, ['created_at', '>=', '2022-07-13'])
$comment->whereDate($comment, ['created_at', '<=', '2022-07-15']);