I need optimize this query, I have this instruction

 $tickets = BITicket::query();
 $tickets = $tickets->whereIn("Status_Code", $status);
 ...
 $tickets = $tickets->with("customer")->get();

after I get the records I use a for to check a condition with to fields on the same table

foreach ($tickets as $key => $value) {
        if ($value->Tipo_de_Pago_Ticket == "some number") {
            if ($value->Subject != "Some Value") {
                $tickets->fet($key);
            }
        }
    }

so, I need change the for for another option inside the query, I tried with when funcion, subqueries but doesn't work.

somebody know how make this istruccion $tickets->fet($key); inside the query

I need optimize this query, I have this instruction

 $tickets = BITicket::query();
 $tickets = $tickets->whereIn("Status_Code", $status);
 ...
 $tickets = $tickets->with("customer")->get();

after I get the records I use a for to check a condition with to fields on the same table

foreach ($tickets as $key => $value) {
        if ($value->Tipo_de_Pago_Ticket == "some number") {
            if ($value->Subject != "Some Value") {
                $tickets->fet($key);
            }
        }
    }

so, I need change the for for another option inside the query, I tried with when funcion, subqueries but doesn't work.

somebody know how make this istruccion $tickets->fet($key); inside the query

• laravel
• database
• eloquent

• 1 It's not clear why you can't use the conditions in the query. For example why can't you do $tickets = $tickets->where('Tipo_de_Pago_Ticket', 'some number')->where('Subject', '!=', 'Some Value')->whereIn('Status_Code', $status); ? – apokryfos Commented Mar 12 at 20:26
• @apokryfos because only need skip an specific Tipo_de_Pago_Ticket with one specific Subject value, for example if Tipo_de_Pago_Ticket = 1 and subject in [1,2,3,4,5] pass but if Tipo_de_Pago_Ticket = 1 and subject in 6 skip but Tipo_de_Pago_Ticket = 2 and subject in [1,2,3,4,5] pass, if I apply the filter $tickets->where('Tipo_de_Pago_Ticket', '1') all Tipo_de_Pago_Ticket != 1 wil be ignored – Mike Commented Mar 12 at 23:27
• @Mike , Hi do you want to exclude particular data from the $tickets if $ticket->Tipo_de_Pago_Ticket is "some number" and $ticket->Subject is "Some Value" --these two conditions met? Please clarify. – Subha Commented Mar 13 at 5:22
• 1 If you can define the SQL query that gets your correct result then you can also specify the eloquent equivalent. What you are describing sounds like something you can get using only SQL without any need for in-code filtering after you get all the results so why not try doing that? – apokryfos Commented Mar 13 at 8:13

1 Answer 1

Try something like this:

$tickets = BITicket::query()
    ->whereIn("Status_Code", $status)
    ->where(function ($query) {
        $query->where('Tipo_de_Pago_Ticket', '!=', 'some number')
              ->orWhere(function ($q) {
                  $q->where('Tipo_de_Pago_Ticket', 'some number')
                    ->where('Subject', 'Some Value');
              });
    })
    ->with('customer')
    ->get();

Explanations:

MySQL doesn't have IF(well, it does, but it a different kind), we can use where and orWhere instead. And grouping with ->where(function ($query) or ->orWhere(function ($q) act as brakets. Your SQL will look like this

SELECT * FROM bit_tickets
WHERE Status_Code IN ('some status', 'other status')
  AND (
        Tipo_de_Pago_Ticket != 'some number'
        OR (Tipo_de_Pago_Ticket = 'some number' AND Subject = 'Some Value')
      )

See, some wheres are grouped with brackets and whole grouped condition must be true. Laravel's default ->where() acts as AND.