difference between ..join vs .join and ..where vs .where ?

[matthewkooshad]

i'm wondering, what's the difference between ..join vs .join and ..where vs .where ? this q came as a result of the problem i'm trying to figure out below if i can get some help with this too please.

using a ..where after a .join, i found i get this error:
The argument type 'Expression<bool?> Function(dynamic)' can't be assigned to the parameter type 'Expression<bool?>'.dart(argument_type_not_assignable)

if i switch to using ..where after ..join, then it works fine. in my map after awaiting a get on the query, i found the rows param is the type for the table that my select has specified. and, my rows param doesn't know about the joined table, even though the join has useColumns: true for the 2nd param. i'm wondering is what i'm experiencing with the rows param related to using ..join and ..where instead of .join and .where.

i found that i use .join and remove my ..where, then the type of my rows param in map is TypedResult, which is what i need. changing that .join back to ..join, it is as described above and no longer TypedResult.

so, if i use .join and try to use .where, i'm having this problem with my where:
The argument type 'Expression<bool?> Function(dynamic)' can't be assigned to the parameter type 'Expression<bool?>'.dart(argument_type_not_assignable)

the where i'm trying to use in a .where (with the .join so i can have a TypedResult), is like this: #767 (comment)

the reason i want a TypedResult, is so that i can map the query result from these 2 joined tables into a custom class, such as here: https://moor.simonbinder.eu/docs/advanced-features/joins/?#joins

[simolus3]

The .. operator in Dart is called the cascade notation. Basically, it allows invoking a method while evaluating to the value the method was called on. For instance myList..clear() evaluates to myList while also calling its void clear() method.

Through a bit of an unfortunate design choice, you should use .. for every method on a select statement except for join(). Methods like where, orderBy and limit all return void, so the cascading notation is a perfect tool to add those clauses to a query without using a temporary variable.
By design, join does not return void but a JoinedSelectStatement. If you used ..join(), you'd still get the original select statement without the join as that's how the .. operator works. So you would have to do something like (select(myTable)..where(...)).join(...)..limit(1) (note the parenthesis up until the .join). For readability, I recommend doing the join as early as possible and then add the clauses on the joined statement: (select(myTable).join(...))..where()..limit(). It may also help to use a variable to hold the query here:

final query = select(myTable).join([...]);
query..where(myPredicate)..limit(1);

return query.watchSingle();

[matthewkooshad]

that helps, thanks. but, i'm still getting that error in OP on my query..where(...), even with storing my query that uses .join in a variable to apply the ..where to.

from that link in OP, i'm having a ..where that looks like this:
..where((v) { final whereList = <Expression<bool>>[ if (hasParent) isNull(v.parentId), if (onlyActive) v.deleted.equals(false), ]; if (whereList.isEmpty) { whereList.add(const Constant(true)); } return whereList.reduce((clause, condition) => clause & condition); })

should that be a problem to use for the myPredicate in your query..where(myPredicate) ?

[simolus3]

Can you post the full query for reference? You can omit actual arguments, I just need to see at which point you're calling where exactly. It it's after the join, you should avoid using a function. Instead, just write the condition directly, something like:

final whereList = <Expression<bool>>[ if (hasParent) isNull(yourTable.parentId), if (onlyActive) yourTable.deleted.equals(false), ];
if (whereList.isNotEmpty) {
  yourQuery.where(whereList.reduce((clause, condition) => clause & condition));
}

(Replace youTable with the table you're doing the primary select on).

[matthewkooshad]

thanks for the note on "avoid using a function" and example on how to "write the condition directly" -- i got it to work following that example.