⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠
Posted 09 December 2015 - 12:46 PM
Hi everybody,
I need to transcript a query that have these conditions :
WHERE start_date <= CURRENT_DATE() AND (end_date IS NULL OR end_date >= CURRENT_DATE())
Can you tell me if I can code it with the basic crud model ? I haven't managed...
Thanks for this wonderfull FW !
Posted 10 December 2015 - 01:15 AM
I've seen there are group_start and group_end functions in DB_query_Builder.php :
// --------------------------------------------------------------------
/**
* Starts a query group, but ORs the group
*
* @return CI_DB_query_builder
*/
public function group_start($not = '', $type = 'AND ')
{
$type = $this->_group_get_type($type);
$this->qb_where_group_started = TRUE;
$prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? '' : $type;
$where = array(
'condition' => $prefix.$not.str_repeat(' ', ++$this->qb_where_group_count).' (',
'escape' => FALSE
);
$this->qb_where[] = $where;
if ($this->qb_caching)
{
$this->qb_cache_where[] = $where;
}
return $this;
}
// --------------------------------------------------------------------
/**
* Starts a query group, but ORs the group
*
* @return CI_DB_query_builder
*/
public function or_group_start()
{
return $this->group_start('', 'OR ');
}
Unfortunately, that doesn't seem to be implemented in Grocery_crud_model.php :(
That would be exactly what I'm looking for.
And by the way, that's also the reason why flexigrid filtering on all fields doesn't work with GC : here's an example of the query generated by the set_ajax_list_queries in this case :
SELECT * FROM `data_individus` LEFT JOIN `param_sites` as `j33291976` ON `j33291976`.`id` = `data_individus`.`id_site` WHERE `entree` <= '2015-12-10' AND `sortie` >= '2015-12-10' OR `data_individus`.`id` LIKE '%my searched text%' ESCAPE '!' OR `data_individus`.`nom` LIKE '%my searched text%' ESCAPE '!' OR `data_individus`.`prenom` LIKE '%my searched text%' ESCAPE '!' OR `j33291976`.`site` LIKE '%my searched text%' ESCAPE '!'
where it should be :
SELECT *
FROM `data_individus`
LEFT JOIN `param_sites` as `j33291976` ON `j33291976`.`id` = `data_individus`.`id_site`
WHERE `entree` <= '2015-12-10'
AND `sortie` >= '2015-12-10'
AND ( // imagine a start_group('', 'AND ') call
`data_individus`.`id` LIKE '%my searched text%' ESCAPE '!'
OR `data_individus`.`nom` LIKE '%my searched text%' ESCAPE '!'
OR `data_individus`.`prenom` LIKE '%my searched text%' ESCAPE '!'
OR `j33291976`.`site` LIKE '%my searched text%' ESCAPE '!'
) // end_group()
If anyone saw what I mean and could code an implementation of these functions in GC ... he would be my hero :D !
Posted 11 December 2015 - 14:12 PM
Hi everybody,
I need to transcript a query that have these conditions :
WHERE start_date <= CURRENT_DATE() AND (end_date IS NULL OR end_date >= CURRENT_DATE())Can you tell me if I can code it with the basic crud model ? I haven't managed...
Thanks for this wonderfull FW !
OK, I answer to myself :
$today=date('Y-m-d');
$crud->where('start_date <= ', $today);
$crud->where('(end_date >= \''.$today.'\' OR end_date IS NULL)');
That works.
Posted 11 December 2015 - 14:17 PM
I've seen there are group_start and group_end functions in DB_query_Builder.php :
// -------------------------------------------------------------------- /** * Starts a query group, but ORs the group * * @return CI_DB_query_builder */ public function group_start($not = '', $type = 'AND ') { $type = $this->_group_get_type($type); $this->qb_where_group_started = TRUE; $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? '' : $type; $where = array( 'condition' => $prefix.$not.str_repeat(' ', ++$this->qb_where_group_count).' (', 'escape' => FALSE ); $this->qb_where[] = $where; if ($this->qb_caching) { $this->qb_cache_where[] = $where; } return $this; } // -------------------------------------------------------------------- /** * Starts a query group, but ORs the group * * @return CI_DB_query_builder */ public function or_group_start() { return $this->group_start('', 'OR '); }Unfortunately, that doesn't seem to be implemented in Grocery_crud_model.php :(
That would be exactly what I'm looking for.
And by the way, that's also the reason why flexigrid filtering on all fields doesn't work with GC : here's an example of the query generated by the set_ajax_list_queries in this case :
SELECT * FROM `data_individus` LEFT JOIN `param_sites` as `j33291976` ON `j33291976`.`id` = `data_individus`.`id_site` WHERE `entree` <= '2015-12-10' AND `sortie` >= '2015-12-10' OR `data_individus`.`id` LIKE '%my searched text%' ESCAPE '!' OR `data_individus`.`nom` LIKE '%my searched text%' ESCAPE '!' OR `data_individus`.`prenom` LIKE '%my searched text%' ESCAPE '!' OR `j33291976`.`site` LIKE '%my searched text%' ESCAPE '!'where it should be :
SELECT * FROM `data_individus` LEFT JOIN `param_sites` as `j33291976` ON `j33291976`.`id` = `data_individus`.`id_site` WHERE `entree` <= '2015-12-10' AND `sortie` >= '2015-12-10' AND ( // imagine a start_group('', 'AND ') call `data_individus`.`id` LIKE '%my searched text%' ESCAPE '!' OR `data_individus`.`nom` LIKE '%my searched text%' ESCAPE '!' OR `data_individus`.`prenom` LIKE '%my searched text%' ESCAPE '!' OR `j33291976`.`site` LIKE '%my searched text%' ESCAPE '!' ) // end_group()If anyone saw what I mean and could code an implementation of these functions in GC ... he would be my hero :D !
Again I answer to mysellf : in order to correct the search in all fields, you have to patch Grocery_CRUD.php like this :
protected function set_ajax_list_queries($state_info = null)
{
if(!empty($state_info->per_page))
{
if(empty($state_info->page) || !is_numeric($state_info->page) )
$this->limit($state_info->per_page);
else
{
$limit_page = ( ($state_info->page-1) * $state_info->per_page );
$this->limit($state_info->per_page, $limit_page);
}
}
if(!empty($state_info->order_by))
{
$this->order_by($state_info->order_by[0],$state_info->order_by[1]);
}
if(!empty($state_info->search))
{
if (!empty($this->relation)) {
foreach ($this->relation as $relation_name => $relation_values) {
$temp_relation[$this->_unique_field_name($relation_name)] = $this->_get_field_names_to_search($relation_values);
}
}
if (is_array($state_info->search)) {
foreach ($state_info->search as $search_field => $search_text) {
if (isset($temp_relation[$search_field])) {
if (is_array($temp_relation[$search_field])) {
foreach ($temp_relation[$search_field] as $relation_field) {
$this->or_like($relation_field , $search_text);
}
} else {
$this->like($temp_relation[$search_field] , $search_text);
}
} elseif(isset($this->relation_n_n[$search_field])) {
$escaped_text = $this->basic_model->escape_str($search_text);
$this->having($search_field." LIKE '%".$escaped_text."%'");
} else {
$this->like($search_field, $search_text);
}
}
} elseif ($state_info->search->field !== null) {
if (isset($temp_relation[$state_info->search->field])) {
if (is_array($temp_relation[$state_info->search->field])) {
foreach ($temp_relation[$state_info->search->field] as $search_field) {
$this->or_like($search_field , $state_info->search->text);
}
} else {
$this->like($temp_relation[$state_info->search->field] , $state_info->search->text);
}
} elseif(isset($this->relation_n_n[$state_info->search->field])) {
$escaped_text = $this->basic_model->escape_str($state_info->search->text);
$this->having($state_info->search->field." LIKE '%".$escaped_text."%'");
} else {
$this->like($state_info->search->field , $state_info->search->text);
}
}
else
{
$columns = $this->get_columns();
$search_text = $state_info->search->text;
if(!empty($this->where)) {
foreach($this->where as $where)
$this->basic_model->having($where[0],$where[1],$where[2]);
}
// patch start
//In order to open a group
$this->basic_model->where('(0 = 1');
// patch end
foreach($columns as $column)
{
if(isset($temp_relation[$column->field_name]))
{
if(is_array($temp_relation[$column->field_name]))
{
foreach($temp_relation[$column->field_name] as $search_field)
{
$this->or_like($search_field, $search_text);
}
}
else
{
$this->or_like($temp_relation[$column->field_name], $search_text);
}
}
elseif(isset($this->relation_n_n[$column->field_name]))
{
//@todo have a where for the relation_n_n statement
}
else
{
$this->or_like($column->field_name, $search_text);
}
}
// patch start
//In order to close the group
$this->basic_model->where('1 = 1)');
// patch end
}
}
}