SqlAggregateFunction QML Type

Executes aggregate functions. More...

• List of all members, including inherited members

Properties

• finalize : var
• initialize : var
• iterate : var

Detailed Description

Aggregate functions return a single result row based on a group of rows, rather than single rows. Typical aggregate functions include MIN(), MAX() and SUM().

When the SQLite driver is used through the driverName property in Sql being set to 'QSQLITE', then you can use SqlAggregateFunction to define custom aggregate functions in JavaScript.

The following code sample defines a new aggregate function called 'average', to provide the average of values within the database. Be aware that, to support the TableView and TableViewColumn components, you will need to import QtQuick.Controls 1.4, rather than the latest version (you can import both).

Item {
        property var averageArea

        ColumnLayout {
                anchors.fill: parent
                anchors.margins: 10 * AppFramework.displayScaleFactor

                spacing: 10 * AppFramework.displayScaleFactor

                TableView {
                        id: tableView

                        Layout.fillWidth: true
                        Layout.fillHeight: true

                        TableViewColumn {
                                role: "area"
                                title: qsTr("Area")
                                width: 200 * AppFramework.displayScaleFactor
                        }
                }

                Text {
                        text: qsTr("Average Area: %1").arg(averageArea)
                }
        }

        SqlDatabase {
                id: db
                databaseName: ":memory:"

                SqlAggregateFunction {
                        name: "average"
                        initialize: average_initialize
                        iterate: average_iterate
                        finalize: average_finalize
                }
        }

        Component.onCompleted: {
                db.open();
                db.query("CREATE TABLE Parcel ( AREA real ); ");
                db.query("INSERT INTO Parcel VALUES (:area) ", { area: 110.0 } );
                db.query("INSERT INTO Parcel VALUES (:area) ", { area: 130.0 } );
                db.query("INSERT INTO Parcel VALUES (:area) ", { area: 170.0 } );
                db.query("INSERT INTO Parcel VALUES (:area) ", { area: 190.0 } );
                tableView.model = db.tableModel("Parcel");
                var query = db.query("SELECT average(AREA) as avg FROM Parcel ");
                var ok = query.first();
                if (ok) {
                        averageArea = query.values.avg; // 150.0
                }
                query.finish();
        }

        function average_initialize() {
                var context = {
                        sum: 0,
                        count: 0
                }
                return context;
        }

        function average_iterate(context, value) {
                context.sum += value;
                context.count++;
        }

        function average_finalize(context) {
                return context.count ? context.sum / context.count : Number.NaN;
        }
}

Property Documentation