construir sql dinamico con if

[banthas]

buenos dias a todos tengo un problema con una consulta
este es mi codigo

Código SQL [-]
CREATE OR REPLACE FUNCTION consulta(op integer) 
RETURNS smallint AS $$
DECLARE
sql_str varchar(1000);

BEGIN

sql_str= 'SELECT alumno.nombre,alumno.curso, operador.gestion, COUNT(*)
from alumno
inner join operador on datos.id_operador=operador.id_operador
where 1=1';

IF op <> 0  THEN
sql_str =+'datos.id_operador in('||op||')';
END IF;


execute sql_str;
END
$$
LANGUAGE 'plpgsql';

la idea es contruir el sql segun los valores que me manden en los parametros si es cero y no enviaron nada muestra todos los datos sino entonces lo que manden
no se si se puede hacer de esta forma

gracias por su tiempo

[[AgustinOrtu]]

Primero que nada bienvenido

Segundo, te van a retar por no poner los tag al codigo SQL

Con repecto a la consulta, algo como esto? no te serviria?

[Casimiro Notevi]

Empezado por AgustinOrtu

Segundo, te van a retar por no poner los tag al codigo SQL

Exacto

Banthas, bienvenido a clubdelphi, como siempre aconsejamos a los nuevos, no olvides leer nuestra guía de estilo, gracias por tu colaboración

Y recuerda poner los tags al código fuente, ejemplo:



Gracias

[mamcx]

Empezado por banthas

este es mi codigo

El SQL que pones:

1- Esta errado (o incompleto?)
2- Tiene una condicion innecesaria
3- La concatenacion tiene 2 errores
4- Usa una clausula que aunque correcta en sintaxis y comportamiento, no es la que se usa normalmente para comparar contra un solo valor
5- La invocacion del metodo tiene error

Si el SQL directo no funciona, menos va a funcionar de forma dinamica.

Por otro lado... lo que haces es la idea y por lo tanto la pregunta estrictamente hablando ya te la respondiste vos mismo...

[[AgustinOrtu]]

lo del where 1=1 lo hace para ya "tener" un where. No se si me explico

El problema que se enfrentan es siempre el mismo:

Lo que esta entre [] vendria a ser opcional en mi pseudocodigo

Código Delphi [-]
TPersonGroup = (pgSoltero, pgCasado...etc)
TPersonGroups = set of TPersonGroup;
function Buscar(const Nombre, Apellido: string;  const Grupos: TPersonGroups = []): TDataSet;
begin
  q := CrearQueryMagicamente;
  [1] q.SQL.Text := ' SELECT [Campos] FROM Tabla1 [INNER JOIN Tabla2 (ON T1.Id = T2.IdTabla1)] ';
end;

Llegados al punto [1] si, ejecuto ese SQL, que pasa, me trae "todo". Obvio, no filtra no hay where

Ahora, seria deseable poder hacer esto

Código Delphi [-]
  if Apellido <> EmptyStr then
  begin
    q.SQL.Add(' Apellido LIKE :Ape ');
   q.ParamByName('Ape').AsString := Apellido + ''%'';
  end;

  if Nombre <> EmptyStr then
  begin
    q.SQL.Add(' Nombre LIKE :Nom');
   q.ParamByName('Nom').AsString := Nombre + ''%'';
  end;

  if Grupos <> [] then
    q.SQL.Add(Format(' Grupo IN (%s)', [SetToCSV(Grupos)]));

Las preguntas del millon:

1. Quien pone el WHERE? si todos los filtros vienen "vacios" entonces si pongo el where la consulta se rompe

2. Quien pone los AND? O los OR?


Ese es basicamente el problema que tiene

[mamcx]

Cierto, SQL es un lenguaje que no esta hecho para programar en el, sino para usarlo de forma "ad-hoc", lo cual es desafortunado, porque el modelo relacional no es tan limitado.

Como hacer eso no es tan dificil, despues de todo. Quien pone el WHERE? Lo mismo de quien pone todo lo demas, manualmente: El SQL no se puede modificar, solo re-crear desde zero una y otra vez.

Hacer un codigo que genere el SQL de forma generica puede ser un poco molesto y repetitivo, pero para el caso en particular de este hilo es muy simple: Si hay que filtrar por "op" es solo agregar " WHERE datos.id_operador = ?" y ya.

