Moor (Room for Flutter) #2 - Advanced Queries & DAOs - Fluent SQLite Database

📗 Learn from the written tutorial & get the project files 👇👇
resocoder.com/...
👨‍💻 Do you write good code? Find out now 👇
resocoder.com/...
Moor is a powerful library for using an SQLite database from your Flutter apps by writing pure Dart code. In the first part of this series, we've only touched the basics of this package. Let's now take a look at some of the more advanced queries and also how to keep your code clean by separating it into Data Access Objects.
All of the code for queries currently lives inside the AppDatabase class itself. At the moment, that's perfectly fine - we are only dealing with Tasks. We have to think into the future though. Once we would start adding comments, habits and who knows what else into the app, placing all of the queries together would create a mess.
To separate the queries dealing with tasks (and in the future, with comments and habits), we can use data access objects (DAOs). The functionality of the queries will remain unchanged, they will only be moved to a new and cleaner place.
Go to the website for more information, code examples, and articles:
● resocoder.com
Follow me on social media:
● / resocoder
● / resocoder
● / resocoder
● gab.ai/resocoder

[@Hugamestricks]

This series of videos help me a lot! Subscribed and still waiting for more, Excellent!

[@JKPedapudi]

Thank you very much for the Awesome videos. Do you have a tutorial for using VS Code shortcuts when using Flutter & Dart

[@enafor99]

Thank you for this awesome tutorial!

[@AkshayTaru]

Question:
Can you have Table and Dao class and Database Class in different file, Just to keep it clean?
If yes How?

[@PankajNikam]

Hi, thank you so much for such great tutorial. One doubt though - why did you add the where clause after the order statement at 15:14? I believe that the ordering should be done with the least data available and I would have approached it with where first and then order by. Can you please guide?

[@function1983]

I believe you are right. This part bugs me also. But overall it's a very well made tutorial. Kudo to the creator of this video.

[@Gabzim]

super late to this but he’s just constructing the SQL statement. It doesn’t mean that the operations will happen in this order, that’s up to the database engine to decide. Once he’s done adding all the parts of the SQL statement, it’s sent to the engine and then it will filter first and then order.

[@PankajNikam]

@@Gabzim Thanks for the update. I completely forgot the optimization layer which must be present.

[@TareqAlmadhoun]

thanks.. great tutorials

[@YasinIlhan61]

Thanks for this tut. Can you create tutorial google sign in with provider package?

[@kirill4531]

how do you pass arguments if you choose to generate custom queries using annotation? where does annotation receive the arguments?

[@diveshnaik2248]

Hi there....i just love your videos. Can u tell how to execute this query "Select max(tran_id) from transaction"; i want to get only single id from the table.
Thank you

[@mehedihasansony]

is it necessary to use moor if i am comfortable with raw query? and how about the performance does it has any affect in doing so?

[@sohedulislam1652]

How to call custome query function and native query eg, "select * from mytable" from my dart function or flutter UI.

[@toBeABetterTranslator]

They now recommend using moor ffi, which doesn't fit in this video. Any advice?

[@safdhersahai894]

Hi, Good Tutorials....
I have a Doubt..
when i place & simbol (AND condition) in my code this shows syntax error.. how can I solve this issue?
"select(animals)..where((a) => a.isMammal.not() & a.amountOfLegs.equals(4))"

[@VimalMistry10]

How to check data is already in database and update otherwise create new.??

[@rubinbajracharya9319]

Hi there, how can i perform SELECT SUM(price) FROM TABLE_NAME WHERE completed = 1; in moor. From the Video I can only see that the query returns List/More than one rows.

[@function1983]

moor_flutter 2.0.0
return customSelectQuery(
"SELECT SUM(${carts.price.$name}) as total from ${carts.$tableName} WHERE ${carts.completed.$name} = ?''',
variables: [Variable.withBool(true)],
readsFrom: {carts},
).getSingle().then((row) => row.data["total"] as double);

[@gajendrapandeya5081]

so my problem was
I made id as autoincreament and set isCompleted field's default value to be false.
but while inserting a new task in onSubmit callback
it requires to pass both id and isCompleted columns as they are required field in generated dart file
What i did is, I modified generated dart file by setting isCompleted = false and id = 1.
On doing this, my app run sucessfully.
But this doesnot allow me to add another task as there can't be two task with same id.
How do i solve this error?
I copied everthing from resocoder website

[@irperera]

how do you empty the table created with moor?

[@function1983]

Future clearTables() {
return transaction(() async {
await customUpdate(
'DELETE from ${tasks.$tableName}',
updates: {tasks},
);
.... clear other tables....
});
}
There could be better ways in moor. But this works for me.

[@alixsonpl7973]

customSelectStream is removed from moor. What function should I use? customSelectQuery also deprecated

[@alixsonpl7973]

final query = customSelect('SELECT * FROM tasks WHERE completed = 1 ORDER BY due_date DESC, name;',
readsFrom: {tasks},);
return query
.map((row) => Task.fromData(row.data, db)).watch();

[@gabrieldragone]

Can someone show me an example to make manual select in moor? Something that run this query "Select field1, field2, field3 from table where field1 = null"?

[@amit.jangid]

Hi, How can I perform check for more than one columns in where clause?

[@function1983]

did you try
select(tasks)
..where((t)=>column_1_condition)
..where((t)=>column_2_condition)
OR you could use moor's customSelectQuery for more query flexibility

[@bisnake]

i tried to update with Companion and still request my nullable fields ::(

[@AliKhan-tu8pv]

body: SingleChildScrollView(
child: Column(
crossAxisAlignment: CrossAxisAlignment.start,
children: [
Text("love your videos ♥️"),
],
),
),

[@mulletman1705]

This seems over engineered to the extream compared to a package like swlfite