GROUP_CONCAT in filter error

[kingjia90]

hi,

here is the fix i have add to make group concat working:

public function select($columns, $backtick_protect = TRUE)
{
  foreach($this->explode(',', $columns) as $val)
  {

    /*GROUP CONCAT FIX JJ*/
    $finder = strpos($val,"GROUP_CONCAT");

    $column = trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$2', $val));
    $this->columns[] =  $column;
    if ($finder!==false){
      $this->select[$column] =  trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$2', $val));
    }else{
      $this->select[$column] =  trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$1', $val));
    }
  }

mysql ...GROUP_CONCAT(codice,"") as center...

Before this fix, the filter where using the search on the function istead of the "as" value, pratically were: group_concat(codice,"") like %finding% while i needed center like %finding%

Hope this will help somebody, maybe you can reintegrate the source with a better coding

J

[marcelod]

@kingjia90 your solution did not work here.here not work, only with GROUP_CONCAT(codice) as "" and the filter does not search the value in condice. Do you could a search of concatenated values​​? tks

[marcelod]

if you use group_concat to work need search this field whit having, but as fazer this. If you use the GROUP_CONCAT function to search this field needs to be with HAVING and WHERE not, but how?

see http://stackoverflow.com/questions/3806830/search-group-concat-using-like

[xtreme8888]

Your approach will not work. Just in case someone else get into the same situation. You should make changes to the Datatables library for function get_filtering(), between A and B add below should resolved the group_concat filtering issue.

It basically using Having instead of where when found items in group_concat.

A. $sWhere = ''; B. if($sWhere != '') $this->ci->db->where('(' . $sWhere . ')');

---

  $qHaving = '';
  $sSearch = $this->ci->db->escape_like_str($this->ci->input->post('sSearch'));//$this->ci->input->post('sSearch');
  //$sSearch = mysql_real_escape_string($this->ci->input->post('sSearch'));

  $mColArray = array_values(array_diff($mColArray, $this->unset_columns));
  $columns = array_values(array_diff($this->columns, $this->unset_columns));

  if($sSearch != '')
    for($i = 0; $i < count($mColArray); $i++){
      if($this->ci->input->post('bSearchable_' . $i) == 'true' && in_array($mColArray[$i], $columns))
        if (strpos($this->select[$mColArray[$i]],"GROUP_CONCAT")===false) {
          $sWhere .= $this->select[$mColArray[$i]] . " LIKE '%" . $sSearch . "%' OR ";
        } else {
          $qHaving .= $this->select[$mColArray[$i]] . " LIKE '%" . $sSearch . "%' OR ";
        }
    }

  $sWhere = substr_replace($sWhere, '', -3);
  $qHaving = substr_replace($qHaving, '', -3);

  if($qHaving != '') {
    $this->ci->db->having('(' . $qHaving . ')');
    $sWhere = '';
  }

[haider-pw]

Is there any other better option. Cuz when used group_concat, then search is only done in concanated string, not in other columns :(

[n1crack]

try subqueries when using group functions.