Moor: Where statement on Joined table

[MarcinusX]

Hi!
Is it possible to add where statement to a joined table?
For example, I have such SQL query:

'SELECT * from $_personsTable as A ' +
            'INNER JOIN $_projectWorkersTable as B ' +
            'ON A.id = B.workerId ' +
            'WHERE B.projectId = ?'

In moor I have this:

List<TypedResult> results = await select(moorPersons).join([
      innerJoin(
        moorProjectWorkers,
        moorProjectWorkers.workerId.equalsExp(moorPersons.id),
      )
    ]).get();

But I have no idea how to put where statement on joined table. My only idea is to switch tables but it works only in this example.
I would appreciate your help :)

[simolus3]

You could use

final query = select(moorPersons).join([
  innerJoin(...),
])..where(moorProjectWorkers.projectId.equals(yourTargetId));
List<TypedResult> results = await query.get();

By the way: If you prefer to use sql, using moor files might be something worth looking into. You can put named sql queries in there, moor will analyze them and generate matching Dart methods. With imports, you can also use Dart-defined tables in moor files (small limitation: At the moment only the Dart database file or imports of it can be imported in moor files).

[MarcinusX]

Well, I'm surprised I didn't think about it :D Thank you!  <3

[gsouf]

@simolus3 it seems to fail when we have tables generated via dart classes and we write sql queries in a moor file. Moor generator is not able to associate tables and gives error

Could not find some_table. Available are: sqlite_master, sqlite_sequence, sqlite_master, sqlite_sequence

This is my useMoor statement:

@UseMoor(tables: [SomeTable], include: {'database_queries.moor'})

[simolus3]

You need to import the Dart file defining the table into the moor file:

-- This is database_queries.moor
import 'database.dart'; -- assuming that SomeTable is defined in database.dart

[gsouf]

@simolus3 just wow! That's really amazing thank you for your hard work