Where Filter

Comprehensive Use of the where Filter

The where filter is one of the most powerful features for specifying detailed conditions when retrieving records. It is supported in methods such as findUnique, findMany, findFirst, and more. It enables developers to build precise queries using pattern matching, range comparisons, lists, negations, and relational filters.

Filtering Options Explained

The following options are available within the where filter:

String Matching

• contains — Field contains a substring.
• startsWith — Field begins with the given prefix.
• endsWith — Field ends with the given suffix.

List Operations

• in — Field value is inside the provided list.
• notIn — Field value is not inside the provided list.

Numerical Comparisons

• lt — Less than.
• lte — Less than or equal to.
• gt — Greater than.
• gte — Greater than or equal to.

Equality and Negation

• equals — Value must match exactly.
• not — Negates any condition.

Example Usage: Basic Filtering Options

Using lt and gt

use Lib\Prisma\Classes\Prisma;

$prisma = Prisma::getInstance();
$users = $prisma->user->findMany([
    'where' => [
        'age' => [
            'lt' => 30,
            'gt' => 20
        ]
    ]
]);

echo "<pre>";
echo "User found: " . print_r($users, true);
echo "</pre>";

    

Using notIn

use Lib\Prisma\Classes\Prisma;

$prisma = Prisma::getInstance();
$users = $prisma->user->findMany([
    'where' => [
        'name' => [
            'notIn' => ['John Doe', 'John Doe4', 'Mario Rossi'],
        ]
    ]
]);

echo "<pre>";
echo "User found: " . print_r($users, true);
echo "</pre>";

    

Example Usage: Advanced Filtering Options

use Lib\Prisma\Classes\Prisma;

$prisma = Prisma::getInstance();
$users = $prisma->user->findMany([
    'where' => [
        'name' => [
            'contains' => 'Doe',
            'startsWith' => 'J',
            'endsWith' => 'e'
        ],
        'age' => [
            'gt' => 25,
            'lt' => 35
        ],
        'email' => [
            'in' => ['example1@email.com', 'example2@email.com'],
            'notIn' => ['admin@email.com']
        ],
        'status' => [
            'equals' => 'active',
            'not' => 'suspended'
        ]
    ],
    'orderBy' => ['name' => 'asc'],
    'take' => 10,
]);

echo "<pre>";
echo "Users found: " . print_r($users, true);
echo "</pre>";

    

Understanding Logical Operators: AND, OR, NOT

Logical operators allow combining multiple conditions for more powerful and flexible queries.

Basic AND Filtering

use Lib\Prisma\Classes\Prisma;

$prisma = Prisma::getInstance();
$findFirstPost = $prisma->categoryToPost->findFirst([
    'where' => [
        'A' => 1,
        'B' => 47
    ],
]);

echo "<pre>";
echo "Post found: " . print_r($findFirstPost, true);
echo "</pre>";

    

Explicit AND Filtering

use Lib\Prisma\Classes\Prisma;

$prisma = Prisma::getInstance();
$users = $prisma->user->findMany([
    'where' => [
        'email' => [
            'contains' => 'gmail.com'
        ],
        'AND' => [
            'name' => [
                'contains' => 'Reyn'
            ]
        ]
    ]
]);

echo "<pre>";
echo "User found: " . print_r($users, true);
echo "</pre>";

    

Advanced AND Filtering

use Lib\Prisma\Classes\Prisma;

$prisma = Prisma::getInstance();
$users = $prisma->user->findMany([
    'where' => [
        'AND' => [
            'email' => [
                'contains' => 'gmail.com'
            ],
            'roleId' => 3
        ]
    ]
]);

echo "<pre>";
echo "User found: " . print_r($users, true);
echo "</pre>";

    

Advanced OR Filtering

use Lib\Prisma\Classes\Prisma;

$prisma = Prisma::getInstance();
$users = $prisma->user->findMany([
    'where' => [
        'OR' => [
            [
                'email' => [
                    'contains' => 'example.com'
                ]
            ],
            [
                'email' => [
                    'contains' => 'gmail.com'
                ]
            ]
        ]
    ]
]);

echo "<pre>";
echo "User found: " . print_r($users, true);
echo "</pre>";

    

Advanced NOT Filtering

use Lib\Prisma\Classes\Prisma;

$prisma = Prisma::getInstance();
$users = $prisma->user->findMany([
    'where' => [
        'email' => [
            'contains' => 'gmail.com'
        ],
        'NOT' => [
            'roleId' => 1
        ]
    ]
]);

echo "<pre>";
echo "User found: " . print_r($users, true);
echo "</pre>";

    

Complex Query using OR, AND, and NOT

use Lib\Prisma\Classes\Prisma;

$prisma = Prisma::getInstance();
$users = $prisma->user->findMany([
    'where' => [
        'OR' => [
            [
                'email' => [
                    'contains' => 'gmail.com'
                ]
            ],
            [
                'AND' => [
                    [
                        'name' => [
                            'contains' => 'Reina'
                        ]
                    ],
                    [
                        'isActive' => true
                    ]
                ]
            ]
        ],
        'NOT' => [
            'status' => [
                'equals' => 'suspended'
            ]
        ]
    ]
]);

echo "<pre>";
echo "Users found: " . print_r($users, true);
echo "</pre>";

    

Filtering by Related Models using some

The some validator filters records based on related entries. This is especially useful for many-to-many or one-to-many relations, matching records that have at least one related item meeting the condition.

use Lib\Prisma\Classes\Prisma;

$prisma = Prisma::getInstance();

$icons = $prisma->icon->findMany([
    'where' => [
        'categories' => [
            'some' => [
                'name' => [
                    'equals' => $categoryName
                ]
            ]
        ]
    ],
    'include' => [
        'categories' => true
    ],
    'orderBy' => [
        'name' => 'asc'
    ]
]);

echo "<pre>";
echo "Icons found: " . print_r($icons, true);
echo "</pre>";