how make this query in filament php?

Hi how i can replicate this query in filament php?

  CAST(u.id AS CHAR) AS id,
  u.estado,
  u.nombre,
  u.cedula,
  GROUP_CONCAT(DISTINCT ts.ip SEPARATOR '\n ') AS ip,
  GROUP_CONCAT(DISTINCT ts.direccion SEPARATOR '\n ') AS direccion_servicio,
  SUM(
    CASE
      WHEN fa.estado = 'No pagado' THEN fa.total
      ELSE 0
    END
  ) AS deuda_total,
  COUNT(
    CASE
      WHEN fa.estado = 'No pagado' THEN 1
    END
  ) AS cantidad_facturas_no_pagadas,
  GROUP_CONCAT(DISTINCT ts.instalado SEPARATOR '\n ') AS instalado
FROM
  usuarios u
  LEFT JOIN tblservicios ts ON u.id = ts.idcliente
  LEFT JOIN facturas fa ON u.id = fa.idcliente
WHERE
  (
    {{ ! filtroNombre.value }}
    OR u.nombre LIKE {{ '%' + filtroNombre.value + '%' }}
  )
  AND (
    {{filtroEstado.value.length === 0}}
    OR u.estado IN ({{ filtroEstado.value }})
  )
  AND (
    {{ ! filtroCedula.value }}
    OR u.cedula = {{ filtroCedula.value }}
  )
GROUP BY
  u.id,
  u.nombre,
  u.cedula,
  u.direccion_principal;

  CAST(u.id AS CHAR) AS id,
  u.estado,
  u.nombre,
  u.cedula,
  GROUP_CONCAT(DISTINCT ts.ip SEPARATOR '\n ') AS ip,
  GROUP_CONCAT(DISTINCT ts.direccion SEPARATOR '\n ') AS direccion_servicio,
  SUM(
    CASE
      WHEN fa.estado = 'No pagado' THEN fa.total
      ELSE 0
    END
  ) AS deuda_total,
  COUNT(
    CASE
      WHEN fa.estado = 'No pagado' THEN 1
    END
  ) AS cantidad_facturas_no_pagadas,
  GROUP_CONCAT(DISTINCT ts.instalado SEPARATOR '\n ') AS instalado
FROM
  usuarios u
  LEFT JOIN tblservicios ts ON u.id = ts.idcliente
  LEFT JOIN facturas fa ON u.id = fa.idcliente
WHERE
  (
    {{ ! filtroNombre.value }}
    OR u.nombre LIKE {{ '%' + filtroNombre.value + '%' }}
  )
  AND (
    {{filtroEstado.value.length === 0}}
    OR u.estado IN ({{ filtroEstado.value }})
  )
  AND (
    {{ ! filtroCedula.value }}
    OR u.cedula = {{ filtroCedula.value }}
  )
GROUP BY
  u.id,
  u.nombre,
  u.cedula,
  u.direccion_principal;

and show it in a table

Solution:

Relationships are Laravel level. You need to understand Eloquent the ORM of Laravel

Jump to solution

4 Replies

agaitan026OP•2mo ago

i already got some regular columns added:

    return $table
            ->columns([
                //
                TextColumn::make('id'),

    return $table
            ->columns([
                //
                TextColumn::make('id'),

Matthew•2mo ago

You can't skip from database to filament without understanding laravel. You need to create the right relationships within you parent table model to be able to have filamnet grab this sort of data. The rough and ready sidestep to this, is if you only need to read the table, and you understand SQL views, then you can do it that way.

agaitan026OP•2mo ago

by creating relationship you mean at sql level or laravel level

Solution

Dennis Koch•2mo ago

Relationships are Laravel level. You need to understand Eloquent the ORM of Laravel

Gaming

Programming

how make this query in filament php?