Moor: How can we use dynamic Multiple Where Clause?

[pranay2210]

Example

class MainItemManager
{
  final String itemsCode;
  final String stones;
  final String materials;
  MainItemManager({this.itemsCode,this.stones,this.materials});
}

 Future<List<MainItemManager>> filterMappingJoining( //here I would pass a Map where it would contain number of keys which will be column names and List of values to compare in where contion 
example:
My Map would look Like: {StoneCut: [Center, Medium, Rounded] //from stones table, costPrice: [5000,20000,100000] //from items table  }
)  async {
    final query = await ((selectOnly(items)..addColumns([items.itemID])).join([
      leftOuterJoin(stoneMappings, stoneMappings.ItemID.equalsExp(items.itemID)),
      leftOuterJoin(stones, stones.stoneID.equalsExp(stoneMappings.StoneID)),
      leftOuterJoin(materialMappings,materialMappings.itemID.equalsExp(items.itemID)),
      leftOuterJoin(materials, materials.materialID.equalsExp(materialMappings.materialID)),
      leftOuterJoin(miscellaneousMappings,miscellaneousMappings.itemID.equalsExp(items.itemID)),
      leftOuterJoin(miscellaneouses,miscellaneouses.miscellaneousID.equalsExp(miscellaneousMappings.miscID)),

    ])..where()..where().get();
//number of where() would be dynamic

 return query.map((result){
      return MainItemManager(
        itemsCode: result.read(items.itemID),
        stones: result.read(stones.stoneID),
        materials: result.read(materials.materialID)
      );
    }).toList();

so here my while conditions can be dynamic from any of the tables present in the joins and multiple where could be there so how can i pass the multiple conditions ?

[simolus3]

Something like this should work:

Future<List<MainItemManager>> filterMappingJoining(Map<String, List<Variable> values) async {
  final query = ((selectOnly(items)..addColumns([items.itemID])).join([
      leftOuterJoin(stoneMappings, stoneMappings.ItemID.equalsExp(items.itemID)),
      leftOuterJoin(stones, stones.stoneID.equalsExp(stoneMappings.StoneID)),
      leftOuterJoin(materialMappings,materialMappings.itemID.equalsExp(items.itemID)),
      leftOuterJoin(materials, materials.materialID.equalsExp(materialMappings.materialID)),
      leftOuterJoin(miscellaneousMappings,miscellaneousMappings.itemID.equalsExp(items.itemID)),
      leftOuterJoin(miscellaneouses,miscellaneouses.miscellaneousID.equalsExp(miscellaneousMappings.miscID)),
    ]);

  final table = allTables.singleWhere((t) => t.$tableName == yourTable);
   for (final entry in values.entries) {
     final column = table.columnsByName[entry.key];
     final columnMatches = entry.values.fold(null, (condition, element) {
       if (condition == null) return column.equalsExp(element);
       return condition | column.equalsExp(element);
     });     

     query.where(columnMatches);
   }

   final results = await query.get();
   // can now map those
}

You should probably also include stones and materials in addColumns - right now you're only using items.ItemID.

[pranay2210]

Something like this should work:

Future<List<MainItemManager>> filterMappingJoining(Map<String, List<Variable> values) async {
  final query = ((selectOnly(items)..addColumns([items.itemID])).join([
      leftOuterJoin(stoneMappings, stoneMappings.ItemID.equalsExp(items.itemID)),
      leftOuterJoin(stones, stones.stoneID.equalsExp(stoneMappings.StoneID)),
      leftOuterJoin(materialMappings,materialMappings.itemID.equalsExp(items.itemID)),
      leftOuterJoin(materials, materials.materialID.equalsExp(materialMappings.materialID)),
      leftOuterJoin(miscellaneousMappings,miscellaneousMappings.itemID.equalsExp(items.itemID)),
      leftOuterJoin(miscellaneouses,miscellaneouses.miscellaneousID.equalsExp(miscellaneousMappings.miscID)),
    ]);

  final table = allTables.singleWhere((t) => t.$tableName == yourTable);
   for (final entry in values.entries) {
     final column = table.columnsByName[entry.key];
     final columnMatches = entry.values.fold(null, (condition, element) {
       if (condition == null) return column.equalsExp(element);
       return condition | column.equalsExp(element);
     });     

     query.where(columnMatches);
   }

   final results = await query.get();
   // can now map those
}

You should probably also include stones and materials in addColumns - right now you're only using items.ItemID.

Do you mean Stone ID and Material ID? from items table

[pranay2210]

will this solution work for comparing price ranges like isBetween() in where conditions?

[pranay2210]

I'm getting this values and error while using your given approach

Actually the Class which I'm using to store the Map<> and tableName to pass the tableName dynamically

class StoreSelectedFilterValues {
  String tableName;
  Map<String,List<String>> valuesforSelectedFilters;
  StoreSelectedFilterValues(this.tableName,this.valuesforSelectedFilters);

}

 Future<List<MainItemManager>> testOperationForSelectedFilter(
      List<StoreSelectedFilterValues> values) async {
    final query = await ((selectOnly(items)..addColumns([items.itemID])).join([
      leftOuterJoin(
          stoneMappings, stoneMappings.ItemID.equalsExp(items.itemID)),
      leftOuterJoin(stones, stones.stoneID.equalsExp(stoneMappings.StoneID)),
      leftOuterJoin(
          materialMappings, materialMappings.itemID.equalsExp(items.itemID)),
      leftOuterJoin(materials,
          materials.materialID.equalsExp(materialMappings.materialID)),
      leftOuterJoin(miscellaneousMappings,
          miscellaneousMappings.itemID.equalsExp(items.itemID)),
      leftOuterJoin(
          miscellaneouses,
          miscellaneouses.miscellaneousID
              .equalsExp(miscellaneousMappings.miscID)),
    ]));

    for (var i = 0; i < values.length; i++) {
      final table =
          allTables.singleWhere((t) => t.$tableName == values[i].tableName);
      print("TABLE: $table");
      for (final entry in values[i].valuesforSelectedFilters.entries) {
//I'm Spliting the columnName as I'm getting the value of key '(stone_i_d)' to remove
//these brackets else I get error of "Tried Calling NULL...."
        String columnRefine= (values[i].valuesforSelectedFilters.keys).toString();
        var temp= columnRefine.split("(");
        temp = temp[1].split(")");
        print("Splitted: ${temp[0]}");
        String columnByRefine =temp[0];
        final column =
            table.columnsByName[columnByRefine];
        assert(column != null);
        final columnMatches = entry.value.fold(null, (condition, element) {
          print("ELEMENT: $element");
          print("COLUMN: $column");
          if (condition == null) return column.equals(element);
          return condition | column.equals(element);
        });

        query.where(columnMatches);
      }
    }
    final results = await query.get();
    return results.map((result) {
      return MainItemManager(
          itemsCode: result.read(items.itemID),
          stones: result.read(stones.stoneID),
          materials: result.read(materials.materialID));
    }).toList();
  }

Calling of Data from main page

    appDatabase.testOperationForSelectedFilter(storeObj).then((t) {
      print("Getting into Experiment");
      print("Printing Experimental Filter: ${t[0].stones}");
    }).catchError((error) => print("Error:$error"));

Output

flutter: TABLE: Instance of '$StonesTable'
flutter: ELEMENT: 13614
flutter: COLUMN: Instance of 'GeneratedTextColumn'
flutter: ELEMENT: 13615
flutter: COLUMN: Instance of 'GeneratedTextColumn'
flutter: Error:NoSuchMethodError: Class '_Comparison' has no instance method '|'.
Receiver: Instance of '_Comparison'
Tried calling: |(Instance of '_Comparison')
flutter: Moor: Sent SELECT * FROM classifications WHERE parent_classification_i_d = ?; with args [20876]
flutter: Entered parent ID: 20876

[simolus3]

Do you mean Stone ID and Material ID? from items table

Ah probably, yes. But if you're only interested in one table, I'm wondering why you would join all the other tables - do you need them for potential use in the where clause?

Error:NoSuchMethodError: Class '_Comparison' has no instance method '|'.
Receiver: Instance of '_Comparison'

The | should be an extension method. Maybe if you use fold<Expression<boolean, BoolType>> it will be inferred properly.

[pranay2210]

Do you mean Stone ID and Material ID? from items table

Ah probably, yes. But if you're only interested in one table, I'm wondering why you would join all the other tables - do you need them for potential use in the where clause?

Actually My itemId is mapped in stoneMapping table with stoneIDs and stones table has all the details, so is that the right way to join all the tables with items table that is the main one?

[pranay2210]

The | should be an extension method. Maybe if you use fold<Expression<boolean, BoolType>> it will be inferred properly.

I'm still confused about the fold(null,condition,element){} what is condition? and in my list I'm storing only ColumnName and their selected values so how will it get mapped with myMap.values.fold(null,condition,element){}

I'm getting an error

flutter: TABLE: Instance of '$StonesTable'
flutter: Splitted: stone_i_d
flutter: Error:NoSuchMethodError: The method 'writeInto' was called on null.
Receiver: null
Tried calling: writeInto(Instance of 'GenerationContext')

my code is
final columnMatches = entry.value.fold(0, (condition, element) { int len=values[i].valuesforSelectedFilters.length; if (condition == null) { return column.equals(element); } else if (len > 1) { return column.isIn(values[i].valuesforSelectedFilters.entries); }

[simolus3]

I'm still confused about the fold(null,condition,element){} what is condition

It will build a chain like column = ? OR column = ? OR column = ? in sql, where the variables will be bound to the Variable passed to the function. It's basically an isIn for variables.

The method 'writeInto' was called on null.

Can you post a stack trace or try to figure out what exactly is null in this case?

[pranay2210]

Can you make an example for this part

` final columnMatches = entry.value.fold(null,(condition,element){
if(condition==null) {
  return column.equals(element);
}
else if(//what will be this condition if I have to check 'isIN' Expression){
}
}`

or

` final columnMatches = entry.value.fold<Expresion<bool,BoolType>>{
if(condition==null) {
  return column.equals(element);
}
else if(//what will be this condition if I have to check 'isIN' Expression){
}
}`

How do I actually work on this nesting of conditions using fold in my case

[simolus3]

I started to use fold as a replacement for isIn since isIn doesn't work for variables - sorry if that caused confusion.

I see that your filter class is now this:

class StoreSelectedFilterValues {
  String tableName;
  Map<String,List<String>> valuesforSelectedFilters;
  StoreSelectedFilterValues(this.tableName,this.valuesforSelectedFilters);
}

If you want to apply a List<StoreSelectedFilterValues> as a list, you can use fold while iterating over all columns:

// List<StoreSelectedFilterValues> filterValues
for (final filter in filterValues) {
  final table = allTables.singleWhere((t) => t.actualTableName == filter.tableName);
  final predicate = filter.valuesforSelectedFilters.entries.fold<Expression<bool, BoolType>>(null, (condition, element) {
    // you might have to remove the () from element.key if that's how you get them
    final column = table.columnsByName[element.key]; 
    assert(column != null);

    final predicateForColumn = column.isIn(element.values);
    return condition == null ? predicateForColumn : condition | predicateForColumn;
  });
  query.where(predicate);
}

[pranay2210]

It's giving me an error while I execute and call

 appDatabase.testOperationForSelectedFilter(storeObj).then((t) {
      print("Printing Query Test: ${t[0].stones}");
    }).catchError((error) => print("Error:$error"));

flutter: Error:RangeError (index): Invalid value: Only valid value is 0: 1

[simolus3]

Can you post the stack trace? If it happens inside your callback, can you post what value t has?

[pranay2210]

NoSuchMethodError: The method 'stop' was called on null.Receiver: null Tried calling: stop()

t= {_GrowableList}size = 0

[simolus3]

Sorry for the back-and-forth on this, but I think I need more information on where the error happened. Flutter should print a trace of methods that caused the error, can you post that?

If you can post a small sample with table definitions for me to try out, I can take a direct look at the problem myself and figure out what's going on.

[pranay2210]

Thank you Simon, My issue is solved.