How to order result "sum"

I have a connection built with CCv5, based on a mysql view.
with a CFv5 form I make a selection of the records in the view.

I want to sum ​​the values ​​of "Totaal" and then I want to order the results of the sum desc.

here 's what I've built so far.
everything is working but the "order" totaal desc
It looks like ordering is based on the first record of the selection before sum.

[attachment=0]model.png[/attachment]

[attachment=1]frontlist.png[/attachment]

[attachment=2]listing.png[/attachment]

debug
Array
(
[0] => SELECT `Extension`.`id` AS `Extension.id`, `Extension`.`name` AS `Extension.name` FROM `jos_chronoengine_extensions` AS `Extension` WHERE `Extension`.`enabled` = '1' ORDER BY `Extension`.`ordering` ASC
[1] => SELECT `Form`.`id` AS `Form.id`, `Form`.`title` AS `Form.title`, `Form`.`params` AS `Form.params`, `Form`.`extras` AS `Form.extras`, `Form`.`published` AS `Form.published`, `Form`.`app` AS `Form.app`, `Form`.`form_type` AS `Form.form_type`, `Form`.`content` AS `Form.content` FROM `jos_chronoengine_chronoforms` AS `Form` WHERE `Form`.`title` = 'pre_inklasalg' AND `Form`.`published` = '1'
[2] => SELECT `Connection`.`id` AS `Connection.id`, `Connection`.`title` AS `Connection.title`, `Connection`.`params` AS `Connection.params`, `Connection`.`extras` AS `Connection.extras`, `Connection`.`published` AS `Connection.published` FROM `jos_chronoengine_connections` AS `Connection` WHERE `Connection`.`title` = 'InklasAlg' AND `Connection`.`published` = '1'
[3] => DESCRIBE `itel2`
[4] => SELECT COUNT(*) AS `inklasa.count` FROM `itel2` AS `inklasa` WHERE `inklasa`.`etappe` <= '2015-07-10 10:30:00' GROUP BY `inklasa`.`naam`
[5] => SELECT `inklasa`.`naam` AS `inklasa.naam`, sum(`inklasa`.`totaal`) AS `inklasa.totaal` FROM `itel2` AS `inklasa` WHERE `inklasa`.`etappe` <= '2015-07-10 10:30:00' GROUP BY `inklasa`.`naam` ORDER BY `inklasa`.`totaal` desc LIMIT 30
)

Wim

Hi Wim,

The MySQL query at the end shows that the SUM() is being loaded as `inklasa.totaal`

SELECT `inklasa`.`naam` AS `inklasa.naam`, sum(`inklasa`.`totaal`) AS `inklasa.totaal` 

So you should be able to use inklasa.totaal in the ORDER box.

Bob

Hello Bob

Unfortunately, that does not work neither. It gives the same result as totaal.
I also tried sum(totaal) in the ORDER box, but that gives a "ERROR: 1630".

Wim

Bob means that you can use this in the order box:

inklasa.totaal DESC

Regards,
Max

Hello Max, Bob,

That is exactly what i did, but result is the same as just totaal DESC in de order box

[attachment=0]aki.png[/attachment]

The order is still seems on base of the first 4 records off the view itel2
(2015-07-08 10:30:00).
It should be on the the sum off 'totaal' of all records from > till date.

[attachment=1]itel2.png[/attachment]

Wim

Ok, please try this in the "fields" box:

<?php
return array("naam", "sum(totaal)" => "tot");

Now in the "order" box:

tot DESC

I think this may work correctly!

Regards,
Max

Hello Max,

that's what i was looking for, how to rename the column sum(totaal) to another name then totaal.
but your code is not working, alas.

Now I get:
ERROR: 1054 - UNKNOWN COLUMN 'INKLASA.TOT' IN 'ORDER CLAUSE' SQL=SELECT `INKLASA`.`NAAM` AS `INKLASA.NAAM`, SUM(`INKLASA`.`TOTAAL`) AS `INKLASA.TOT` FROM `ITEL2` AS `INKLASA` WHERE `INKLASA`.`ETAPPE` <= '2015-07-10 10:30:00' GROUP BY `INKLASA`.`NAAM` ORDER BY `INKLASA`.`TOT` DESC LIMIT 30

Please try this:

<?php
return array("naam", "sum(totaal)" => ":tot");

Regards,
Max

Hello Max,

This gives te same error

Regards,
Wim

Are you sure ? the "tot" should not be automatically aliased with my code above, did you add the ":" ?

Yes i am sure.
I added the ":" and second I did copy/paste the whole code.
Wim

Hello,

I'm still looking for a solution.
I tried all kinds of things for days but, nothing works in the right way.
I'm walking in circles.

<?php
return array("naam", "sum(totaal)" => ":tot");

this code in the "field" box does rename the colomn but it seems that it canot be (re-)used in the connection.

Please show me in the right direction


Thank you for chronoforms and cronocennectivity they are are wonderful components it gives me great pleasure working with them.

Hello Max, Bob,

Is there a way to re-order the data in the "Settings>Pre display processing" box with PHP "rsort" of "usort"?
I tried this code but it not working.

<?php
$var = array(return array("naam","tot"));
rsort($var);
foreach ($var as $key => $val) {
    echo "$key = $val\n";
}
?>

