Manejo de cláusulas WHERE complejas con un generador de consultas PHP

Hay varias librerías de generador de consultas con estilo ActiveRecord por ahí. Algunos son independientes y algunos vienen integrados en frameworks . Sin embargo, realmente tienen problemas con las cláusulas WHERE y HAVING cuando se trata de SQL complejo. Poniendo a un lado otras bases de datos – Estoy tratando de encontrar un método WHERE () compatible con MySQL y PostgreSQL que pueda solucionar las fallas de este método actual.

Lo que sigue es una larga list de ideas y ejemplos que muestran lo mejor que pude llegar hasta ahora. Sin embargo, parece que no puedo resolver todos los casos de uso y siento que mi solución parcial es descuidada. Cualquiera que pueda responder con algo que solucione todos estos problemas no solo responderá a esta pregunta, sino que será responsable de solucionar un problema que ha perseguido las implementaciones de PHP durante varios años.

Operadores comunes

= Equal <> Not Equal > Greater Than < Less Than >= Greater Than Or Equal <= Less Than Or Equal BETWEEN between values on right NOT logical NOT AND logical AND OR logical OR 

Ejemplo donde cláusulas

 SELECT ... FROM table... WHERE column = 5 WHERE column > 5 WHERE column IS NULL WHERE column IN (1, 2, 3) WHERE column NOT IN (1, 2, 3) WHERE column IN (SELECT column FROM t2) WHERE column IN (SELECT c3 FROM t2 WHERE c2 = table.column + 10) WHERE column BETWEEN 32 AND 34 WHERE column BETWEEN (SELECT c3 FROM t2 WHERE c2 = table.column + 10) AND 100 WHERE EXISTS (SELECT column FROM t2 WHERE c2 > table.column) 

Hay muchos formattings comunes de ActiveRecord que la cláusula where () usa en las diferentes bibliotecas actuales .

 $this->db->where(array('session_id' => '?', 'username' => '?')); $this->db->fetch(array($id, $username)); // vs with is_int($key) $this->db->where(array('session_id', 'username')); $this->db->fetch(array($id, $username)); // vs with is_string($where) $this->db->where('session_id', '?'); $this->db->where('username'); $this->db->fetch(array($id, $username)); // vs with is_array($value) $this->db->where('session_id', '?'); $this->db->where('username', array('Sam', 'Bob')); $this->db->fetch(array($id)); 

Aquí está el formatting final que tengo hasta ahora. Debería manejar la agrupación (...) AND (...) así como los parametros enlazados de statement preparados ("?" & ": Name").

 function where($column, $op = '=', $value = '?', $group = FALSE){} // Single line $this->db->where('column > 5'); $this->db->where('column IS NULL'); // Column + condition $this->db->where('column', '='); // WHERE column = ? (prepanetworking statement) $this->db->where('column', '<>'); // WHERE column <> ? (prepanetworking statement) // Column + condition + values $this->db->where('column', '=', 5); // // WHERE column = 5 $this->db->where('column', 'IN', '(SELECT column FROM t2)'); // WHERE column IN (SELECT column FROM t2) $this->db->where('column', 'IN', array(1,2,3)); // WHERE column IN (1, 2, 3) $this->db->where('column', 'NOT IN', array(1,2,3)); // WHERE column NOT IN (1, 2, 3) // column + condition + values + group $this->db->where( array( array('column', '<', 20), array('column', '>', 10) ), NULL, NULL, $group = TRUE ); // WHERE (column < 20 AND column > 10) 

:ACTUALIZAR:

En el transcurso de mi pregunta, me di count de que DONDE y TENIENDO las condiciones solo se vuelven más complejas cuanto más profundo vas. Intentar abstraer incluso el 80% de las características daría como resultado una biblioteca masiva solo para WHERE y HAVING. Como señala Bill, eso no es razonable para un lenguaje de scripting como PHP.

La solución es simplemente crear a mano la parte DONDE de su consulta. Siempre que use " alnetworkingedor de sus columnas puede usar la misma consulta WHERE en Postgre, SQLite y MySQL, ya que usan casi la misma syntax SQL. (Para MySQL debe str_replace() con un tic ').

Llega un momento en que la abstracción duele más de lo que ayuda, DONDE las condiciones son uno de esos lugares.

