Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Group_concat filtering causing error SQL #53

Open
xtreme8888 opened this issue May 12, 2014 · 2 comments
Open

Group_concat filtering causing error SQL #53

xtreme8888 opened this issue May 12, 2014 · 2 comments

Comments

@xtreme8888
Copy link

When using Group_CONCAT in the query, filtering will having an issue.

Below is hacking the get_filtering function but works, it will also filtering the fields with group_concat, however, the downside is that you will have to group_concat and distinct every field that you wish to search for.

See if you can find a better approach for group_concat

$sWhere = '';
$qHaving = '';
$sSearch = $this->ci->db->escape_like_str(trim($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))
        $sWhere .= $this->select[$mColArray[$i]] . " LIKE '%" . $sSearch . "%' OR ";*/
  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 = '';
  }    
RodolfoSilva added a commit to RodolfoSilva/Ignited-Datatables that referenced this issue Jul 2, 2014
cryogenix added a commit that referenced this issue Nov 4, 2014
@haider-pw
Copy link

@xtreme8888
Your Solution Works on my table, but problem is if i have column with no Group Concat, it dont get to filter???
How to fix the Issue??

@Allan-Nava
Copy link

Allan-Nava commented Apr 24, 2018

I add COUNT(*) and SUM(a.price) but doesn't work the where clause for dates, how can I fix?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants