Body: <p>Based on <a href="https://civicrm.stackexchange.com/questions/20341/extending-civicrm-advanced-search-queryobjects-hook">this solution</a>, I'm trying to add a custom pan in advanced search in order to filter by Line Item Total Sum.</p>
  <h2>The context</h2>
  <p>I successfully manage to filter by Line Item Total with these fields:</p>
  <pre><code>public static $lineitem_fields = [
          'line_total_min' =&gt; [
              'name'  =&gt; 'line_total_min',
              'description'  =&gt; 'Total of a Line Item greater than',
              'title' =&gt; 'Line Total Min',
              'entity' =&gt; 'LineItem',
              'table_name' =&gt; 'civicrm_line_item',
              'where' =&gt; 'civicrm_line_item.line_total',
              'type'  =&gt; CRM_Utils_Type::T_MONEY,
          ],
          'line_total_max' =&gt; [
              'name'  =&gt; 'line_total_max',
              'description'  =&gt; 'Total of a Line Item lower than',
              'title' =&gt; 'Line Total Max',
              'entity' =&gt; 'LineItem',
              'table_name' =&gt; 'civicrm_line_item',
              'where' =&gt; 'civicrm_line_item.line_total',
              'type'  =&gt; CRM_Utils_Type::T_MONEY,
          ],
  ];
  </code></pre>
  <p>And these actions:</p>
  <pre><code>   public function from($name, $mode, $side) {
        if ($name != 'civicrm_line_item') return '';
  
        return &quot;
          $side JOIN civicrm_line_item ON civicrm_line_item.contribution_id = civicrm_contribution.id
        &quot;;
      }
      public function where(&amp;$query) {
  
          if (empty($query-&gt;_params)) return;
  
          $is_queried = 0;
          foreach ($query-&gt;_params as $id =&gt; $param) {
              if (empty($param[0])) {
                  continue;
              }
  
              $is_queried += $this-&gt;whereClauseSingle($param, $query);
          }
          if($is_queried){
            $query-&gt;_whereTables['civicrm_line_item'] = 1;
          }
      }
      public function whereClauseSingle(&amp;$values, &amp;$query) {
          list($name, $op, $value, $grouping, $wildcard) = $values;
          $fields = $this-&gt;getFields();
          if (!array_key_exists($name, $fields)) return 0;
  
          if($name == 'line_total_min'){
            $op = '&gt;=';
            $query-&gt;_where[$grouping][] = 'civicrm_line_item.line_total '.$op.' '.$value;
            return 1;
          }
          if($name == 'line_total_max'){
            $op = '&lt;=';
            $query-&gt;_where[$grouping][] = 'civicrm_line_item.line_total '.$op.' '.$value;
            return 1;
          }
      }
  </code></pre>
  <h2>Now, the problem</h2>
  <p>It becomes tricky when I want to add <strong>SUM</strong> filters</p>
  <p>I try it by adding a search field like this:</p>
  <pre><code>   'line_total_sum_min' =&gt; [
              'name'  =&gt; 'line_total_sum_min',
              'description'  =&gt; 'Sum of a Line Item Totals greater than',
              'title' =&gt; 'Line total sum Min',
              'entity' =&gt; 'LineItem',
              'table_name' =&gt; 'civicrm_line_item',
              'where' =&gt; 'civicrm_line_item.line_total',
              'select' =&gt; 'SUM(civicrm_line_item.line_total) as line_total_sum_min',
              'type'  =&gt; CRM_Utils_Type::T_MONEY,
     ],
  </code></pre>
  <p>and, in <code>whereClauseSingle</code>, the following code:</p>
  <pre><code>      if($name == 'line_total_sum_min'){
            $op = '&gt;=';
            $query-&gt;_where[$grouping]['line_total_sum_min'] = 'civicrm_line_item.line_total IS NOT NULL';
            $query-&gt;_having[$grouping][] = 'line_total_sum_min '.$op.' '.$value;
            return 1;
          }
  </code></pre>
  <p>Actually, it could work, by the alphabetical summary in <a href="https://github.com/civicrm/civicrm-core/blob/master/CRM/Contact/BAO/Query.php#L4968" rel="nofollow noreferrer">Contact BAO</a> builds a query with the <code>HAVING</code> <strong>before</strong> the <code>GROUP BY</code> clause, which causes an exception. (In fact, I wonder how it can work, as SQL takes the <code>HAVING</code> after <code>GROUP BY</code>)</p>
  <p>Here is the tpl content:</p>
  <pre><code>&lt;table class=&quot;form-layout&quot;&gt;
    &lt;tr&gt;
      &lt;td&gt;
        Total
      &lt;/td&gt;
      &lt;td&gt;
        &lt;label&gt;{ts}{$form.line_total_min.label}{/ts}&lt;/label&gt;&lt;br /&gt;
        {$form.line_total_min.html}
      &lt;/td&gt;
      &lt;td&gt;
        &lt;label&gt;{ts}{$form.line_total_max.label}{/ts}&lt;/label&gt;&lt;br /&gt;
        {$form.line_total_max.html}
      &lt;/td&gt;
    &lt;/tr&gt;
    &lt;tr&gt;
      &lt;td&gt;
        Aggregated Sum
      &lt;/td&gt;
      &lt;td&gt;
        &lt;label&gt;{ts}{$form.line_total_sum_min.label}{/ts}&lt;/label&gt;&lt;br /&gt;
        {$form.line_total_sum_min.html}
      &lt;/td&gt;
    &lt;/tr&gt;
  &lt;/table&gt;
  </code></pre>
  <h2>The question(s)</h2>
  <p>Is there a way to use clauses only in the main search query?</p>
  <p>In other way: is there a way to exclude clauses from <code>alphabetQuery()</code>?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-06-29T15:40:02.090Z
