sql-functions.md

SQL Functions

中文版请见这里

SQLite has many built-in functions. We usually would use them in two places: after SELECT keyword or in conditions (use for WHERE and HAVING).

Using functions in conditions like this:

fun sample() {
    database {
        PersonTable { table ->
             table SELECT WHERE(abs(age) LTE 5)
             table SELECT GROUP_BY(name) HAVING (count(X) > 2)
        }
    }
}

In Modify Database and Transaction, we have introduced sqllin-processor will help us to generate some ClauseElements to represent column names. SQL functions will receive a ClauseElement as a parameter and return a ClauseElement as the result. The functions supported by SQLlin are as follows:

count, max, min, avg, sum, abs, upper, lower, length

The count function has a different point, it could receive X as parameter be used for representing count(*) in SQL, as shown in the example above.

SQLlin only supports using functions in conditions now. We will consider supporting using functions after the SELECT keyword in future versions. Now, if you have similar demands, you can use Kotlin Collections API to handle query results:

fun sample() {
    lateinit var selectStatement: SelectStatement<Person>
    database {
        PersonTable { table ->
             selectStatement = table SELECT X
        }
    }
    // Get the max value
    selectStatement.getResult().maxOrNull()
    // Get the min value
    selectStatement.getResult().minOrNull()
    // Get the count of query results
    selectStatement.getResult().count()
    // ......
}

Finally, let's learn Advanced Query.