Implementing a query that uses IF and SUM in the select statement

[MGA555]

How do you achieve a query that has an IF and a SUM in the select statement. I am quite sure, that cannot be in the TH, where the basic parts of the SELECT are defined.

Is it in the Ajax, specifically the select function?

If only I could get a comprehensive example of using OpenJS Grid, I wouldn't be bothering you.

thanks for all your help

[optikalefx]

I gotchat.

Here is a complete example that I use for a client. This example has multiple joins, a having statement, some CONCATs a timestampdiff, and it even holds off rendering till later so that fields can be decrypted. If you didn't want the whole decryption thing, just get rid of that last param that says FALSE.

require_once("packages/grid.php");
$grid = new Grid("user",array(
    "save"=>true,
    "joins"=>array(
        "LEFT JOIN `order` ON (`order`.user_id = user.id)",
        "LEFT JOIN subscription ON (subscription.order_id = `order`.id)"
    ),
    "fields"=>array(
        "name"=>"CONCAT(first_name,' ',last_name)",
        "first_name"=>"user.first_name",
        "last_name"=>"user.last_name",
        "trialDays"=>"timestampdiff(DAY,NOW(),date_added + INTERVAL 90 DAY)",
        "active"=>"subscription.active",
        "date_added"=>"DATE(date_added)"
    ),
    "having"=>"trialDays > 0  && (subscription.active != 1 || subscription.active IS NULL)"
),FALSE);

// decryption
foreach($grid->data['rows'] as $key=>$row) {
    $grid->data['rows'][$key]['name'] = $this->_decryptData($row['first_name'])." ".$this->_decryptData($row['last_name']);
    $grid->data['rows'][$key]['email'] = $this->_decryptData($row['email']);
    $grid->data['rows'][$key]['phone'] = $this->_decryptData($row['phone']);
}

$grid->render();

[optikalefx]

to further answer your questions, you do SUM and SUM(IF( inside your "fields" array as seen above.

[MGA555]

Unless I am going blind, there is no SUM or Group by in the example above. I thought there was when you first posted it, but I sure don't see it now.

I have tried just about everything I can think of, and just can't get it to work. Help!

[optikalefx]

If you wanted to do a sum you would just do

 "fields"=>array(
        "name"=>"CONCAT(first_name,' ',last_name)",
        "first_name"=>"user.first_name",
        "last_name"=>"user.last_name",
        "trialDays"=>"SUM(trialDays)"
    ),

And if you wanted a group by you would do

$grid = new Grid("user",array(
    "save"=>true,
   "groupBy" => "something"
);

[optikalefx]

The "fields" is basically your SELECT statement. So normally you would do SUM and SUM(IF inside of a select. Well in OpenJSGrid since your "fields" are your SELECTS you do them there. "trialDays"=>"SUM(trialDays)" translates to SELECT SUM(trialDays) as trialDays

If you wanted to do SUM(IF) "trialDays"=>"SUM(IF(trialDays,1,0))" which translates to SELECT SUM(IF(trialDays,1,0)) as trialDays