Group by

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
theyland
Posts: 12
Joined: 2020-11-04 15:43

Group by

Post by theyland » 2022-12-09 15:55

Hello,
I have a DB that contains the names of farmers who receive inputs. I need to know which farmer receives what amount of input according to this query:

SELECT farmer.Name ,Input.input, sum(input.amount) FROM Farmer
LEFT JOIN Input ON Input.GN=Farmer.GN GROUP BY by input.input, farmer.GN

This does not to be possible with a calculated field.

I have circumvented this now by creating a view and then having a table in AppGini with the same fields as the view - which works.
Is there any wat to do this in AppGini directly?
Thanks
Thomas

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Group by

Post by pbottcher » 2022-12-09 21:26

Hi,
which is your calculated field and how is the calulaton done? You can try to reflect that in your query.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

theyland
Posts: 12
Joined: 2020-11-04 15:43

Re: Group by

Post by theyland » 2022-12-10 06:54

This is my query from the calculated field:

Select sum(`Input`.`amount`) from Farmer
LEFT JOIN `Input` ON `Input`.`GN`=`Farmer`.`GN`
WHERE `Farmer`.`GN`= %ID%

It will correctly give me the sum of inputs, but I still have to group by inputs to know the sum of each input.

Greetings
Thomas

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Group by

Post by pbottcher » 2022-12-10 08:55

HI Thomas,

maybe I'm not clear about your requirement. The calculated field needs to return one value. Can you please post the table structure and point to where the aggregated sum per input and farmer shall be stored.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

theyland
Posts: 12
Joined: 2020-11-04 15:43

Re: Group by

Post by theyland » 2022-12-10 12:52

Ok, I guess the basic problem is that AppGini works with tables only, while Mysql will generate a temporary table for a result of a query.

So, I can either work with views in the DB or create new calculated fields in the table for each instance like this:

Select sum(`Input`.`amount`) from Farmer
LEFT JOIN `Input` ON `Input`.`GN`=`Farmer`.`GN`
LEFT JOIN `input_list` ON `Input`.`input`=`input_list`.`id`
WHERE `Farmer`.`GN`= %ID% and `input_list`.`Id` = 1
and then 2,3,4,5 etc.

That does work and actually looks quite nice.
Greetings
Thomas

Post Reply