How do i get the array data?
Or is this way not possible?

Wim

this code in the "field" box does rename the colomn but it seems that it canot be (re-)used in the connection.

Then just use it for sorting, so in the fields box:

<?php
return array("naam", "sum(totaal)", "sum(totaal)" => ":tot");

order box:

return array(":tot");

Does that work ?

Hallo Max,
no it's not working
this is the error message

ERROR: 1064 - YOU HAVE AN ERROR IN YOUR SQL SYNTAX; CHECK THE MANUAL THAT CORRESPONDS TO YOUR MYSQL SERVER VERSION FOR THE RIGHT SYNTAX TO USE NEAR '`INKLASA`.`ARRAY`(":`INKLASA`.`TOT`"); LIMIT 30' AT LINE 1 SQL=SELECT `INKLASA`.`NAAM` AS `INKLASA.NAAM`, SUM(`INKLASA`.`TOTAAL`) AS `INKLASA.TOTAAL`, SUM(`INKLASA`.`TOTAAL`) AS TOT FROM `ITEL2` AS `INKLASA` WHERE ETAPPE <= '2015-07-12 10:30:00' GROUP BY `INKLASA`.`NAAM` ORDER BY `INKLASA`.`RETURN` `INKLASA`.`ARRAY`(":`INKLASA`.`TOT`"); LIMIT 30

Wim

Hello Max,

Thank You MAX

I'm almost there.
I altered the view and used "`" instead of """

Field box

<?php
return array('naam', 'sum(totaal)', 'sum(totaal)' => ':tot');

and in the Order box

<?php
return (array(':tot'));

Now the list is ordered ASC.
I want to order DESC. DESC after the code in the order box is not working

How can i do that right?

Best Regards
Wim

Thanks for sharing the solution!🙂

Regards,
Max

hi,

in CC5

I try all solutions but not, I have only empty result in the column...
My goal is to show max results.score for each enrollments_id

in the model "results"
Fields :
<?php return array('enrollments_id', 'MAX(score)' => ':maxscore');
(I tried several combinations)

Group :
enrollments_id

in the front list, columns list
results.maxscore:Max

the debug shows

[4] => SELECT `results`.`enrollments_id` AS `results.enrollments_id`, MAX(`results`.`score`) AS maxscore FROM `skf0d_admincrm_results` AS `results` WHERE `results`.`enrollments_id` IN ('342', '341', '340', '339', '338', '337', '336', '335', '334', '333', '332', '331') GROUP BY `results`.`enrollments_id`
)

If you have any idea...?
thanks

Hi Chris,

The field is selected as "maxscore" and so you should use "maxscore" in the columns list, I think that should work.

Best regards,
Max

Hello Max,

I tried...

enrollments.maxscore:Max
results.maxscore:Max
maxscore:Max

Nothing, the column stay desperatly empty

thanks Max

Hi Chris,

Ok, please try to change your code to:
<?php return array('enrollments_id', 'MAX(score)' => 'results.maxscore');

Best regards,
Max

Hello Max,

It's better, the column displays the max score but only for the first line...

Regards
Chris

Hi Chris,

I think this is normal, the MAX function returns only the max value of that field, you will need a join or grouping to get multiple max values.

Best regards,
Max

Hi Max,

The fields is grouped by enrollments_id. For one enrollment you have several results, I want the max.
I thinck it's good

Regards

Hi Chris,

I think that it should work, please try the debug SQL query in phpmyadmin, does it return the correct max values for all results ?

Best regards,
Max

Yes

SELECT `results`.`enrollments_id` AS `results.enrollments_id`, MAX(`results`.`score`) AS `results.maxscore` FROM `xxx_admincrm_results` AS `results` WHERE `results`.`enrollments_id` IN ('160', '159', '158', '157', '156', '155', '154', '153', '152', '151', '150', '149') GROUP BY `results`.`enrollments_id`

Hi Chris,

Apologizes for the late reply here!

The last query should be working ok in connectivity if this is how its displayed in the debug section.

If it does not then it may be a bug in v5 and I would recommend using v6 for this connection.

Best regards,
Max

Hello Max,

No problem..

The query works well in phpmyadmin and in CC5 the debug is

[9] => SELECT `results`.`enrollments_id` AS `results.enrollments_id`, MAX(`results`.`score`) AS `results.maxscore` FROM `xxx_admincrm_results` AS `results` WHERE `results`.`enrollments_id` IN ('2645', '2644', '2643', '2642', '2641', '2640', '2639', '2638', '2637', '2636', '2635', '2634', '2633', '2632', '2631', '2630', '2629', '2628', '2627', '2626', '2625', '2624', '2623', '2622', '2621', '2620', '2619', '2618', '2617', '2616', '2615', '2614', '2613', '2612', '2611', '2610', '2609', '2608', '2607', '2606', '2605', '2604', '2603', '2602', '2601', '2600', '2599', '2598', '2597', '2596') GROUP BY `results`.`enrollments_id`
)

Only the first line is correctly calculated, all the next line are empty

Ok I'll try in cc6

regards
chris

Hi Chris,

I guess it may be a problem in the way v5 extracts the data so I suggest using v6 instead, at least for this connection.

Best regards,
Max