
# Derived queries

A derived query is a repository method whose name is the query: `findByTitleContainingIgnoreCase(String text)` loads the books whose title contains `text`, ignoring case. The build parses the name, checks it against the entity, and writes a constant query tree for it. The same method runs on every store: in memory, SQLite and PostgreSQL.

## Why

Most finders are simple conditions on a few columns. Writing SQL for each one is noise, and SQL in strings is checked only when it runs. A method name is short, reads like the question, and the build checks it: `findByTitel` is a build error with `did you mean 'title'?`, not a failure in production.

## Example

```dart
abstract interface class BookRepository implements ListCrudRepository<Book, int> {
  Future<List<Book>> findByTitle(String title);
  Future<List<Book>> findByTitleContainingIgnoreCase(String part);
  Future<List<Book>> findByTitleAndYear(String title, int year);
  Future<List<Book>> findByYearBetween(int from, int to);
  Future<List<Book>> findByYearIn(List<int> years);
  Future<List<Book>> findBySubtitleIsNull();
  Future<List<Book>> findByInStockTrue();

  Future<Book?> findOneByTitle(String title);       // null when none
  Future<Book> getByTitle(String title);            // EmptyResultDataAccessException when none
  Future<Book?> findFirstByOrderByYearAsc();        // the oldest
  Future<List<Book>> findTop2ByOrderByYearDesc();   // the two newest
  Future<List<Book>> findByInStockOrderByYearDescTitleAsc(bool inStock);
  Stream<Book> streamByInStock(bool inStock);       // rows as they come

  Future<int> countByInStock(bool inStock);
  Future<bool> existsByTitle(String title);
  Future<int> deleteByYearLessThan(int year);       // the number deleted
  Future<void> removeByTitle(String title);
  Future<List<Book>> deleteByInStock(bool inStock); // the deleted aggregates
}
```

## The parts of a name

`findTop5ByTitleContainingIgnoreCaseAndYearGreaterThanOrderByYearDesc` reads as:

| Part | Here | Meaning |
|---|---|---|
| Subject | `find` | What to do with the matches. |
| Limit | `Top5` | At most 5 results (`First` alone is 1). |
| `By` | `By` | The conditions start. |
| Condition | `TitleContainingIgnoreCase` | A property, an operator, and maybe `IgnoreCase`. |
| `And` / `Or` | `And` | Joins conditions; `And` binds tighter than `Or`. |
| Condition | `YearGreaterThan` | |
| Order | `OrderByYearDesc` | The order of the results. |

## Subjects

| Prefix | Does | Returns |
|---|---|---|
| `find`, `read`, `get`, `query`, `search`, `stream` | Loads the matches. | `List`, `Iterable`, `Set`, `Stream`, `Page`, `Slice`, or one entity |
| `watch` | Loads the matches and keeps them up to date, on a store that reports its changes. See [Changes of rows](changes.md). | `Stream<List<Book>>` |
| `changes` | The changes of the rows the conditions match, on such a store. | `Stream<RowChange<Book>>` |
| `count` | Counts the matches. | `Future<int>` |
| `exists` | Whether any matches. | `Future<bool>` |
| `delete`, `remove` | Deletes the matches, with their children. | `Future<int>` (the count), `Future<void>`, or the deleted aggregates (`Future<List<Book>>`) |

`Distinct` after the prefix (`findDistinctByInStock`) removes duplicates. `Top<n>` and `First<n>` limit the results; with a `Pageable`, the page's window is cut at that limit (`findTop3By...(Pageable)` with a page size of 2 gives pages of 2 and 1). `findAll...` and `countBy` with nothing after `By` match every aggregate.

## Operators

