Drift search query in DropdownSearch for Flutter

Question

I'm using the Drift package in my Flutter project, and I need some help with a search feature in a DropdownSearchwidget. I have a table called ingredients defined like this:

class Ingredients extends Table {
  TextColumn get id => text()();
  TextColumn get ingredientName => text()();
  TextColumn get code => text()();
  RealColumn get glucides => real()();
}

I want to allow users to search ingredients from this table using a dropdown with a search field. Here's the dropdown implementation:

DropdownSearch<Ingredient>(
  selectedItem: ing,
  key: dropDownkey,
  decoratorProps: DropDownDecoratorProps(
    decoration: InputDecoration(
      hintText: 'Search field',
      hintStyle: GoogleFonts.lexend(
        fontWeight: FontWeight.w400,
        fontSize: 12.sp,
        color: AppColors.greyLight,
      ),
      border: InputBorder.none,
    ),
  ),
  items: (String? filter, _) {
    if (filter == null || filter.trim().isEmpty) {
      return ingredients; // Default list
    }

    final cleanedTerms = filter
        .split(RegExp(r'\s+'))
        .map((term) => term.replaceAll(RegExp(r'[^\w\s]+'), '').toLowerCase())
        .where((term) => term.isNotEmpty)
        .toList();

    return db.searchIngredients(cleanedTerms);
  },
  itemAsString: (Ingredient ingredient) => ingredient.ingredientName,
  onChanged: (Ingredient? selectedIngredient) {
    if (selectedIngredient != null) {
      int index = controllers.indexOf(controller);
      addOrUpdateIngredientWithGlucose(
        ingredients: [selectedIngredient],
        totalMass: controller.text.isNotEmpty
            ? double.parse(controller.text)
            : 0,
        index: index,
      );
    }
  },
  compareFn: (i1, i2) => i1?.id == i2?.id,
  popupProps: const PopupProps.menu(
    menuProps: MenuProps(backgroundColor: AppColors.white),
    showSearchBox: true,
    fit: FlexFit.loose,
    constraints: BoxConstraints(),
  ),
)

And here’s the searchIngredients function in my Drift database:

Future<List<Ingredient>> searchIngredients(List<String> userInput) async {
    if (userInput.isEmpty) return [];

    final query = select(ingredients);
    query.where((ingredient) {
      return userInput.map((term) {
        MyLogger.info('term: $term');
        MyLogger.info('ingredient: ${ingredient.ingredientName}');

        return ingredient.ingredientName.like('%$term%');
      }).reduce((value, element) => value | element);
    });

    return query.get();
  }

What I need help with:

• The dropdown doesn’t always return expected results when searching for multi-word inputs.
• Is my LIKE search in Drift correct for searching multiple keywords?

when I search on 'white sugar' I got result but when I search on 'sugar white' I got no result

Answer 1

The issue you're facing is due to how the searchIngredients function constructs the WHERE clause using Drift's like operator. The current implementation splits the input into terms and applies a LIKE condition for each term, combining them with an OR operator. However, this approach doesn’t account for the order of words or partial matches across the entire phrase effectively, which is why searching for "sugar white" doesn’t return results for "white sugar".

Here’s why:

When you search for "white sugar", the query checks if ingredientName contains "white" OR "sugar", and since "white sugar" contains both, it matches. When you search for "sugar white", the same logic applies, but the query doesn’t consider the phrase as a whole or the order of terms, leading to inconsistent results.

To fix this, you can improve the search logic to:

Treat the input as a single phrase for a LIKE search to match the exact sequence of words. Optionally, still support individual term searches with OR for flexibility. Normalize the input and database data (e.g., lowercase, trim spaces) to ensure consistent matching.

Here’s an updated searchIngredients function:

Future<List<Ingredient>> searchIngredients(List<String> userInput) async {
  if (userInput.isEmpty) return [];

  final query = select(ingredients);
  query.where((ingredient) {
    // Join terms to search as a single phrase
    final phrase = userInput.join(' ').toLowerCase().trim();
    final phraseCondition = ingredient.ingredientName.lower().like('%$phrase%');

    // Optionally, keep individual term search for broader matching
    final termConditions = userInput.map((term) {
      final cleanedTerm = term.toLowerCase().trim();
      return ingredient.ingredientName.lower().like('%$cleanedTerm%');
    }).reduce((value, element) => value | element);

    // Combine phrase and term conditions
    return phraseCondition | termConditions;
  });

  return query.get();
}

Explanation of Changes:

Phrase Search: The input terms are joined into a single phrase (e.g., "sugar white" becomes %sugar white%), and we use lower() to make the search case-insensitive. Term Search: The original logic is retained to allow matching individual terms (e.g., "sugar" OR "white"). Combination: The phraseCondition | termConditions ensures that either the full phrase matches or any individual term matches, improving flexibility. Normalization: Using lower() and trim() ensures consistent matching regardless of case or extra spaces.

Additional Suggestions:

Input Cleaning: In the DropdownSearch widget, ensure the cleanedTerms logic removes empty terms and normalizes input consistently: dart

final cleanedTerms = filter
    .split(RegExp(r'\s+'))
    .map((term) => term.replaceAll(RegExp(r'[^\w\s]+'), '').toLowerCase().trim())
    .where((term) => term.isNotEmpty)
    .toList();

Debugging: Add logging to verify the query results:

    final results = await query.get();
    MyLogger.info('Search results: ${results.map((i) => i.ingredientName).toList()}');
    return results;
    Performance: If the ingredient list is large, consider limiting results (e.g., query.limit(50)) to avoid performance issues.

This approach should make searches like "white sugar" and "sugar white" return consistent results.