Moor: Where is the doc??

[reyicare]

Hello,

Sorry but for me the documentation is good for simple query but not enough for more complex... No good documentation about what is moor_ffi, no documentation to make pure SQL queries because I found nothing about "how to construct right query with relation in dart"

Can you help me? Because now I loose a lot of time to implement simple query I can make in 30sec in pur SQL like that :

I did already something like that to found items in lendings but to found the other items without this FK it's seriously not possible... The documentation is poor for that and I waist a lost of time with that and other tasks

Otherwise I'm obliged to found another solution and redo all my app. Thanks

[kuhnroyal]

You can find the docs linked in the README, the pub.dev page and in the repository itself: https://moor.simonbinder.eu/docs/using-sql/moor_files/

[reyicare]

yes thank you, I saw this link but it remains a very bad documentation. everything is in draft I have the impression with outdated documentations. (often deprecated) and in your link I don't see how to return the result from the query to stream it. And my database is already created via the .dart file. I will stop there if there is no better documentation.

[kuhnroyal]

There is no "better" documentation. You are right some thing may be a little bit outdated but there is nothing fundamentally wrong in the documentation. However things like .moor files require some investment on your side to take the time and read the full documentation, specifically regarding DAOs and generation.
If you follow the documentation you will find that moor can generate the methods for you SQL queries either in a DAO object or in your database class.

If you have already created all the tables as Dart files then you can not easily use .moor files and need to write your queries in Dart as well .

[reyicare]

ok thanks for your answer, that's what i thought. So I think I have better time to go directly to sqflite and do some raw queries if everything needs to be changed again. Because if it is to create tables by hand via SQL, you might as well take Sqflite and not be blocked because it is impossible to write an SQL query via the dart interface of moor. I don't really understand the interest of moor anymore if we don't use models to create tables and links

[kuhnroyal]

From my experience it seems easier to start with Dart models but as soon as you get to the point of table migrations, you have to write SQL anyways. So why not use it from the start, you can even use custom converters and so on in moor files.

[simolus3]

select * from items left join lendings l on items.id = l.item_id where l.item_id IS NULL

That query doesn't make much sense considering that the join predicate contradicts the WHERE. Still, you can write that in Dart using

final query = select(items).join([
  leftOuterJoin(lendings, items.id.equalsExp(lendings.itemId))
])..where(isNull(lendings.itemId));

Where

• leftOuterJoin is explained here
  
  
  
  • ìsNull is explained here
  
  

I don't see how to return the result from the query to stream it

You'd have to use .watch(). This is kind-of explained here, but I can see how that's not obvious. We also have Dart documentation though, and that explains the get and watch methods.

No good documentation about what is moor_ffi

What do you want to know about moor_ffi that isn't explained here?

no documentation to make pure SQL queries

There's a big section called Using SQL that explains how to use SQL with moor. Is that not what you were looking for?

So I think I have better time to go directly to sqflite and do some raw queries

Even if you prefer raw queries, moor can provide convenience features like checking your queries at compile time and auto-updating streams. It will also generate all the mapping code and classes for you.

it is impossible to write an SQL query via the dart interface of moor

You can use custom queries in your annotations to add raw sql queries that operate on Dart tables. It's true that the documentation on them is a bit misleading: While I think that .moor files are a better alternative, that feature not deprecated and it's still supported. Instead of using customSelectStream, you now need to use customSelect(...).watch(). I updated the documentation for this in 0d88f209bb37a8c5baeaafeb5781ad2e04d168fd.

You can also import your Dart files into a .moor file to make Dart tables available to that file.

I can understand your frustration, and I'm happy to improve the documentation if you have constructive criticism.

[kuhnroyal]

You can also import your Dart files into a .moor file to make Dart tables available to that file.
I didn't know that, that seems like a good alternative if you started with Dart classes.

[reyicare]

final query = select(items).join([
  leftOuterJoin(lendings, items.id.equalsExp(lendings.itemId))
])..where(isNull(lendings.itemId));

Thanks, that's exactly what I was looking to do. For me it makes sense because I need a result like this:

For the rest of your answer I'll see if it can help me in my cases. Concretely in my opinion the documentation lacks real example style an example project on which to base. I helped a lot from this article https://resocoder.com/2019/07/10/moor-room-for-flutter-2-advanced-queries-daos-fluent-sqlite-database/ but again your versions have changed so quickly with major version breaks and some things in your documentation are outdated.

Ok thanks for "customselectStream" because I couldn't find it. So I know I can use "customSelect" because I actually only had that available with "moor_flutter: ^ 3.1.0".

Concretely for me what is missing are concrete examples (but that's my opinion). "to make the glue with everything" In any case thank you for your help and the speed of your answer

[reyicare]

You can also import your Dart files into a .moor file to make Dart tables available to that file.
I didn't know that, that seems like a good alternative if you started with Dart classes.

Ok great I'll try that then. Because I found it a shame to have started with the models which for me is really a time saver and in fact having to go entirely through pure SQL in order to create the tables and be able to query them. Thanks

[simolus3]

For me it makes sense because I need a result like this:

Ah yeah, good point.

"to make the glue with everything"

This repo contains a small, but fully working app using moor across different platforms. It doesn't use daos or moor files though...

Ok great I'll try that then

There's a generator bug that causes duplicate code to be written when tables are imported in a moor file and used in UseMoor.tables (#447). If that happens you can just use @UseMoor(tables: [], include: {'tables.moor'}), assuming that tables.moor is the moor file with Dart imports.

Since it sounds like moor files or custom queries otherwise fix your problem, I'll close this. Let me know if you run into any issues though!