Home / Questions / 542300
Accepted
Miguel Angel Blanco Gonzalez
Asked: 2022-08-24 00:32:02 +0800 CST 2022-08-24 00:32:02 +0800 CST

Query with null in Laravel

  • 772

I have a table sales_orders in this table I have 3 important fields:

  • housing(numeric)
  • status(enum)
  • shipping_status(enum)

I need to make a query that filters me by the state when it is active and that the housing field is different from zero, but that nullreturns them, that is, to clarify a bit the query would be something like this:

$venta=venta_pedidos::where('estado','aceptado')->where('vivenda','!=',0)->where('estado_envio','pendiente')->get();

But with this it does not return the ones nullfrom the housing field and if I enter the function it orWhereNull()uses all of them nullwithout exception, it does not look at the state they are in.

laravel

3 Answers

  1. In housing you have to indicate the field you want it to look at in this case housing:

    $venta = venta_pedidos::where('estado','aceptado')->where('vivenda','!='0)->where('estado_envio','pendiente')->orWhereNull('vivenda')->get();

    I have tried that yesterday in a project and it has worked, that should help you.

    Take a look at laravel advanced queries.

    • 0

  2. I have it solved:

    It was nonsense, it turns out that I wasn't taking into account that if you put the orWhere() clause you have to specify the restrictive conditions again, so repeating the states again and updating the clause would do the trick, it would stay like this:

    $venta=venta_pedidos::where([['estado','aceptado'],['vivenda','!=',0],['estado_envio','pendiente']])->orWhere(['estado','aceptado'],['vivenda',null],['estado_envio','pendiente'])->get();
    • 0
  3. Best Answer

    I think something like this could work for you:

    $venta = venta_pedidos::where('estado','aceptado')
    ->where('estado_envio','pendiente')
    ->where(function ($query) {
        $query->where('vivenda', '<>', '0')
            ->orWhereNull('vivenda');
    })
    ->get();

    If I'm not mistaken this will generate one SQLsimilar to this:

    SELECT * FROM venta_pedidos WHERE estado = 'aceptado' AND estado_envio = 'pendiente' AND (vivienda <> 0 OR vivienda IS NULL);

    Taking all those that have status = accepted, status_envio = pending and housing different from 0 or that is NULL

    • 0