Variable in query

[andim27]

I need generate in dynamic variable conditions. I use next:

@DriftDatabase(
  tables: [Users],
  queries: {
    'userByCond': 'SELECT * FROM users WHERE ?1 LIMIT ?2',
  },
)

I need have :1 variable like String(it's generated in dynamic mode).
But in the *.g file I see Selectable userByCond(bool var1, int var2)

How to setup Var1 like String?

[simolus3]

Using direct variables as conditions doesn't make much sense as you've seen, since it applies the same value to every row - variables can only be bound to values, not to other SQL expressions.

However, you can use components in queries - those can modify the actual SQL:

@DriftDatabase(
  tables: [Users],
  queries: {
    'userByCond': r'SELECT * FROM users WHERE $predicate LIMIT ?',
  },
)

This generates a userByCond method with a predicate callback that can be used to build the condition.

[simolus3]

I have input parameter as String.

Which string values would you put into the query? Could you give some examples of what you're trying to do here? If you for instance use userByCond('id < 10', 5), the actual SQL that gets executed is SELECT * FROM users WHERE 'id < 10' LIMIT 5. It's just confusing to use strings here because any non-null value would return all rows from the table, and a null value would return none.

[andim27]

I need more explanation how to connect several expression.I have input condition like that:'id<10 and age >30'.This condition is dynamic(it can be like that too:'id>10 and age>30 and have_friend >5'.This is not a static field-one-condition.That why Expression connector is needed(not static '&,|| etc' which described in the documentation

[simolus3]

This works for me when using a named variable instead of the one with an explicit index:

class Users extends Table {
  IntColumn get id => integer().autoIncrement()();

  TextColumn get name => text()();
  IntColumn get age => integer()();
}

@DriftDatabase(tables: [
  Users
], queries: {
  'userByCond': r'SELECT * FROM users WHERE $predicate LIMIT :limit',
})
class MyDatabase extends _$MyDatabase {

Then, I can construct queries with different conditions like this:

Selectable<User> usersWithOneCondition() {
  return userByCond(
    predicate: (users) => users.age.isBiggerOrEqualValue(21),
    limit: 10,
  );
}

Selectable<User> usersWithAnotherCondition() {
  return userByCond(
    predicate: (users) =>
        users.id.equals(5) & users.age.isBiggerOrEqualValue(21),
    limit: 10,
  );
}

Of course, you can also invoke custom logic and so on in the callback to define any expression that you like on dynamic conditions.

[andim27]

Yes, it will works but it is a static definition of conditions. Suppose we have 30 input conditions.What to do in this case?According to your example we need 30 definitions of Selectable<Object> methods!My question how to combine with Expressions<T> ?
Can we do something like this ?

Selectable<User> usersWithAnotherCondition(List<Expression> cond) {
  return userByCond(
    predicate: (users) =>
      ExpressionAnd([for (var item in cond)  item]),
    limit: 10,
  );
}

[simolus3]

There is nothing that forces you to build the condition inside that method. You can also have a general one like

Selectable<User> findUsers({UserByCond$predicate? condition}) {
  return userByCond(
    // allow callers to pass a condition, or return all users by default
    predicate: condition ?? (users) => const Constant(true),
    limit: 10,
  );
}

And then someone could all findUsers() (or even userByCond) with whatever condition they want, including one that they're building dynamically.

You can read more about expressions in the docs, but you can compose them in any way you like, including with dynamic conditions:

findUsers(condition: (users) {
  var condition = users.age.isBiggerOrEqualValue(21);
  if (someHighlyDynamicCondition) {
    condition = condition & someOtherConditionYouMightWantToAddAsWell;
  }
  
  return condition;
})

Can we do something like this ?

Yes, you can type the parameter as List<Expression<bool>> cond and then do cond.reduce((a, b) => a & b) to apply all conditions.