To handle a complex query with 'GROUP BY' part or sub-selectes - wrap it into higher level SELECT.
Database query optimizers easily recognize that trick and produce fine execution plans.

You can filter and sort by result of aggregate function or sub-query like common values.
It just works. No need for special handling at all.
Если необходимо использовать сложный SQL-запрос, включающий в себя GROUP BY или под-запросы - оберните его в дополнительный SELECT.

Поиск и сортировка по всем полям будут работать как обычно.
higrid.net嗨网提供电子表格、在线图形等互联网开发及运营技术，提供相关资料及软件下载，分享奇趣网络时事评论！欢迎访问。


部分内容为俄文，higrid计划翻译一下。敬请期待！

php代码和js代码请点击tab查看。 
<?php

class jqOutComplex extends jqGrid
{
    protected function init()
    {
        #The complex query
        $this->query = "
			SELECT {fields}
			FROM (
				SELECT
					 c.id
					,c.first_name
					,c.last_name

					,count(o.id) AS order_cnt
					,sum(i.price * i.quantity) AS items_sum

					,(SELECT id
					  FROM tbl_order
					  WHERE customer_id=c.id
					  ORDER BY delivery_cost DESC
					  LIMIT 1
					) AS order_max_delivery
					
				FROM tbl_customer c
					JOIN tbl_order o ON (c.id=o.customer_id)
					JOIN tbl_order_item i ON (o.id=i.order_id)
				GROUP BY c.id, c.first_name, c.last_name
			) AS a
			WHERE {where}
		";

        #Set columns
        $this->cols = array(

            'id' => array('label' => 'ID',
                'width' => 10,
                'align' => 'center',
                'formatter' => 'integer',
            ),

            'c_name' => array('label' => 'Name',
                'db' => "CONCAT(first_name, ' ', SUBSTRING(last_name FROM 1 FOR 1))",
                'width' => 35,
            ),

            'order_cnt' => array('label' => 'Orders',
                'width' => 10,
                'formatter' => 'integer',
            ),

            'items_sum' => array('label' => "Order sum",
                'width' => 15,
                'formatter' => 'integer',
            ),

            'order_max_delivery'
            => array('label' => 'Highest delivery',
                'width' => 15,
                'formatter' => 'integer',
            ),
        );
    }
}
<script>
    <?= $rendered_grid ?>
    $grid.filterToolbar();
</script>