Trabajé bastante en la biblioteca Zend_Db , que incluye una class PHP para build consultas SQL . Decidí apostar a intentar manejar cada syntax SQL imaginable en las cláusulas WHERE y HAVING , por varias razones:

  • PHP es un lenguaje de scripting que analiza y comstack el código en cada request (a less que use un bytecode caching). Por lo tanto, el entorno PHP es sensible a las bibliotecas de códigos voluminosos, más que Java o C # o Python, o lo que sea. Por lo tanto, es una prioridad mantener las bibliotecas lo más escasas posible.

    Toda la biblioteca Zend_Db que trabajé tenía unas 2.000 líneas de código PHP. Por el contrario, Java Hibernate está en el order de 118K líneas de código. Pero eso no es un problema ya que una biblioteca Java está precomstackda y no tiene que cargarse en cada request.

  • Las expresiones SQL siguen una gramática generativa que es más compacta, y más fácil de leer y mantener que cualquiera de las construcciones basadas en PHP que mostró. Aprender la gramática de expresiones SQL es mucho más fácil que aprender una API que pueda simularlo. Terminas apoyando una "gramática simplificada". O bien comienzas de esa manera y te encuentras coaccionado por tu comunidad de usuarios en Feature Creep hasta que tu API sea inusualmente compleja.

  • Para depurar una aplicación que usó dicha API, inevitablemente necesitaría acceder a la expresión SQL final, por lo que se trata de la abstracción más filtrada que pueda tener.

  • La única ventaja de utilizar una interfaz basada en PHP para expresiones SQL sería que ayuda a completar el código en editores inteligentes e IDE. Pero cuando tantos de los operadores y operandos usan constantes de cadena como '>=' , arruinas cualquier inteligencia de terminación de código.


actualización: Acabo de leer un buen artículo de blog " Adiós a los ORM ". El escritor, Aldo Cortesi, sugiere usar el Lenguaje de Expresión SQL en SQLAlchemy de Python. La sobrecarga sintáctica de azúcar y operadores que es estándar en Python (pero no soportada en PHP) hace que esta sea una solución de generación de consultas muy efectiva.

También puede ver DBIx :: Class de Perl, pero termina siendo bastante feo.

Esto es parte de mi class ActiveRecord, no manejo subconsultas (ni siquiera me molesto):

 public function Having($data, $operator = 'LIKE', $merge = 'AND') { if (array_key_exists('query', $this->sql) === true) { foreach ($data as $key => $value) { $this->sql['having'][] = ((empty($this->sql['having']) === true) ? 'HAVING' : $merge) . ' ' . $this->Tick($key) . ' ' . $operator . ' ' . $this->Quote($value); } } return $this; } public function Where($data, $operator = 'LIKE', $merge = 'AND') { if (array_key_exists('query', $this->sql) === true) { foreach ($data as $key => $value) { $this->sql['where'][] = ((empty($this->sql['where']) === true) ? 'WHERE' : $merge) . ' ' . $this->Tick($key) . ' ' . $operator . ' ' . $this->Quote($value); } } return $this; } 

Otra cosa que puede considerar es tener methods customHaving () y customWhere ().

Sé que esta es una publicación extremadamente antigua, pero voy a responder de todos modos, porque estoy en el process de desarrollar mis propias classs para satisfacer las necesidades similares a las que plantea la pregunta.

Después de analizarlo, descubrí que el problema con Zend-Db y otros motores similares es que intentan ser todo para todas las personas. Para atraer a la audiencia más grande, necesitan ofrecer la funcionalidad más general, que se convierte en su propia ruina en la medida en que puedo ver (y como explicó expertamente Bill Karwin).

Una de las complicaciones más obvias que hacen muchos motores es confundir la generación de código SQL con su ejecución (lo que facilita la escritura de SQL sucio). En muchas aplicaciones, es una buena idea separar ambas de manera bastante explícita, animando al desarrollador a pensar en ataques de inyección, etc.

Al crear un motor SQL, lo primero que debe hacer es limitar el scope del SQL que su motor puede generar. No debe permitir que produzca un select * from table por ejemplo; el motor debe requerir que el desarrollador defina cada columna select , where y having explícitamente. Como otro ejemplo, a menudo es útil exigir que cada columna tenga un alias (normalmente no requerido por la database).

