Query Builder

• Query Builder
  • Introduction
  • Accessing the Repo
    • Quick Data Access
    • Starting a query
    • Query Filters and Options
      • where
      • whereId
      • like
      • lessThan
      • greaterThan
      • isNull
      • notNull
      • orderBy
      • groupBy
      • limit
      • offset
      • limit & offset together
      • withDeleted
      • onlyDeleted
      • search
  • Executing the query
    • first
    • all
    • get
    • stream
    • count
    • hasRecords
    • isNotEmpty
    • isEmpty
    • delete
    • finalDelete

Introduction

The Query Builder aims to provide a simple to use but also powerful interface for querying entities. It also handles logic related to soft-deletes automatically.

Accessing the Repo

The repository is returned initially by the register method but you can retrieve it at any time using the static get method.

Repo<MyModel> repo = Repo.get(MyModel.class);

Quick Data Access

The repository itself has a few quick access methods for querying data.

• get(id)
• all()
• stream()
• first()

Starting a query

You can start a query either by using the query() method or by directly using the where(field [, operation], value) method on the repository.

Query<MyModel> query = Repo.get(MyModel.class).query();

Query Filters and Options

These are the methods which can be called on a query builder. They are following the builder pattern and return the query itself. Make sure to to field names and not the actual column names.

where

This is the most important method for filtering entities. It allows to compare a field with a field using different supported operators (default "=").

Repo.get(User.class).where("age", ">=", 18) // All users more than or exactly 18 years old

whereId

Similar to the normal where method, it allows to compare a value against the id field without directly referencing it.

Repo.get(User.class).whereId(1) // Gets the user where id=1

like

Sometimes you want to search for values which are not equal but similar. SQL provides the "LIKE" operator to achieve this. This method is a shorthand for where(field, "LIKE", value).

Repo.get(Article.class).like("title", "%" + keyword + "%") // All articles containing the keyword in the title

lessThan

This method is a shorthand for where(field, "<", value).

Repo.get(User.class).lessThan("age", 18) // All users younger than 18

greaterThan

This method is a shorthand for where(field, ">", value).

Repo.get(User.class).greaterThan("age", 17) // All users older than 17

isNull

This method filters for entities where the given field's value is null.

Repo.get(User.class).isNull("description") // All users without a description

notNull

This method filters for entities where the given field's value isn't null.

Repo.get(User.class).notNull("description") // All users with a description

orderBy

This method orders the results by the given field and returns them in descending order if the optional second parameter is set to true.

Repo.get(User.class).orderBy("age", true) // All users starting with the oldest descending to the youngest

groupBy

This method groups the results by the given column name.

Repo.get(User.class).groupBy("age") // Groups all users with the same age together.

limit

Limit the amount of entities returned.

Repo.get(User.class).limit(3) // Only the first 3 users

offset

Shifts the entries by n, useful in combination with limit.

Repo.get(User.class).offset(1337) // Starts at the 1337th User

limit & offset together

The functionality of limit and offset together.

Repo.get(User.class).limit(1337, 100)

withDeleted

If soft-deletes are enabled for the model, this will include soft-deleted entities into the query.

Repo.get(Article.class).withDeleted() // All articles ever written including the deleted ones.

onlyDeleted

If soft-deletes are enabled for the model, this will only include soft-deleted entities into the query.

Repo.get(Article.class).onlyDeleted() // All articles which are deleted.

search

The search method, will search over all fields annotated by the @Searchable annotation.

Repo.get(User.class).search("John")

Executing the query

You can execute the query and retrieve the result using one of the following methods.

first

The first() method will execute the query and return the first entity or null if none was found.

all

The all() method will execute the query and return a Java List holding all entities. If no entities are found the List will be empty.

get

The get method is just an alias for the all method.

stream

Similar to the all method this will return all results but in a Java Stream which can be used for further processing in a functional way.

count

Returns the amount of entities found by the query.

hasRecords

The hasRecords() method will execute the query and return a boolean, if the query returned 1 or more results. Uses the count method internally.

isNotEmpty

The isNotEmpty() method will execute the query and return a boolean, if the query returned 1 ore more results. This method replaces the deprecated hasRecords method.

isEmpty

The isEmpty() method will execute the query and return a boolean, if the query returned 0 results. This method negates the result of the isNotEmpty method.

delete

Will delete each entity found by the query.

finalDelete

If soft-deletes are enabled for the model, this will delete the entities from the database instead of just setting the deletedAt column.