Queries in Laravel Logic operators

Question

I want to query Laravel DB for something like

SELECT * FROM `tickets` 
WHERE (created_at > '2015-07-01'
    AND created_at < '2015-07-31')
AND (state != 'Resolved'
    OR state != 'closed'
    OR state != 'Cancelled'
    OR state != 'Solution Rejected')
ORDER BY `id` DESC

I tried using raw statements but doesn't seem to work. So I'm trying to use Laravel's own model functions to archive the same result, but I'm missing something... here's what I got so far.

Ticket::whereBetween('created_at', [$start, $end])
->whereIn('state',['Resolved','closed','Cancelled','Solution Rejected'])
->get();

PS: alternatively is there a way I can have this instead

SELECT * FROM `tickets` 
WHERE ( (created_at > '2015-07-01'
    AND created_at < '2015-07-31')
    OR (updated_at > '2015-07-01'
    AND updated_at < '2015-07-31') )
AND (state != 'Resolved'
    OR state != 'closed'
    OR state != 'Cancelled'
    OR state != 'Solution Rejected')
ORDER BY `id` DESC

state != 'Resolved' OR state != 'closed' ... will always be true becuase state can't have multiple values at the same time — FuzzyTree
– FuzzyTree, Commented Aug 7, 2015 at 0:01

aldrin27 · Accepted Answer · 2015-08-07 09:18:15Z

1

Try this one sir:

  DB::table('tickets')
        ->whereBetween('created_at',[$start, $end])
        ->orWhere(function($query)
        {
            $query->where('state', '!=', 'Resolved')
                  ->where('state', '!=', 'closed')
                  ->where('state', '!=', 'Cancelled')
                  ->where('state', '!=', 'Solution Rejected')
                  ->orderBy('id', 'desc');
        })
        ->get();

edited Aug 7, 2015 at 9:18

answered Aug 7, 2015 at 0:34

aldrin27

3,4184 gold badges31 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aldrin27 Over a year ago

Try again sir. Sorry Double ")"

Thaenor Over a year ago

yeah I fixed that... It works as in it brings out results but I think it's not working logically as I want it. I think it's showing the dates OR not the states. But I need to double check. My hint is that a query that shouldn't return any results actually returned so many that the server timed out

Collectives™ on Stack Overflow

Queries in Laravel Logic operators

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related