CozyQueryController

This section provide details on `CozyQueryController`.

The CozyQueryController manages query operations for a CozyQueryListener. It allows adding where conditions, joins, order by fields, and custom queries to the listener.

Available APIs

final controller = CozyQueryController<MyModel>();
await controller.addWhere([PredicateGroup(predicates: [Predicate.equals('field', 'value')])]);
await controller.addJoin([Join(table: 'other_table', condition: 'other_table.id = my_table.other_id')]);
await controller.orderBy([OrderBy(field: 'created_at', direction: OrderDirection.desc)]);
await controller.addCustomQuery(CozyQueryBuilder<MyModel>());
await controller.reset();

Where Predicate

The predicate addWhere is use to apply operation quey on our data and it support The query builder supports various SQL operations including:

FeatureOperations
Basic comparisons=, !=, >, <, >=, <=
String operationsLIKE, NOT LIKE, REGEXP
Case-insensitive string operations
Range operationsBETWEEN, NOT BETWEEN
Collection operationsIN, NOT IN
Array operationsJSON array functions
Date operations
Numeric operations
Full-text search

Example usage:

if we want to query all data where one field is equal certain variable then we'll apply this to our controller:

await controller.addWhere([PredicateGroup(predicates: [Predicate.equals('field', 'value')])]);

two field with or:

await  controller.addWhere([
PredicateGroup(
  predicates: [
    Predicate.contains("filed1", "value1"),
    ],
  type: PredicateGroupType.or,
  subgroups: [
    PredicateGroup(
      predicates: [
        Predicate.contains("field2", "value2"),
      ],
    ),
    ],
)
]);

For More details on Predicate

Join Predicate

The Join predicate allows developers to create different types of SQL joins with custom conditions and optional table aliases. It supports multiple join types and handles complex joining scenarios.

Example usage:

final joinExample = Join(
  table: 'orders',
  condition: 'users.id = orders.user_id',
  type: JoinType.left,
  arguments: [1, 2, 3],
  alias: 'o'
);
await controller.addJoin([joinExample]),

OrderBy Predicate

The OrderBy predicate provides flexible sorting capabilities, including:

  • Table-specific sorting
  • Ascending/Descending order
  • Null value positioning

Example usage:

final nameOrder = OrderBy(
  'name', 
  OrderDirection.asc,
  nullsFirst: true
);

Custom Query Predicate

The CozyQueryBuilder predicate provides a flexible and intuitive interface for building SQL queries through method chaining. It supports sophisticated query construction with features like:

  • Selective field retrieval
  • Complex join operations
  • Advanced filtering with predicate groups
  • Aggregation and grouping
  • Sorting and pagination
  • Subquery integration
final complexUserQuery = CozyQueryBuilder<User>()
      // Select specific fields
      .select(['persistentModelID', 'name', 'email'])
      // Left join with user roles
      .join(Join(
        table: 'userRoles',
        condition: 'users.id = userRoles.user_id',
        type: JoinType.left
      ))
      // Complex filtering with nested conditions
      .where(PredicateGroup(
        type: PredicateGroupType.and,
        predicates: [
          Predicate.greaterThan('age', 18),
          Predicate.equals('status', 'active')
        ],
        subgroups: [
          PredicateGroup(
            type: PredicateGroupType.or,
            predicates: [
              Predicate.arrayContains("roles", "admin"),
              Predicate.arrayContains("roles", "manager"),
            ]
          )
        ]
      ))
      // Group by specific fields
      .groupBy(['status'])
      // Having clause for aggregated results
      .having(Having(
        condition: 'COUNT(*) > ?',
        arguments: [5]
      ))
      // Sorting and pagination
      .orderBy(OrderBy('name', OrderDirection.asc))
      .setLimit(10)
      .setOffset(20);

await controller.addCustomQuery(complexUserQuery);

Reset

The reset clean up all query conditions and refreshes the data with no filter.

await controller.reset();