Custom queries

Let drift generate Dart from your  drift watch SQL statements
Although float includes a fluent api that can be used to model most statements, advanced capabilities like WITH clauses or subqueries aren’t supported yet. You can use those capabilities with custom statements. You don’t have to pass over out on other benefits waft brings, although: Drift allows you parse the end result rows and custom queries additionally assist automobile-updating streams.

Statements with a generated api
You can instruct waft to routinely generate a typesafe API to your pick, replace and delete statements. Of route, you can still write custom square manually. See the sections below for information.

To use this feature, all you want to is define your queries on your DriftDatabase annotation:

@DriftDatabase(
tables: [Todos, Categories],
queries:
‘categoriesWithCount’:
‘SELECT *, (SELECT COUNT(*) FROM todos WHERE category = c.Identity) AS “quantity” FROM categories c;’
,
)
class MyDatabase extends _$MyDatabase
// relaxation of sophistication remains the same

After running the construct step once more, waft can have written the CategoriesWithCountResult class for you – it will preserve the result of your query. Also, the _$MyDatabase elegance from which you inherit could have the methods categoriesWithCount (which runs the question as soon as) and watchCategoriesWithCount (which returns an vehicle-updating movement).

Queries will have parameters in them with the aid of using the ? Or :call syntax. When your queries consists of parameters, glide will parent out the correct type for them and encompass them inside the generated strategies. For example, ‘categoryById’: ‘SELECT * FROM classes WHERE identity = :identification’ will generate the method categoryById(int id).

On desk names
To use this selection, it is useful to realize how Dart tables are named in sq.. For tables that don’t override tableName, the call in square could be the snake_case of the magnificence call. So a Dart desk referred to as Categories could be named categories, a desk called UserAddressInformation would be called user_address_information. The equal rule applies to column getters with out an explicit name. Tables and columns declared in Drift files will continually have the name you specific.

You can also use UPDATE or DELETE statements here. Of route, this selection is likewise available for daos, and it perfectly integrates with car-updating streams by way of analyzing what tables you are analyzing from or writing to.

Custom pick out statements
If you don’t need to apply the statements with an generated api, you may nonetheless ship custom queries through calling customSelect for a one-time question or customSelectStream for a query flow that automatically emits a brand new set of gadgets whilst the underlying facts modifications. Using the todo example delivered in the getting started out manual, we will write this query if you want to load the quantity of todo entries in each class:

elegance CategoryWithCount
final Category category;
final int rely; // quantity of entries on this category

CategoryWithCount(this.Class, this.Count);

// then, inside the database elegance:
Stream<List<CategoryWithCount>> categoriesWithCount()
// select all classes and load what number of related entries there are for
// every class
return customSelect(
‘SELECT *, (SELECT COUNT(*) FROM todos WHERE category = c.Identity) AS “amount” FROM categories c;’,
readsFrom: todos, classes, // used for the movement: the stream will update when both desk changes
).Watch().Map((rows)
// we get list of rows here. We just have to turn the raw records from the row right into a
// CategoryWithCount. As we described the Category desk earlier, go with the flow is aware of how to parse
// a category. The handiest component left to do manually is extracting the amount
go back rows
.Map((row) => CategoryWithCount(Category.FromData(row.Records, this), row.ReadInt(‘amount’)))
.ToList();
);

For custom selects, you ought to use the readsFrom parameter to specify from which tables the query is analyzing. When the use of a Stream, float might be able to realize after which updates the flow ought to emit gadgets.

You can also bind SQL variables with the aid of using question-mark placeholders and the variables parameter:

Stream<int> amountOfTodosInCategory(int identity)
return customSelect(
‘SELECT COUNT(*) AS c FROM todos WHERE category = ?’,
variables: [Variable.WithInt(id)],
readsFrom: todos,
).Map((row) => row.ReadInt(‘c’)).Watch();

Of route, you could additionally use listed variables (like ?12) – for more facts on them, see the sqlite3 documentation.

Custom replace statements
For update and delete statements, you may use customUpdate. Just like customSelect, that approach also takes a square declaration and non-obligatory variables. You can also tell float which tables can be suffering from your question the usage of the non-compulsory updates parameter. That will assist with other select streams, as a way to then update mechanically.

Leave a comment

Your email address will not be published.