Method Documentation: where Filter

Comprehensive Use of the where Filter

The where filter is a pivotal feature in the ORM's querying capabilities, applicable across various methods such as findUnique, findMany, findFirst, and others. It allows developers to specify detailed conditions for data retrieval, supporting a wide array of filtering options such as contains, startsWith, endsWith, among others. These options enable precise record matching based on patterns, ranges, lists, and exclusions, facilitating both broad and pinpoint queries within a dataset.

Filtering Options Explained

The where clause in the findMany method supports a variety of filtering options for precise querying. These options enable matching records based on patterns, ranges, lists, and exclusions.

String Matching

contains: Matches records where the field contains the specified substring.
startsWith: Matches records where the field starts with the specified prefix.
endsWith: Matches records where the field ends with the specified suffix.

List Operations

in: Matches records where the field value is within the specified array of values.
notIn: Matches records where the field value is not within the specified array of values.

Numerical Comparisons

lt (less than): Matches records where the field value is less than the specified value.
lte (less than or equal to): Matches records where the field value is less than or equal to the specified value.
gt (greater than): Matches records where the field value is greater than the specified value.
gte (greater than or equal to): Matches records where the field value is greater than or equal to the specified value.

Equality and Negation

equals: Matches records where the field value is equal to the specified value.
not: Matches records where the field value is not equal to the specified value. Can be used to negate any of the above conditions.

These filtering options can be combined in various ways to construct complex queries, allowing for refined searches tailored to specific requirements. For best practices and optimization tips when using these filters, refer to the advanced querying section.

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>";

Example usage: Basic Filtering Options 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

Illustrates how to use advanced filtering options such as contains, startsWith, endsWith, in, notIn, lt (less than), lte (less than or equal to), gt (greater than), gte (greater than or equal to), equals, and not for precise data querying.

  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>";

This example showcases filtering users by name patterns, age range, specific emails (including and excluding), and status, demonstrating the power of advanced filtering in data retrieval.

Understanding Logical Operators: AND, OR, NOT

Logical operators are powerful tools in constructing complex queries, allowing for the combination of multiple conditions. The where clause supports AND, OR, and NOT logical operators to refine search criteria.

AND

The AND operator allows for the combination of multiple conditions, all of which must be true for a record to match. It is useful for narrowing down results to those that meet all specified criteria.

OR

The OR operator matches records that fulfill at least one of the given conditions. It's ideal for expanding search results to include records that meet any of a set of criteria.

NOT

The NOT operator negates a condition, matching records that do not meet the specified criteria. It's used to exclude records from the results.

Example Usage: Basic AND Filtering Options

  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>";

Note: The following examples are based on the AND operator, which will produce the following SQL query: SELECT * FROM `_CategoryToPost` WHERE `A` = :A0 AND `B` = :B0 LIMIT 1. The array passed will be converted to WHERE `A` = :1 AND `B` = :47.

Explicit AND Filtering Options

  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>";

Example Usage: Advanced Filtering Options using AND Operator

  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>";

Example Usage: Advanced Filtering Options using OR Operator

  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>";

Example Usage: Advanced Filtering Options using NOT Operator

  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>";

Example Usage: Advanced Filtering Options using OR, AND, and NOT Operators

  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>";

This example showcases how to use AND, OR, and NOT logical operators to perform a complex query. It searches for users with an email containing "gmail.com" or users named "Reina" who are active, excluding those with a status of "suspended".