Comments.Text: I don't have a direct answer but regarding the `having` before `group`, there's a pending code change that has sort of stalled that might address that. See https://github.com/civicrm/civicrm-core/pull/16877#discussion_r396344872
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-06-30T08:12:58.410Z
Comments.Text: Thank you @Demerit, I've added a comment on the issue.
Comments.UserDisplayName: Bastien Ho
Tags: <search><forms><hooks><advanced-search>
Title: How to use a GROUP_BY / HAVING in extended Advanced Contact Search query?
ThreadText: # How to use a GROUP_BY / HAVING in extended Advanced Contact Search query?
  
  (Asked by: username_0 on 2020-06-29)
  
  Based on [this solution](https://civicrm.stackexchange.com/questions/20341/extending-civicrm-advanced-search-queryobjects-hook), I'm trying to add a custom pan in advanced search in order to filter by Line Item Total Sum.
  
  ## The context
  
  I successfully manage to filter by Line Item Total with these fields:
  
  ```
  public static $lineitem_fields = [
          'line_total_min' => [
              'name'  => 'line_total_min',
              'description'  => 'Total of a Line Item greater than',
              'title' => 'Line Total Min',
              'entity' => 'LineItem',
              'table_name' => 'civicrm_line_item',
              'where' => 'civicrm_line_item.line_total',
              'type'  => CRM_Utils_Type::T_MONEY,
          ],
          'line_total_max' => [
              'name'  => 'line_total_max',
              'description'  => 'Total of a Line Item lower than',
              'title' => 'Line Total Max',
              'entity' => 'LineItem',
              'table_name' => 'civicrm_line_item',
              'where' => 'civicrm_line_item.line_total',
              'type'  => CRM_Utils_Type::T_MONEY,
          ],
  ];
  
  ```
  
  And these actions:
  
  ```
     public function from($name, $mode, $side) {
        if ($name != 'civicrm_line_item') return '';
  
        return "
          $side JOIN civicrm_line_item ON civicrm_line_item.contribution_id = civicrm_contribution.id
        ";
      }
      public function where(&$query) {
  
          if (empty($query->_params)) return;
  
          $is_queried = 0;
          foreach ($query->_params as $id => $param) {
              if (empty($param[0])) {
                  continue;
              }
  
              $is_queried += $this->whereClauseSingle($param, $query);
          }
          if($is_queried){
            $query->_whereTables['civicrm_line_item'] = 1;
          }
      }
      public function whereClauseSingle(&$values, &$query) {
          list($name, $op, $value, $grouping, $wildcard) = $values;
          $fields = $this->getFields();
          if (!array_key_exists($name, $fields)) return 0;
  
          if($name == 'line_total_min'){
            $op = '>=';
            $query->_where[$grouping][] = 'civicrm_line_item.line_total '.$op.' '.$value;
            return 1;
          }
          if($name == 'line_total_max'){
            $op = '<=';
            $query->_where[$grouping][] = 'civicrm_line_item.line_total '.$op.' '.$value;
            return 1;
          }
      }
  
  ```
  
  ## Now, the problem
  
  It becomes tricky when I want to add **SUM** filters
  
  I try it by adding a search field like this:
  
  ```
     'line_total_sum_min' => [
              'name'  => 'line_total_sum_min',
              'description'  => 'Sum of a Line Item Totals greater than',
              'title' => 'Line total sum Min',
              'entity' => 'LineItem',
              'table_name' => 'civicrm_line_item',
              'where' => 'civicrm_line_item.line_total',
              'select' => 'SUM(civicrm_line_item.line_total) as line_total_sum_min',
              'type'  => CRM_Utils_Type::T_MONEY,
     ],
  
  ```
  
  and, in `whereClauseSingle`, the following code:
  
  ```
        if($name == 'line_total_sum_min'){
            $op = '>=';
            $query->_where[$grouping]['line_total_sum_min'] = 'civicrm_line_item.line_total IS NOT NULL';
            $query->_having[$grouping][] = 'line_total_sum_min '.$op.' '.$value;
            return 1;
          }
  
  ```
  
  Actually, it could work, by the alphabetical summary in [Contact BAO](https://github.com/civicrm/civicrm-core/blob/master/CRM/Contact/BAO/Query.php#L4968) builds a query with the `HAVING` **before** the `GROUP BY` clause, which causes an exception. (In fact, I wonder how it can work, as SQL takes the `HAVING` after `GROUP BY`)
  
  Here is the tpl content:
  
  ```
  <table class="form-layout">
    <tr>
      <td>
        Total
      </td>
      <td>
        <label>{ts}{$form.line_total_min.label}{/ts}</label><br />
        {$form.line_total_min.html}
      </td>
      <td>
        <label>{ts}{$form.line_total_max.label}{/ts}</label><br />
        {$form.line_total_max.html}
      </td>
    </tr>
    <tr>
      <td>
        Aggregated Sum
      </td>
      <td>
        <label>{ts}{$form.line_total_sum_min.label}{/ts}</label><br />
        {$form.line_total_sum_min.html}
      </td>
    </tr>
  </table>
  
  ```
  
  ## The question(s)
  
  Is there a way to use clauses only in the main search query?
  
  In other way: is there a way to exclude clauses from `alphabetQuery()`?
  
  **Tags:** search, forms, hooks, advanced-search
  
  > **Comments:**
  > **username_1**: I don't have a direct answer but regarding the `having` before `group`, there's a pending code change that has sort of stalled that might address that. See https://github.com/civicrm/civicrm-core/pull/16877#discussion_r396344872  
  > **username_0**: Thank you @username_1, I've added a comment on the issue.  
  
  ---
  
  ## 0 answers
  
  