Queries with Filtering

This guide is for DeskFlow list endpoints — filtering tasks by status, scoping to a project_id, and sorting for alex@northwind.studio’s board on port 8000. You will move from simple where() arrays to the full Builder after filter().

What you will learn#

• Use fluent where() and Medoo-style array conditions on Northwind tables
• Chain orderBy, limit, and startAt on a Builder
• Build nested AND/OR clauses with the Where builder

How it works#

table('tasks')
  → filter([...])     enters Builder mode
  → where / orderBy / limit / startAt
  → all() | count() | first()   (terminal — executes SQL)

Introduction

Sometimes, you need more than just fetching data from the database. You may need to filter the data based on certain conditions. This is where filtering comes in. Filtering allows you to specify conditions that the data must meet before it is returned.

In this section, we will look at how to filter data when querying the database using Porm.

Fluent where() (ORM-style)#

Porm accepts a Laravel-inspired where(column, operator, value) syntax on table(), filter() builders, and joins. Array conditions still work — both styles can be mixed.

Equality#

table('team_members')
    ->filter()
    ->where('email', 'alex@northwind.studio')   // WHERE email = 'alex@northwind.studio'
    ->all();

// symbolic operator (3-arg)
table('tasks')->filter()->where('priority', '>', 2)->all();

Named operators#

Convenience methods#

table('tasks')->filter()
    ->whereEquals('status', 'open')
    ->whereStartsWith('title', 'Desk')
    ->whereIncludes('title', 'Flow')
    ->whereNotEqual('status', 'archived')
    ->whereIn('project_id', [1, 2, 3])
    ->whereBetween('priority', 1, 3)
    ->all();

orWhere()#

table('team_members')->filter()
    ->where('email', 'alex@northwind.studio')
    ->orWhere('name', 'Alex Chen')
    ->all();
// (email = 'alex@northwind.studio' OR name = 'Alex Chen')

For multiple values on the same column, prefer whereIn('status', ['open', 'in_progress']) over chaining orWhere on one column (PHP arrays cannot repeat keys).

Table-level chaining#

table('tasks')->where('status', 'open')->get();

Array where (Medoo-style)#

This method can be used to filter data based on a single ‘AND’ condition. This method can be used with all the other methods in the query builder.

$tasks = table('tasks')->where(['project_id' => 1])->get();

You can chain as many where methods as you want to filter the data.

$tasks = table('tasks')
    ->where(['project_id' => 1])
    ->where(['status' => 'open'])
    ->all();

All conditions in the where method are joined by an ‘AND’ operator.

filter#

The filter method can be used to filter data based on multiple conditions. The conditions are joined by an ‘AND’ operator.

$tasks = table('tasks')
    ->filter(['project_id' => 1, 'status' => 'open'])
    ->all();

This might look familiar, however, the filter method ports us to the underlying QueryBuilder class, which allows us to chain more complex conditions and avails more methods.

Using filter, you can access methods such as orderBy, group, limit, match, having, first, get, all and many more.

$tasks = table('tasks')
    ->filter(['project_id' => 1, 'status' => 'open'])
    ->orderBy(['priority' => 'DESC'])
    ->limit(10)
    ->startAt(5)
    ->all();

Where Builder#

Observing all our examples provided so far, we can notice that where conditions are passed as an array. With this builder, you can build a more complex query which will internally be converted to an array as shown in the examples above.

Bulding#

To start building, call builder() on Pionia\Porm\Database\Builders\Where:

use Pionia\Porm\Database\Builders\Where;

$clause = Where::builder();

From there, you can chain methods to build your query.

The builder MUST finally call the build method to actually build the query.

$clause = Where::builder()
// add here both AND and OR conditions
    ->build();

You can then pass this clause to any method that accepts conditions.

$clause = Where::builder()
// add here both AND and OR conditions
    ->build();

$tasks = table('tasks')
    ->where($clause) // with where method
    ->all();

$tasks = table('tasks')
    ->filter($clause) // with filter method
    ->all();

$tasks = table('tasks')
    ->all($clause); // with all method

$tasks = table('tasks')
    ->first($clause); // with first method

$tasks = table('tasks')
    ->filter($clause) // with filter method that also defines more complex queries
    ->orderBy(['priority' => 'DESC'])
    ->limit(10)
    ->startAt(5)
    ->all();

AND Conditions#

To add an ‘AND’ condition, you can use the and method.

$clause = Where::builder()
    ->and(['project_id' => 1])
    ->and(['status' => 'open'])
    ->build();

You can chain as many and methods as you want to add more conditions.

OR Conditions#

To add an ‘OR’ condition, you can use the or method.

$clause = Where::builder()
    ->or(['status' => 'open'])
    ->or(['status' => 'in_progress'])
    ->build();

You can chain as many or methods as you want to add more conditions.

AND and OR#

You can mix ‘AND’ and ‘OR’ conditions to build more complex queries.

$clause = Where::builder()
    ->and(['project_id' => 1])
    ->or(['status' => 'blocked'])
    ->build();

You can chain as many and and or methods as you want to build more complex queries.

Complex Relativity#

You can also build more complex queries by nesting conditions.

$clause = Where::builder()
    ->and(['project_id' => 1])
    ->or(
        Where::builder()
            ->and(['status' => 'open'])
            ->or(['status' => 'in_progress'])
            ->and(['assignee_id' => 2])
            ->build()
    )
    ->build();

    // where project_id = 1 OR (status = 'open' OR status = 'in_progress' AND assignee_id = 2)

You can nest as many conditions as you want to build more complex queries. This is where Porm shines!

Common mistakes#

• Calling startAt() without limit() — DeskFlow paginated lists need both or the Builder throws.
• Repeating the same array key in OR conditions — use whereIn('status', [...]) instead of duplicate keys.
• Mixing unqualified column names after joins — prefix with t. when filtering joined task lists.
• Filtering on client fields without validation — whitelist sort columns in TaskService before passing to orderBy().

What’s next#