| Keyword (and its aliases) | Parameters | Condition |
|---|---|---|
| (none), `Is`, `Equals` | 1 | `= value` |
| `Not`, `IsNot` | 1 | `<> value` |
| `Between`, `IsBetween` | 2 | `between from and to` |
| `LessThan`, `IsLessThan`, `Before`, `IsBefore` | 1 | `< value` |
| `LessThanEqual`, `IsLessThanEqual` | 1 | `<= value` |
| `GreaterThan`, `IsGreaterThan`, `After`, `IsAfter` | 1 | `> value` |
| `GreaterThanEqual`, `IsGreaterThanEqual` | 1 | `>= value` |
| `IsNull`, `Null` | 0 | `is null` |
| `IsNotNull`, `NotNull` | 0 | `is not null` |
| `Like`, `IsLike` | 1 | `like value` (your pattern) |
| `NotLike`, `IsNotLike` | 1 | `not like value` |
| `StartingWith`, `IsStartingWith`, `StartsWith` | 1 | `like 'value%'` |
| `EndingWith`, `IsEndingWith`, `EndsWith` | 1 | `like '%value'` |
| `Containing`, `IsContaining`, `Contains` | 1 | `like '%value%'` |
| `NotContaining`, `IsNotContaining`, `NotContains` | 1 | `not like '%value%'` |
| `In`, `IsIn` | 1 (a collection) | `in (...)` |
| `NotIn`, `IsNotIn` | 1 (a collection) | `not in (...)` |
| `True`, `IsTrue` | 0 | `= true` |
| `False`, `IsFalse` | 0 | `= false` |

`IgnoreCase` after a condition compares in lower case; `AllIgnoreCase` at the end applies it to every condition. The parameters follow the conditions in order, then an optional `Sort` or `Pageable`.

## Values that are text

- In `StartingWith`, `EndingWith` and `Containing`, the argument is text, not a pattern: `%`, `_` and `\` in it are escaped by the starter, on every store. In `Like` the pattern is yours.
- A NUL character (`\u0000`) in a text argument is refused before anything is sent: an `InvalidDataArgumentException` (an `InvalidDataAccessApiUsageException`), on every store. PostgreSQL cannot hold one, and SQLite's `LIKE` stops reading at it.
- `IgnoreCase` folds every letter, not only ASCII: on SQLite the driver registers a `lower` that uses Dart's `toLowerCase`, so `ДЮНА` matches `дюна` as in the in-memory store. On PostgreSQL it is `lower()`, which folds what the database's locale knows.

## Long In lists

On PostgreSQL a derived `In` or `NotIn` binds the list as one array parameter (`= any($1)`, `<> all($1)`), so any length works. SQLite binds one value per element and allows 32 766 values per statement: a longer list is an `InvalidDataArgumentException` that says so. A `null` in the list is ignored by `In`, and makes a `NotIn` match nothing, as in SQL.

## One result

| Return type | No match | More than one |
|---|---|---|
| `Future<Book?>` | `null` | `IncorrectResultSizeDataAccessException` |
| `Future<Book>` | `EmptyResultDataAccessException` | `IncorrectResultSizeDataAccessException` |

With `First` or `Top`, more than one match is not an error: the first is taken.

## Order

`OrderBy` names one or more properties, each with `Asc` (the default) or `Desc`: `OrderByYearDescTitleAsc`. A `Sort` or `Pageable` parameter adds its orders after the name's. See [Paging and sorting](paging-and-sorting.md).

## Transactions and locks

- `deleteBy` runs in a transaction of the repository's store, joining the caller's when there is one.
- A finder marked `@Lock` locks the rows it reads until the transaction ends (see [Locks](locks.md)).

## What the build checks

- Each property exists on the entity and is stored (not `@Transient`), with a suggestion when it is misspelled.
- The number of parameters matches the conditions; a `Pageable` or `Sort` comes last.
- `References` are compared by their own type: `findByEditor(AggregateReference<Editor, int>)`, or `findByAuthorId(int)` for a `@Reference` property.

In the Zed extension `dart_spring`, the language server reads names with the same grammar while you type: it underlines an unknown property with a suggestion, checks the parameter count, and shows the SQL the name stands for on hover.

## In Spring (Java)

```java
List<Book> findByTitleContainingIgnoreCase(String part);
Optional<Book> findFirstByOrderByYearAsc();
long countByInStock(boolean inStock);
```

The grammar and keywords are Spring Data's. Results are `Future`s or `Stream`s, a single result is nullable instead of `Optional`, and the name is checked by the build instead of at startup.

## See also

- [Repositories](repositories.md)
- [Paging and sorting](paging-and-sorting.md)
- [@Query](query.md)
