php - How to create an EXCEL file avoiding deleted items

Question

Currently, my Excel file is being created showing all the records in DB. The problem is that it shows the deleted items too.

Here is the code that I've for my current excel file:

public function collection()
{
    $resultados = collect( DB::select('SELECT
articulos.id,
articulos.codigo_comercial,
articulos.codigo_interno,
articulos.descripcion,
coalesce( articulos.marca, 'SIN MARCA' ) as marca,
coalesce( articulos.modelo, 'SIN MODELO' ) as modelo,
coalesce( articulos.categoria,'SIN CATEGORIA' ) as categoria,
if( articulos.subcategoria = 'null', 'SIN CATEGORIA', coalesce( articulos.subcategoria, 'SIN CATEGORIA' ) ) as subcategoria,
(SELECT existencia FROM costo_promedio_historico WHERE costo_promedio_historico.articulo_id = articulos.id ORDER BY created_at DESC LIMIT 1) as existencia,
articulos.unidad_medida,
articulos.codigo_producto_sat,
articulos.codigo_unidad_sat,
CASE
    WHEN JSON_EXTRACT( articulos.impuestos_compra, "$[0]" ) = "EXENTO" THEN 'EXENTO 0%'
    WHEN JSON_EXTRACT( articulos.impuestos_compra, "$[0]" ) = "IVA" THEN 'IVA 16%'
    ELSE 'SIN IMPUESTOS CONFIGURADOS'
END as impuestos_compra,
CASE
    WHEN JSON_EXTRACT( articulos.impuestos_venta, "$[0]" ) = "EXENTO" THEN 'EXENTO 0%'
    WHEN JSON_EXTRACT( articulos.impuestos_venta, "$[0]" ) = "IVA" THEN 'IVA 16%'
    ELSE 'SIN IMPUESTOS CONFIGURADOS'
END as impuestos_venta,
FORMAT( ( SELECT listas_precios_articulos.costo_compra FROM listas_precios_articulos WHERE listas_precios_articulos.articulo_id = articulos.id ORDER BY listas_precios_articulos.created_at DESC LIMIT 1 ), 2 ) as costo_compra,
( SELECT listas_precios_articulos.tipo_utilidad FROM listas_precios_articulos WHERE listas_precios_articulos.articulo_id = articulos.id ORDER BY listas_precios_articulos.created_at DESC LIMIT 1 ) as tipo_utilidad,
FORMAT( ( SELECT listas_precios_articulos.porcentaje FROM listas_precios_articulos WHERE listas_precios_articulos.articulo_id = articulos.id ORDER BY listas_precios_articulos.created_at DESC LIMIT 1 ) ,2) as porcentaje,
FORMAT( ( SELECT listas_precios_articulos.precio_venta FROM listas_precios_articulos WHERE listas_precios_articulos.articulo_id = articulos.id ORDER BY listas_precios_articulos.created_at DESC LIMIT 1 ), 2 ) as precio_venta,
FORMAT( ( SELECT listas_precios_articulos.utilidad FROM listas_precios_articulos WHERE listas_precios_articulos.articulo_id = articulos.id ORDER BY listas_precios_articulos.created_at DESC LIMIT 1 ), 2 ) as utilidad FROM articulos')
    );

    return $resultados;
}

If I write this right after FROM articulos the excel is created but empty:

WHERE articulos.deleted_at = NULL

How can I avoid all my deleted items?

I want my output to show only results when deleted_at == NULL

Regards.

question from:https://stackoverflow.com/questions/65851169/how-to-create-an-excel-file-avoiding-deleted-items

Answer 1

In MySQL ... = NULL always returns NULL. Use the IS / IS NOT keyword:

WHERE articulos.deleted_at IS NULL