[banthas]

mil disculpas la verdad es q estoy aprendiendo a programar y me va peor con las consultas en base de datos.... tengo esta consulta en java

Código Delphi [-]

String sql = "SELECT nombre,gestion,curso,COUNT(*)  FROM alumno ";

    if (var.getnombre().equals("-") && var.getcurso().equals("-") ) {
      sql += "";
    } else {
      sql += " WHERE 1 = 1";
      if (!var.getOperador().equals("-")) {
        sql += " and id_operador in (" + var.getnombre() + ")";
      }
      if (!var.getServicio().equals("-")) {
        sql += " and id_servicio in (" + var.getcurso() + ")";
      }
    }

    sql += " GROUP BY nombre,gestion,curso";

esto quiero hacerlo en una consulta sql y la verdad ni siquiera se porq caminos me fui perdon

[[AgustinOrtu]]

Empezado por mamcx

Cierto, SQL es un lenguaje que no esta hecho para programar en el, sino para usarlo de forma "ad-hoc", lo cual es desafortunado, porque el modelo relacional no es tan limitado.

Como hacer eso no es tan dificil, despues de todo. Quien pone el WHERE? Lo mismo de quien pone todo lo demas, manualmente: El SQL no se puede modificar, solo re-crear desde zero una y otra vez.

Hacer un codigo que genere el SQL de forma generica puede ser un poco molesto y repetitivo, pero para el caso en particular de este hilo es muy simple: Si hay que filtrar por "op" es solo agregar " WHERE datos.id_operador = ?" y ya.

No es tan sencillo. Si tenes multiples parametros de filtro la cosa se complica. Usando el codigo de ejemplo de mi anterior post, que pasa ante esta llamada:

Buscar('Juan', '', [pgCasado])

el SQL va quedando asi:

Código Delphi [-]
SELECT Campos
FROM Tablas [JOIN de ser necesarios]
WHERE

Primero pregunta por Apellido, como mande nada, sige
Despues pregunta por Nombre, como mande Juan, agrega la sentencia SQL y el parametro
Despues hace lo del grupo, como le pedi los Casados, agrega la sentencia, pero me falta un AND en el medio, quien lo pone?

Si lo pongo despues de poner "Juan", como me aseguro que despues alguien va a poner algo despues de mi AND?

Y si antes de agregar lo del grupo, pongo un AND, como me aseguro que antes habia una condicion?

Yo lo termine solucionando a lo bestia, poniendo siempre el AND (u OR) al final, y despues sacandoselo a la ultima linea. A lo bestia si, pero hacer algo generico como decis vos, es bastante molesto

En un mundo feliz, habria que implementar alguna clase que opere estilo LINQ, y vaya armando el SQL solita, masomenos asi:

Código Delphi [-]
SQL := Select(Campos).From(Tabla).Where(Condicion1).And(Condicion2).Or(Condicion3).Order(..)

Eso si, despues te regalo los join, las funciones agregadas, los group by, etc

[mamcx]

Sip, armar SQL con la especificacion completa es practicamente re-implementar un Parser de SQL. Sin embargo, el caso de los where es relativamente simple.

Uno de los ORMS que hice (obj-c) lo muestra:

https://bitbucket.org/elmalabarista/...e-view-default

Código PHP:

+(NSString *) buildFilterCriteria: (NSArray *)filterList {
    NSMutableString * sql = [NSMutableString string];
    int count;
    int i;

    [sql appendString :@" WHERE "];
    
    count = [filterList count];
    
    for (i = 0; i < count; i++) { 
        if (i + 1 == count) {
            [sql appendFormat:@"(%@)", [filterList objectAtIndex:i] ];
        }
        else {
            [sql appendFormat:@"(%@) AND ", [filterList objectAtIndex:i] ];
        }
    } 
    
    return sql;
}

