[1] Core

Core module in DOPE Query Builder

The Core module includes all the implementations for functions that replicate SQL++ functions.

General

With DOPE Core, you can build an AST (Abstract Syntax Tree) consisting of various Clauses and Expressions. The AST can be built using the build() function, which returns a DopeQuery. The DopeQuery contains a string with the query and a map with all the parameters used in the query.

---

Expressions

NumberExpression

NumberExpressions are expressions that produce a numeric result. These are useful for performing arithmetic operations within your query. For example, you can add a constant to a field, multiply a field by a constant, or combine multiple operations.

personAge.plus(3) // -> "person.age + 3"
2.mul(personAge).sub(5) // -> "2 * person.age - 5"

StringExpression

StringExpressions are expressions that produce a string result. These expressions are handy for string manipulation within your queries. You can concatenate strings, trim whitespace, or perform other string operations.

concat("_", personName, "_") // -> "CONCAT("_", person.age, "_")"
ltrim(personName) // -> "LTRIM(person.name)"

BooleanExpression

BooleanExpressions are expressions that produce a boolean result. These expressions are useful for making comparisons and logical operations within your query. You can check for equality, pattern matching, or null checks.

personAge.isEqualTo(18) // -> "person.age = 18"
personName.isLike("A%") // -> "person.name LIKE "A%""
personName.isNotNull() // -> "person.name IS NOT NULL"

Fields

A Field in DOPE represents a property in a JSON document and can have the same ValidTypes as some Expressions.

Here's how you can create a Field:

val personName = Field<StringType>(name = "name", path = "person")
val personAge = Field<NumberType>(name = "age", path = "person")
val isPersonEmployed = Field<BooleanType>(name = "isEmployed", path = "person")

In this example, we have a String property person.name, a Number property person.age and a Boolean property person.isEmployed.

Parameters

The build() function also returns Parameters, which are stored in a map with names or positions as keys and values. To create a Parameter, you can use the asParameter() extension function, which works with Numbers, Strings, Booleans and Collections.

Named Parameters

You can create a Named Parameter by passing a name to the asParameter()-function.

7.asParameter("number") // -> "$number"

Positional Parameters

If you don't pass a name, DOPE creates a Positional Parameter.

7.asParameter() // -> "$1"

Example

val parameter1 = 2.asParameter()
val parameter2 = 5.asParameter("param")
val parameter3 = "peter".asParameter()

val result = QueryBuilder()
    .select(
        parameter1.isEqualTo(parameter2)
    ).where(
        personName.isNotEqualTo(parameter3)
    ).build()

// result.queryString = "SELECT $1 = $param WHERE person.name != $2"
// result.parameters = { "$1": 2, "param": "5", "$2": "peter"}

---

Clauses

Select

DOPE has several Select Clauses for different purposes. All of them can be created using the QueryBuilder. The basic Select Clause takes multiple Expressions and looks like this:

val result = QueryBuilder()
    .select(personName, personAge) // personName and personAge are the Fields created above
    .build()

// result.queryString = "SELECT person.name, person.age"

Other Selects include Select Raw and Select Distinct.

You can chain multiple Clauses like this:

val personBucket = UnaliasedBucket("person")

val result = QueryBuilder()
    .select(personBucket.asterisk())
    .from(personBucket)
    .join(
        UnaliasedBucket("city"),
        onCondition = personCityId.isEqualTo(cityId),
    )
    .where(personAge.isGreaterThan(14))
    .groupBy(personName)
    .orderBy(personAge)
    .limit(10)
    .offset(5)
    .build()

// result.queryString = "SELECT person.* FROM person WHERE person.isEmployed JOIN city ON person.cityId = city.id GROUP BY person.name ORDER BY person.age LIMIT 10 OFFSET 5"

In DOPE we support currently these Clauses:

• Select
• Select Raw
• Select Distinct
• From
• Join
• Unnest
• Where
• OrderBy
• GroupBy
• Limit
• Offset
• Delete
• Returning

---