Moor: Can we create a query for search inventory items where user will have the freedom to choose the table name, column name and value?

[pranay2210]

Actually I want to create a search query where usr could pass tableName, columnName, and Value for search dynamically in a manner example:-

Future<List<foo>> getItems(tableName, columnName, value)
{
//here I would be able to search using (select * from tableName where  columnName=value)
//can i do something like this?

}

[simolus3]

It's hard for moor to generate apis for that because the return type would depend on what you pass as tableName. That said, maybe something like this could work:

Future<List> getItems<V>(String tableName, String columnName, V value) {
  final table = allTables.singleWhere((t) => t.tableName == tableName);
  final column = table.columnsByName[columnName];
  assert(column != null);

  final query = select(table)
    ..where((row) => column.equalsExp(Variable<V>(value)));

  return query.get();
}

[pranay2210]

it gives error for .columnsByName
and Variable

I guess, columns couldn't be retrieved

[simolus3]

Which moor version are you on? That code compiles for me on the latest version.

[pranay2210]

I upgraded my moor to moor: 2.4 and moor_flutter: 2.1.1 and still getting an issue with Variable, Can I pass a "var value" instead??

[simolus3]

Ah right, sorry. I ran that on my develop branch. With the latest stable version this is very awkward to use:

Future<List> getItems<V, T extends SqlType<V>>(String tableName, String columnName, V value) {
  final table = allTables.singleWhere((t) => t.$tableName == tableName);
  final column = table.columnsByName[columnName];
  assert(column != null);

  final query = select(table)
    ..where((row) => column.equalsExp(Variable<V, T>(value)));

  return query.get();
}

You would then have to specify the type parameters when using that function, e.g. await getItems<int, IntType>(...).
At least it will get better in moor 3.0

[pranay2210]

I didn't get the e.g. await getItems(...). what parameters do
we need to pass? I'm confused, can you help me to resolve this?

On Mon, Feb 24, 2020 at 2:16 PM Simon Binder [e-mail]
wrote:

Ah right, sorry. I ran that on my develop branch. With the latest stable
version this is very awkward to use:

Future getItems>(String tableName, String columnName, V value) {
final table = allTables.singleWhere((t) => t.$tableName == tableName);
final column = table.columnsByName[columnName];
assert(column != null);

final query = select(table)
..where((row) => column.equalsExp(Variable(value)));

return query.get();
}

You would then have to specify the type parameters when using that
function, e.g. await getItems(...).
At least it will get better in moor 3.0

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/simolus3/moor/issues/404?email_source=notifications&email_token=AJBXU6C6TMOPJKKYWGWWQULREOCOBA5CNFSM4KYHQWT2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEMXAFRY#issuecomment-590217927,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AJBXU6DDGNWVKPXQQXVJKOTREOCOBANCNFSM4KYHQWTQ
.

[simolus3]

Of course. The second type parameter denotes which type a column or variable would have _in sql_. An instance of that class is also responsible to map Dart values from and to sql. When you use variables, that type needs to be set so that the variable can be mapped to sql.

The mapping is

• int -> IntType
• double -> RealType
• String -> StringType
• DateTime -> DateTimeType
• Uint8List -> BlobType

In the next breaking moor version we'll remove this. Perhaps, a better way to write this would be

Future<List> getItems(String tableName, String columnName, Variable value) {
  final table = allTables.singleWhere((t) => t.$tableName == tableName);
  final column = table.columnsByName[columnName];
  assert(column != null);

  final query = select(table)
    ..where((row) => column.equalsExp(value));

  return query.get();
}

You could then just use getItems(tableName, columnName, Variable.withInt() instead of that weird IntType thing.

[pranay2210]

I'm getting the same error again and my calling is something like this
await appDatabase.getItems("items", "item_type", "EAR").then((t){print("Printing Values for dynamic Table fetching : $t");});

[simolus3]

Yeah the type arguments need to be set on the Variable you pass. If you write <String, StringType> in the method definition, those will be type variables and won't point to an actual class.

If you know you're only going to pass strings, you could use

Future<List> getItems(String tableName, String columnName, String value) {
  // ...
    ..where((row) => column.equalsExp(Variable.withString(value))
}

If you want a more general approach you can use the method from my previous comment.

[pranay2210]

Thank you so much Simon, its working and keep it up!!

[pranay2210]

One more thing, can I get specific columns like select column1, column2 from tableName.. instead of select * from tableName... while selecting by passing parameters?

[simolus3]

This should be possible in the latest moor version, yes. Instead of select, you'll want to use selectOnly, then you'd use addColumns to add the columns you need (you can look those up by their name as well).