left - Zend Framework SQL select query construction(DONDE CLAUSULA)

zend framework sql query

Question

Intento generar una consulta que seleccione todo de una tabla de usuario donde cualquier combinación del apellido del primer nombre coincida con un término de búsqueda particular

$select = $select->where(''last_name LIKE ?'', ''%''.$term.''%'')->orWhere(''first_name LIKE ?'', ''%''.$term.''%'')
->orWhere("CONCAT(first_name,'' '', last_name) LIKE ?", ''%''.$term.''%'')
->orWhere("CONCAT(last_name,'' '', first_name) LIKE ?", ''%''.$term.''%''); 

Hay otra condición que también debe cumplirse y que se especifica en otra cláusula where

$select = $select->where("deleted = 0 AND scholar = 0");

Se genera la siguiente instrucción SQL

SELECT `user`.* FROM `user` WHERE (last_name LIKE ''%frank%'') OR (first_name LIKE ''%frank%'') OR (CONCAT(first_name,'' '', last_name) LIKE ''%frank%'') OR (CONCAT(last_name,'' '', first_name) LIKE ''%frank%'') AND (deleted = 0 AND scholar = 0) ORDER BY `date_created` desc LIMIT 25

Esto no devuelve el resultado deseado ya que obtengo filas donde scholar = 1;

Pensé que la consulta debería ser

SELECT `user`.* FROM `user` WHERE ((last_name LIKE ''%frank%'') OR (first_name LIKE ''%frank%'') OR (CONCAT(first_name,'' '', last_name) LIKE ''%frank%'') OR (CONCAT(last_name,'' '', first_name) LIKE ''%frank%'')) AND (deleted = 0 AND scholar = 0) ORDER BY `date_created` DESC LIMIT 25

Cuál es la sintaxis correcta para lograr esto utilizando el objeto $ select.

Answer 1

Puede usar quoteInto para preparar sus condiciones y luego usarlas así:

 $first_name_cond = $db->quoteInto(''first_name LIKE ?'', ''%''.$term.''%'');
$last_name_cond = $db->quoteInto(''last_name LIKE ?'', ''%''.$term.''%'');
$concat_cond1 = $db->quoteInto("CONCAT(first_name,'' '', last_name) LIKE ?", ''%''.$term.''%'');
$concat_cond2 = $db->quoteInto("CONCAT(last_name,'' '', first_name) LIKE ?", ''%''.$term.''%'');
$select = $select->where($first_name_cond.'' OR ''.$last_name_cond.'' OR ''.
$concat_cond1.'' OR ''.$concat_cond2)->where("deleted = 0 AND scholar = 0");

Answer 2

Supongo que eliminado y erudito son columnas separadas. Entonces, la manera más fácil es simplemente romper:

$select = $select->where("deleted = 0 AND scholar = 0");

en dos declaraciones, como:

$select->where("deleted = ?", 0);
$select->where("scholar = ?", 0);

este cambio debería dar como resultado una cadena sql como:

SELECT `user`.* FROM `user` WHERE (last_name LIKE ''%frank%'')
OR (first_name LIKE ''%frank%'')
OR (CONCAT(first_name,'' '', last_name) LIKE ''%frank%'')
OR (CONCAT(last_name,'' '', first_name) LIKE ''%frank%'')
AND deleted = 0 AND scholar = 0 ORDER BY `date_created` desc LIMIT 25

también elimine el extra $select = . Toda tu selección debería parecerse a algo como:

//first line initializes the select object
//The select object will handle most quoting needs for you
$select = $this->select();
//I like to add expressions this way just to keep things easy to read and easy to edit
//you can string multiple statements together, but I find that harder to edit.
$select->where(''last_name LIKE ?'', ''%''.$term.''%'');
$select->orWhere(''first_name LIKE ?'', ''%''.$term.''%'');
$select->orWhere("CONCAT(first_name,'' '', last_name) LIKE ?", ''%''.$term.''%'');
$select->orWhere("CONCAT(last_name,'' '', first_name) LIKE ?", ''%''.$term.''%'');
$select->where("deleted = ?", 0);
$select->where("scholar = ?", 0);
$select->order(''date_created DESC'');
$select->limit(25);

Answer 3

$user = new Application_Model_DbTable_User();
// User List
$uname=$_POST[''uname''];
echo $query = $user->select()->where(''firstname LIKE ?'', $uname.''%'')->ORwhere(''lastname LIKE ?'', $_POST[''lname''].''%'')->ORwhere(''emailid LIKE ?'', $_POST[''email''].''%'');
$userlist = $user->fetchAll($query);