[eluser]ReyPM[/eluser]
Hi, I'm trying to build this query:
Code: SELECT id_autor, nombre, apellido
FROM autores
WHERE id_autor IN
(SELECT distinct id_autor FROM filiacion
INNER JOIN autores_filiacion ON (autores_filiacion.id_filiacion = filiacion.id_filiacion)
WHERE id_ciudad = 4 AND id_pais = 212)
using Active Record but get lost in where_in(), can any help me to build the query?
[eluser]CroNiX[/eluser]
You can't, and don't really need to. Just use what you have and use db::query(). AR is just a query builder that does some automatic escaping and isn't designed to do everything (like subqueries). Just manually escape any input using db::escape(field). I wouldn't waste any time trying to "make" something work in AR.
[eluser]ReyPM[/eluser]
Well the main problem here is that the whole function is write using AR so change all to db::query() will be a lot of job to do. See the query here:
Code: $this->db->query("SET NAMES 'utf8'");
$this->db->select('autores.id_autor, autores.nombre, autores.apellido, autores.nacionalid');
$this->db->from('autores');
$this->db->distinct();
if (!empty($data['search_criteria']['nombre']) && $data['search_criteria']['nombre'] != "") {
$this->db->like('autores.nombre', $data['search_criteria']['nombre'], 'both');
}
if (!empty($data['search_criteria']['apellido']) && $data['search_criteria']['apellido'] != "") {
$this->db->like('autores.apellido', $data['search_criteria']['apellido'], 'both');
}
if (!empty($data['search_criteria']['gender']) && $data['search_criteria']['gender'] != "") {
$this->db->or_where('autores.genero', $data['search_criteria']['gender']);
}
if (!empty($data['search_criteria']['nacionalidad']) && $data['search_criteria']['nacionalidad'] != "") {
$this->db->or_where('autores.nacionalid', $data['search_criteria']['nacionalidad']);
}
if (!empty($data['search_criteria']['cedula']) && $data['search_criteria']['cedula'] != "cedula") {
$this->db->or_where('autores.ci', $data['search_criteria']['cedula']);
}
if (!empty($data['search_criteria']['fecha_nacimiento']) && $data['search_criteria']['fecha_nacimiento'] != "fecha_nacimiento") {
$this->db->or_where('autores.fecha_naci', $data['search_criteria']['fecha_nacimiento']);
}
if (!empty($data['search_criteria']['email_default']) && $data['search_criteria']['email_default'] != "") {
$this->db->or_where('autores.email', $data['search_criteria']['email_default']);
}
if (!empty($data['search_criteria']['email_alternativo']) && $data['search_criteria']['email_alternativo'] != "") {
$this->db->or_where('autores.email_1', $data['search_criteria']['email_alternativo']);
}
if (empty($data['search_criteria']['pais']) && empty($data['search_criteria']['ciudad'])) {
$this->db->join($this->tbl_autores_filiacion, 'autores.id_autor = autores_filiacion.id_autor', 'left');
$this->db->join($this->tbl_filiacion, 'autores_filiacion.id_filiacion = filiacion.id_filiacion', 'left');
}
if (!empty($data['search_criteria']['pais']) && $data['search_criteria']['pais'] != "" && !empty($data['search_criteria']['ciudad']) && $data['search_criteria']['ciudad'] != "") {
$in_sql = "SELECT DISTINCT id_autor FROM filiacion INNER JOIN autores_filiacion ON (autores_filiacion.id_filiacion = filiacion.id_filiacion) WHERE id_ciudad = " . (int) $data['search_criteria']['ciudad'] . " AND id_pais = " . (int) $data['search_criteria']['pais'];
$this->db->where_in('autores.id_autor', $in_sql, NULL, FALSE);
}
$this->db->limit($limit, $offset);
$query = $this->db->get();
Also could you please supply a example here?
[eluser]Unknown[/eluser]
Code: $this->db->query("SET NAMES 'utf8'");
$aceptado_bool = FALSE;
if ( ( ! empty($data['search_criteria']['pais']) )
&& ( $data['search_criteria']['pais'] != "" )
&& ( ! empty($data['search_criteria']['ciudad'])
&& ( $data['search_criteria']['ciudad'] != "" ) )
{
$in_sql = "SELECT DISTINCT id_autor ";
$in_sql .= "FROM filiacion INNER JOIN autores_filiacion ";
$in_sql .= "ON (autores_filiacion.id_filiacion = filiacion.id_filiacion) ";
$in_sql .= "WHERE id_ciudad = " . (int) $data['search_criteria']['ciudad'];
$in_sql .= " AND id_pais = " . (int) $data['search_criteria']['pais'];
$query = $this->db->query($in_sql);
$id_autor_aceptado = array();
foreach($query->result_array() AS $id_autor)
{
$id_autor_aceptado[] = $id_autor['id_autor'];
}
$aceptado_bool = TRUE;
}
$this->db->select('autores.id_autor, autores.nombre, autores.apellido, autores.nacionalid');
$this->db->from('autores');
$this->db->distinct();
.....
If you take what is above and add it in to your code. Then change your `if clause` further down like so..
Code: if (empty($data['search_criteria']['pais']) && empty($data['search_criteria']['ciudad'])) {
$this->db->join($this->tbl_autores_filiacion, 'autores.id_autor = autores_filiacion.id_autor', 'left');
$this->db->join($this->tbl_filiacion, 'autores_filiacion.id_filiacion = filiacion.id_filiacion', 'left');
}
if ($aceptado_bool)
{
$this->db->where_in('autores.id_autor', $id_autor_aceptado, NULL, FALSE);
}
$this->db->limit($limit, $offset);
All I did was create your little query as an array of accepted ids. Provided your dataset isn't in the millions of author ids you shouldn't noticeably impact performance. This isn't tested but should work just fine. (I am pretty good at coding without an IDE, lol)
The good new here is that you won't have to rewrite your entire method and you shouldn't impact performance.
The bad news is that you will run this as two separate queries instead of just one. Without knowing more about this method's execution or the size of it's datasets; I cannot accurately tell you how this will impact your application overall.
It is more likely that this will be a good solution to your problem here. If this doesn't perform up to what you need, let me know and I will see if I can help further. But, honestly, I think this is all you will need to do.
[eluser]ReyPM[/eluser]
Thanks for your solution and your help but sadly don't work, why? the results are different see here:
Code: SELECT DISTINCT id_autor
FROM filiacion
INNER JOIN autores_filiacion ON ( autores_filiacion.id_filiacion = filiacion.id_filiacion )
WHERE id_ciudad =4
AND id_pais =212
)
LIMIT 0 , 30
Quote:Mostrando registros 0 - 29 ( 174 total, La consulta tardó 0.3241 seg)
Your solution:
Code: SELECT DISTINCT `autores`.`id_autor`, `autores`.`nombre`, `autores`.`apellido`, `autores`.`nacionalid` FROM (`autores`) WHERE `autores`.`id_autor` IN ('43646', '28070', '30514', '30794', '32255', '34074', '34077', '35615', '35710', '39388', '43608', '29192', '35787', '36427', '39632', '27081', '32311', '36440', '26027', '26078', '30114', '34214', '34274', '34587', '36217', '39470', '41468', '35689', '36075', '43291', '27282', '19830', '39571', '27794', '28633', '29831', '30454', '31151', '31152', '36561', '37644', '37786', '38894', '38979', '41405', '41842', '43837', '25730', '13947', '43121', '25541', '40555', '33373', '36713', '26815', '26826', '25753', '27063', '31887', '34282', '35362', '38363', '39273', '42456', '28726', '29585', '31917', '35243', '35266', '35483', '39603', '41010', '41254', '30181', '33517', '29514', '36819', '39541', '36705', '27276', '31265', '32870', '36408', '36635', '39153', '25631', '26741', '41510', '27573', '28052', '35092', '36330', '39504', '39505', '41743', '27180', '27312', '26495', '26985', '29025', '30210', '30632', '33030', '33693', '35062', '36982', '37214', '37692', '37812', '38349', '38630', '38721', '39152', '39753', '40286', '41398', '41890', '42700', '43020', '43541', '43902', '31866', '39339', '41410', '42377', '32937', '37264', '37320', '44141', '25838', '41047', '41479', '42419', '43523', '43534', '26185', '26725', '27649', '28309', '28453', '29030', '29114', '29791', '30049', '30090', '31487', '32806', '33734', '34768', '35384', '39881', '40137', '42163', '43529', '44062', '27804', '28550', '34138', '34261', '35831', '44148', '10592', '44313', '10593', '818', '832', '44314', '44320', '44326', '10598', '44319', '10605', '44328', '44344') LIMIT 25
Quote:Mostrando registros 25 - 24 ( 25 total, La consulta tardó 0.0011 seg)
[eluser]ReyPM[/eluser]
Thanks for your solution and your help but sadly don't work, why? the results are different see here:
Code: SELECT DISTINCT id_autor
FROM filiacion
INNER JOIN autores_filiacion ON ( autores_filiacion.id_filiacion = filiacion.id_filiacion )
WHERE id_ciudad =4
AND id_pais =212
)
LIMIT 0 , 30
Quote:Mostrando registros 0 - 29 ( 174 total, La consulta tardó 0.3241 seg)
Your solution:
Code: SELECT DISTINCT `autores`.`id_autor`, `autores`.`nombre`, `autores`.`apellido`, `autores`.`nacionalid` FROM (`autores`) WHERE `autores`.`id_autor` IN ('43646', '28070', '30514', '30794', '32255', '34074', '34077', '35615', '35710', '39388', '43608', '29192', '35787', '36427', '39632', '27081', '32311', '36440', '26027', '26078', '30114', '34214', '34274', '34587', '36217', '39470', '41468', '35689', '36075', '43291', '27282', '19830', '39571', '27794', '28633', '29831', '30454', '31151', '31152', '36561', '37644', '37786', '38894', '38979', '41405', '41842', '43837', '25730', '13947', '43121', '25541', '40555', '33373', '36713', '26815', '26826', '25753', '27063', '31887', '34282', '35362', '38363', '39273', '42456', '28726', '29585', '31917', '35243', '35266', '35483', '39603', '41010', '41254', '30181', '33517', '29514', '36819', '39541', '36705', '27276', '31265', '32870', '36408', '36635', '39153', '25631', '26741', '41510', '27573', '28052', '35092', '36330', '39504', '39505', '41743', '27180', '27312', '26495', '26985', '29025', '30210', '30632', '33030', '33693', '35062', '36982', '37214', '37692', '37812', '38349', '38630', '38721', '39152', '39753', '40286', '41398', '41890', '42700', '43020', '43541', '43902', '31866', '39339', '41410', '42377', '32937', '37264', '37320', '44141', '25838', '41047', '41479', '42419', '43523', '43534', '26185', '26725', '27649', '28309', '28453', '29030', '29114', '29791', '30049', '30090', '31487', '32806', '33734', '34768', '35384', '39881', '40137', '42163', '43529', '44062', '27804', '28550', '34138', '34261', '35831', '44148', '10592', '44313', '10593', '818', '832', '44314', '44320', '44326', '10598', '44319', '10605', '44328', '44344') LIMIT 25
Quote:Mostrando registros 25 - 24 ( 25 total, La consulta tardó 0.0011 seg)
|
