CFV8 Read Database Query Fields Add Query fields and Functions

Hi Max,

First of all thanks for the excellent job you are doing! 

In CF V7 there was a functionnality in read database action :  "Query Fields Add Query fields and Functions" which was quite practical.

Currently in CF8 Read Database we have the "SQL code" allowing to perform any query. However for relative simple queries, the "Query Fields Add Query fields and Functions" as in CF7 would be nice and very convenient speeding up the creation of queries.

Can you consider to include it ?

Thanks

s.

Hi Max,

Thanks for the feed back. The "issue" is that it does not accept or recognise   SUM(field_name)=custom_field_name, throwing and error.

In CF V7  the " Query FieldsAdd Query fields and Functions"  was accepting that when putting in the available inputs

New field                                 Field Alias

SUM(field_name)                     custom_field_name

See image attached.

in order for the SUM function to work you must have a Group By, this is how I have it working:

Excellent,  Thank you !👍

Hi Max,

i tried exactly as you indicated but it still throws an error:

Unknown column 'sum(id)' in 'field list'

Maybe something to do with the way SUM is interpreted ?

s

Maybe it's your mySQL settings, try to use sum('id') ?

Hi Max,

I tried tried, that but unfortunately same result.  =>  Unknown column 'sum(id)' in 'field list'

[read_data36] => Array
        (
            [sql] => SELECT `id` AS 'id', `title` AS 'title', `SUM('id')` AS 'status_sum' FROM `josrj_chronoforms8` GROUP BY published
            [returned] => NULL
        )

    [read_data36] => Array
        (
            [sql] => SELECT `id` AS 'id', `title` AS 'title', `SUM(id)` AS 'status_sum' FROM `josrj_chronoforms8` GROUP BY published
            [returned] => NULL
        )

Possibly the reserved words like SUM are not interpreted correctly ?

Hi simelas

Please post as screenshot of your Read Data settings, and make sure you have the latest v8.0.49 update installed

Hi Max, here we go. updated to v8.0.49

No error anymore. Sounds good!

However it still missinterpret "complex" query such as  SUM(field1*field2)=sumfield1field2 when using joins.

  [read_data36] => Array
        (
            [sql] => SELECT `id` AS 'id', `title` AS 'title', sum(id) AS 'status_sum' FROM `josrj_chronoforms8` GROUP BY published
            [returned] => Array
                (
                    [0] => Array
                        (
                            [id] => 2
                            [title] => Balance
                            [status_sum] => 386
                        )

                    [1] => Array
                        (
                            [id] => 1
                            [title] => Administration
                            [status_sum] => 317
                        )

                )

        )

)

So the SUM query is now working ?

What is not working ? please give me the function you are trying to use

Hi Max,

The SUM now works ok.

The function i wanted to use was multiplication with jointed tables.  SUM(Main.id*Chrono7Join.id)=seven_square_staus_sum

more precisely:

and result seems ok:

 [read_data37] => Array
        (
            [sql] => SELECT `Main`.`id` AS 'Main.id', `Main`.`title` AS 'Main.title', sum(Main.id) AS 'status_sum', sum(Main.id*Main.id), sum(Main.id*Chrono7Join.id) FROM `josrj_chronoforms8` AS `Main` LEFT JOIN josrj_chronog3_forms7 AS `Chrono7Join` ON Main.id=Chrono7Join.id GROUP BY Main.published
            [returned] => Array
                (
                    [Main.id] => 1
                    [Main.title] => Administration
                    [status_sum] => 317
                    [sum(Main.id*Main.id)] => 8823
                    [sum(Main.id*Chrono7Join.id)] => 6158
                )

        )

)

So all in all it seems to work. Well done !

I 'll keep you posted if i find something else.

Thanks again for the great job!

BR;

s

Hi simelas

Great, thanks for the confirmation.

It looks like the field alias is not used for your complex fields, I have fixed this issue for the next update.