Tenga en count que limitar el SQL de esta forma no limita lo que realmente puede get de la database. Sí, hace que la encoding inicial sea más prolija de vez en cuando, pero también la hace más estructurada, y te permite volcar cientos de líneas de código de biblioteca que solo existieron en primer lugar para tratar con excepciones complicadas y proporcionar ( ejem) "flexibilidad".

Las bibliotecas que he escrito hasta ahora son alnetworkingedor de 600 líneas de código (~ 170 líneas de las cuales es event handling errores). Se trata de uniones ISO, subenunciados (en las cláusulas SELECT , FROM y WHERE ), cualquier cláusula de comparación de dos lados, IN , EXISTS y BETWEEN (con subenunciados en la cláusula WHERE). También crea enlaces de forma implícita, en lugar de inyectar directamente valores en el SQL.

Limitaciones (distintas a las ya mencionadas): el SQL está escrito expresamente para Oracle. No probado en ninguna otra plataforma de database.

Estoy dispuesto a compartir el código, suponiendo que se envíen mejoras.

Como ejemplo de lo que las bibliotecas me permiten producir, espero que lo siguiente sea lo suficientemente simple como para ser intuitivo, a la vez que sea lo suficientemente complejo como para mostrar el potencial de capacidad de expansión:

 <?php $substmt = new OraSqlStatement; $substmt->AddVarcharCol ('value','VALUE') ->AddVarcharCol ('identity','UID',false) ->AddVarcharCol ('type','info_type',false) ->AddFrom ('schemaa.user_propertues','up') ->AddWhere ('AND') ->AddComparison ('UID', '=', 'e.identity', 'column') ->AddComparison ('info_type', '=', 'MAIL_ADDRESS'); $stmt = new OraSqlStatement; $stmt->AddVarcharCol ('company_id', 'Company') ->AddVarcharCol ('emp_no', 'Emp Id') ->AddVarcharCol ('person_id', 'Pers Id') ->AddVarcharCol ('name', 'Pers Name') ->AddDateCol ('employed_date', 'Entry Date') ->AddDateCol ('leave_date', 'Leave Date') ->AddVarcharCol ('identity', 'User Id') ->AddVarcharCol ('active', 'Active') ->AddVarcharCol ($substmt, 'mail_addy') ->AddFrom ('schemab.employee_tab', 'e') ->AddFrom ('schemaa.users_vw','u','INNER JOIN','u.emp_no=e.emp_number') ->AddWhere ('AND') ->AddComparison ('User Id', '=', 'my_user_id') ->AddSubCondition ('OR') ->AddComparisonNull ('Leave Date', false) ->AddComparisonBetween ('Entry Date', '2011/01/01', '2011/01/31'); echo $stmt->WriteSql(); var_dump($stmt->GetBindArray()); ?> 

Que produce:

 SELECT company_id "Company", emp_no "Emp Id", person_id "Pers Id", name "Pers Name", employed_date "Entry Date", leave_date "Leave Date", identity "User Id", active "Active", ( SELECT value "VALUE" FROM schemaa.user_propertues up WHERE upper(identity) = upper(e.identity) AND upper(TYPE) = upper (:var0) ) "mail_addy" FROM schemab.employee_tab e INNER JOIN schemaa.users_vw u ON u.emp_no = e.emp_number WHERE upper (identity) = upper (:var1) AND ( leave_date IS NOT NULL OR employed_date BETWEEN to_date (:var2,'YYYY/MM/DD') AND to_date (:var3,'YYYY/MM/DD') ) 

Junto con la matriz de vinculación:

 array 0 => string 'MAIL_ADDRESS' (length=12) 1 => string 'my_user_id' (length=10) 2 => string '2011/01/01' (length=10) 3 => string '2011/01/31' (length=10) 

La API de SQLAlchemy es la mejor con la que he trabajado hasta ahora. Es una biblioteca de Python, pero aún así puedes inspirarte. No es solo para las cláusulas WHERE: toda la consulta SQL (ya sea una selección o DML) se expresa con una estructura de datos fácilmente modificable.

(Me refiero a su kit de herramientas SQL, no a las partes ORM. 🙂

Puede considerar SQLBuilder, escrito en PHP y puede generar SQL de plataforma cruzada para MySQL y PostgreSQL configurando un controller de consulta diferente.

El caso de uso está aquí: https://github.com/c9s/SQLBuilder/blob/2.0/tests/SQLBuilder/Query/SelectQueryTest.php