PDA

Ver la Versión Completa : construir sql dinamico con if


banthas
22-10-2015, 19:38:54
buenos dias a todos tengo un problema con una consulta
este es mi codigo
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
22-10-2015, 20:07:19
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? (http://www.clubdelphi.com/foros/showthread.php?t=89104) no te serviria?

Casimiro Notevi
22-10-2015, 20:10:07
Segundo, te van a retar por no poner los tag al codigo SQL
Exacto :D

Banthas, bienvenido a clubdelphi, como siempre aconsejamos a los nuevos, no olvides leer nuestra guía de estilo (http://www.clubdelphi.com/foros/guiaestilo.php), gracias por tu colaboración :)

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

http://www.clubdelphi.com/images/UtilizarTAGs.png

Gracias :)

mamcx
22-10-2015, 20:20:08
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
22-10-2015, 20:39:32
lo del where 1=1 lo hace para ya "tener" un where. No se si me explico :D

El problema que se enfrentan es siempre el mismo:

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


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


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
22-10-2015, 21:47:49
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
22-10-2015, 21:56:09
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


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
22-10-2015, 22:40:39
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:


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:

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
22-10-2015, 23:00:55
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/chibiorm/src/2b6f2ad28b745b6bc2dbccd81c9bbd5da9fe6344/src/SqlGenerator.m?at=default&fileviewer=file-view-default



+(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:


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 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 (https://en.wikipedia.org/wiki/Abstract_syntax_tree) y un mini-interprete de eso para armar los SQL.

roman
23-10-2015, 18:28:20
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,


$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:


$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, :p, 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
23-10-2015, 18:47:57
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?

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?? :confused:

muchas gracias por la ayuda !

AgustinOrtu
23-10-2015, 22:36:50
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
23-10-2015, 23:53:54
Bueno siquiera de algo le servira a alguien espero... pero sigo con la duda por fa como podria obtener el resultado en columnas?

mamcx
24-10-2015, 00:05:09
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
26-10-2015, 04:33:08
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


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
26-10-2015, 04:56:17
Je ya encontre la solucion creo que me sirvio simplemente preguntar y ya solito me respondi
Dejo el codigo talvez a alguien le sirva



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;