+(NSString *) buildSelect: (NSString *)tableName fields:(NSArray *)fields filters:(NSArray *)filterList orders:(NSArray *)orderList {
...
...
    //Add the filters
    if (filterList) {
        [sql appendString: [SqlGenerator buildFilterCriteria:filterList]];
    } 


---

OFF-TOPIC:

Ahora estoy en la tarea (por hobby) de hacer un lenguaje de programacion relacional, donde es normal programar estilo LINQ, asi que tengo por ejemplo esto:

Código SQL [-]
data = [1, 3, 4, 2, 8, 1]
q1a = where data ? this < 4 end
q1b = where data ? this > 0 end

q1 = q1a && q1b

q2 = select data ? this * 2 end

qFinal = orderby q1 && q2 ? this end

for row in qFinal do
     row | print
end

La idea es que es redudante hacer "SELECT campo1, ..." en el caso de

Código SQL [-]
where data ? this < 4 end

, y como el lenguaje es relacional, puede hacer composicion de querys (unir q1 & q2 que combina la projeccion y el filtro y luego aplica el orden)

Para que quede claro."where data ? this < 4 end" no requiere decirle "select" ni "from" porque es redundante con lo que ya se. Esa es una de las cosas que le falto al SQL para hacerlo mas amigable.

Esto no es usando una base de datos relacional, todo es en memoria. Pasarlo a SQL es algo extra que se resuelve mucho mas facil si en vez de hacer concatenacion de STRINGs genero un AST y un mini-interprete de eso para armar los SQL.

[roman]

Empezado por AgustinOrtu

Primero pregunta por Apellido, como mande nada, sige
Despues pregunta por Nombre, como mande Juan, agrega la sentencia SQL y el parametro
Despues hace lo del grupo, como le pedi los Casados, agrega la sentencia, pero me falta un AND en el medio, quien lo pone?

Si lo pongo despues de poner "Juan", como me aseguro que despues alguien va a poner algo despues de mi AND?

Y si antes de agregar lo del grupo, pongo un AND, como me aseguro que antes habia una condicion?

Yo lo termine solucionando a lo bestia, poniendo siempre el AND (u OR) al final, y despues sacandoselo a la ultima linea. A lo bestia si, pero hacer algo generico como decis vos, es bastante molesto

Quizá no estoy entendiendo el punto, pero a mi entender, el problema aquí está en querer construir el texto de la sentencia SQL secuencialmente sobre la marcha.

En lugar de tratar de ver si hay condiciones antes o después, mejor junta todas y, al final, armas la cláusula, si es que quedó algo. Por ejemplo,

Código PHP:

$condiciones = array(); // en este arreglo vamos a almacenar las posibles condiciones

if (!empty($nombre)) {
  $condiciones[] = "nombre like $nombre"; // agregamos la condicón del nombre
}

if (!empty($grupos)) {
  $condiciones[] = "grupo in $grupos"; // agregamos la condición de grupos
}

if (!empty($edad)) {
  $condiciones[] = "edad > $edad"; // agregamos la condición de edad
} 


Cada condición se agrega sin necesidad de saber si antes o después hay otra. Entonces, al final, sólo miramos si el arreglo de condiciones está vacío y, si no lo está, aglutinamos los elementos del arreglo con un AND:

Código PHP:

$whereStr = "";

if (!$empty($condiciones)) {
  $whereStr = ' WHERE ' . implode(' AND ', $condiciones); // cláusula where + las condiciones aglutinadas
} 


El ejemplo está en PHP para fastidiar un poco a Mario, , pero puede adaptarse. Lo esencial es:

• Recabar las condiciones de forma independiente
• Armar la consulta una vez que todas las condiciones estén recabadas.

// Saludos

[banthas]

les comento que tras mucho pelear logre sacar lo que queria con un pequeño defecto las columnas me salen unidas es por el type verdad?

Código Delphi [-]
CREATE OR REPLACE FUNCTION consulta2(nom text, curso text,gestion int) RETURNS setof prueba AS 
$BODY$

DECLARE
    r prueba%ROWTYPE;
    sql text;
BEGIN
sql := 'SELECT alumno.nombre,alumno.gestion,alumno.curso,operador.detalle,count(*) as total FROM alumno
inner join operador on alumno.id_operador=operador.id_operador
 WHERE 1=1 ';
 IF nom <>0 THEN sql := sql || ' AND alumno.nombre in('||nom||')'; END IF;
 IF curso <>0 THEN sql := sql || ' AND alumno.curso in('||curso||')'; END IF;
 IF gestion <>0 THEN sql := sql || ' AND alumno.gestion in('||gestion||')'; END IF;

 
sql:=sql || 'GROUP BY alumno.nombre,alumno.gestion,alumno.curso,operador.detalle';

for r in execute sql loop
    return next r;
end loop;

end;
$BODY$

LANGUAGE plpgsql VOLATILE
COST 100;

bueno lo que quiero saber ahora es si puedo dividirlas despues de crearlas o que me aconcejan??

muchas gracias por la ayuda !

[[AgustinOrtu]]

Roman muy interesante tu propuesta, nunca se me ocurrió encararlo así. En realidad nunca queme muchas neuronas en esto, prefiero ser más práctico y meter sql mas a lo bestia y no armarlo en forma dinámica, sólo por simpleza

Ya veré si hago algo más sofisticado en mi próximo emprendimiento, por el momento estoy haciendo todo con clases no persistentes, pero el sistema va tomando forma y puedo hacer test sin ningún problema. Ya cuando este todo terminado haré la parte de persistencia.

[banthas]

Bueno siquiera de algo le servira a alguien espero... pero sigo con la duda por fa como podria obtener el resultado en columnas?

[mamcx]

Ah ya te entendi!

El problema es que le diste "setof" como tipo de retorno y eso significa que te retorna una TUPLA, no registros. A proposito, es redundante que hagas ese FOR.

Checa bien los DOCS de como hacer las funciones en postgresql. PG tiene un muy amplio abanico de caracteristicas que hacen que sea muy potente y flexible pero por otro lado hay que leer un poco porque si vienes por ejemplo, de MySql, puede no ser tan obvio como mapear las cosas...

[banthas]

estimado mamcx segui tu concejo y busque en la documentacion pero no entiendo como hacer que me retorne las columnas de mi consulta lo hice tal cual indica

Código Delphi [-]
CREATE OR REPLACE FUNCTION consulta2(
    nombre text,
    curso text,
    gestion  int,
)
  RETURNS table (nombre text, gestion int, curso text, operador text, total int) AS
$$

DECLARE
 
    sql text;
BEGIN
sql := 'SELECT alumno.nombre as nombre,alumno.gestion as gestion,alumno.curso as curso,operador.detalle as operador,count(*) as total FROM alumno
inner join operador on alumno.id_operador=operador.id_operador
 WHERE 1=1 ';
 IF nom <>0 THEN sql := sql || ' AND alumno.nombre in('||nom||')'; END IF;
 IF curso <>0 THEN sql := sql || ' AND alumno.curso in('||curso||')'; END IF;
 IF gestion <>0 THEN sql := sql || ' AND alumno.gestion in('||gestion||')'; END IF;

 
sql:=sql || 'GROUP BY alumno.nombre,alumno.gestion,alumno.curso,operador.detalle';
execute sql;
end;
$$
  LANGUAGE plpgsql;

me devuelve las columnas en blanco


por fa alguna sugerencia

[banthas]

Je ya encontre la solucion creo que me sirvio simplemente preguntar y ya solito me respondi
Dejo el codigo talvez a alguien le sirva

Código Delphi [-]

CREATE OR REPLACE FUNCTION consulta2(
    nombre text,
    curso text,
    gestion  int,
)
  RETURNS table (nombre text, gestion int, curso text, operador text, total int) AS
$$

DECLARE
 
    sql text;
BEGIN
sql := 'SELECT alumno.nombre as nombre,alumno.gestion as gestion,alumno.curso as curso,operador.detalle as operador,count(*) as total FROM alumno
inner join operador on alumno.id_operador=operador.id_operador
 WHERE 1=1 ';
 IF nombre <>0 THEN sql := sql || ' AND alumno.nombre in('||nombre||')'; END IF;
 IF curso <>0 THEN sql := sql || ' AND alumno.curso in('||curso||')'; END IF;
 IF gestion <>0 THEN sql := sql || ' AND alumno.gestion in('||gestion||')'; END IF;

 
sql:=sql || 'GROUP BY alumno.nombre,alumno.gestion,alumno.curso,operador.detalle';

for var in execute sql loop
nombre:= var.nombre
curso:= var.curso;
 gestion:=var.gestion;
 operador:=var.operador;
 total:=var.total;
 return next;
end loop;
end;
$$
  LANGUAGE plpgsql;