Skip to content

Laraframe Query Builder

Introduction

We recommend to use Repository pattern for Eloquent Model. It will help you in future once you decide to change your database from MYSQL to No-SQL database like MongoDB. The Repositories directory contains your Eloquent Model Repository and Interface. To make a repository and interface execute make:repository and make:interfaceArtisan command. This will create a Repository Class and Interface inside your Repositories directory. After creating Repository and Interface you need to bind your Repository with Interface in RepositoryServiceProvider.

Every Repository must extend `BaseRepository` class and must implement an Interface. And inside your controller or service you should use Interface for calling Eloquent Model. This will hide your Eloquent Model and give us some ready made query.

List of Query Builder Functions

getAll():

Get all rows.

parameters: relations:array|string(optional), orders:array(optional)

return: Collection

Example:

    $userModel = app(UserInterface::class);
    $users = $userModel->getAll('userInfo', ['id'=>'desc']);

getByConditions():

Get all rows by conditions.

parameters: conditions:array, relations:array|string(optional), orders:array(optional)

return: Collection

Example:

    $userModel = app(UserInterface::class);
    $conditions = ['is_active' => 1, 'is_email_verified' => 1];
    $users = $userModel->getByConditions($conditions, 'userInfo', ['id'=>'desc']);

findOrFailById():

Find a row by table id.

parameters: id:int, relations:array|string(optional)

return: Object|404

Example:

    $userModel = app(UserInterface::class);
    $user = $userModel->findOrFailById(1, 'userInfo');

findOrFailByConditions():

Find a row by conditions.

parameters: conditions:array, relations:array|string(optional)

return: Object|404

Example:

    $userModel = app(UserInterface::class);
    $conditions = ['is_active' => 1, 'email' => 'name@example.com'];
    $user = $userModel->findOrFailByConditions($conditions, 'userInfo');

getFirstByConditions():]

Get a row by table id.

parameters: conditions:array, relations:array|string(optional)

return: Object|Null

Example:

    $userModel = app(UserInterface::class);
    $user = $userModel->getFirstByConditions(1, 'userInfo');

getFirstById():

Get a row by conditions.

parameters: id:int, relations:array|string(optional)

return: Object|Null

Example:

    $userModel = app(UserInterface::class);
    $user = $userModel->getFirstById(1, 'userInfo');

create():

Create a new row into table

parameters: attributes:array

return: Object

Example:

    $userModel = app(UserInterface::class);
    $attributes = ['first_name' => 'John', 'last_name' => 'Doe' 'email' => 'name@example.com'];
    $user = $userModel->findOrFailByConditions($attributes);

insert():

Insert multiple rows at a time into table

parameters: attributes:array

return: boolean

Example:

    $userModel = app(UserInterface::class);
    $attributes = [
        ['first_name' => 'John', 'last_name' => 'Doe' 'email' => 'john@example.com'],
        ['first_name' => 'Robin', 'last_name' => 'Milford' 'email' => 'robin@example.com'],
    ];
    $userModel->findOrFailByConditions($attributes);

update():

Update a row by id or other column name. By default it is updated by the table id. If you want update by another column just pass your column name as 3rd parameter

parameters: attributes:array, id|Object, attribute(optional)

return: Updated Object

Example:

    $userModel = app(UserInterface::class);
    $attributes = ['first_name' => 'John', 'is_email_verified' => 1];
    $user = $userModel->update($attributes, 1);

updateByConditions():

Update a row or multiple rows with same conditions

parameters: attributes:array, conditions:array, isSingleRowUpdate:boolean(optional)

return: Updated Object|Boolean

Example:

    $userModel = app(UserInterface::class);
    $attributes = ['is_email_verified' => 1];
    $conditions = ['is_active' => 1 , 'is_email_verified' => 0]
    $user = $userModel->updateByConditions($attributes, $conditions, false);

bulkUpdate():

Update multiple rows at a times by different conditions for each row.

parameters: attributes:array

return: updated row count

Example:

    $userModel = app(UserInterface::class);
    $parameters = [
    [
        'conditions' => ['is_active' => 1, 'is_email_verified' => 1],
        'fields' => [
            'first_name' => 'John',
            'last_name' => 'Doe'
        ]
    ],
    [
        'conditions' => ['email' => 'robin@example.com', 'is_active' => 1],
        'fields' => [
            'is_financial_suspended' => 1,
    ];
    $user = $userModel->bulkUpdate($parameters);

Fields key can update different ways. Example Below:

'fields' => [
    'column_name1' => ['increment', 10],
    'column_name2 => ['decrement', 5],
    'column_name3' => ['raw', 'column_name1 - column_name2'],
    'column_name4' => 'value',
];

deleteById():

Delete a row by id

parameters: id:int

return: boolean

Example:

    $userModel = app(UserInterface::class);
    $userModel->deleteById(1);

deleteByConditions():

Delete a row by conditions

parameters: conditions:array

return: boolean

Example:

    $userModel = app(UserInterface::class);
    $conditions = ['first_name' => 'John', 'is_email_verified' => 1];
    $userModel->deleteByConditions($conditions);

toggleStatus():

Change status of a row by its id. By default it changes is_active column. If you want to change different column then pass the column name as 2nd parameter.

parameters: id:int|Object, attribute(optional)

return: Updated Object

Example:

    $userModel = app(UserInterface::class);
    $user = $userModel->toggleStatus(1, 'is_financial_suspended');

toggleStatusByConditions():

Change status of a row by conditions. By default it changes is_active column. If you want to change different column then pass the column name as 2nd parameter.

parameters: conditions:array, attribute(optional), isSingleRowUpdate(optional)

return: Updated Object

Example:

    $userModel = app(UserInterface::class);
    $conditions = ['first_name' => 'John', 'is_email_verified' => 1];
    $user = $userModel->toggleStatusByConditions($conditions);

countByCondition():

Get the number of row by conditions.

parameters: conditions:array

return: number of rows

Example:

    $userModel = app(UserInterface::class);
    $conditions = ['is_email_verified' => 1];
    $count = $userModel->countByCondition($conditions);

filters():

Get filtered rows by conditions, search keywords,date range with ordering by its column.

parameters: searchFields:array, orderFields:array(optional), conditions:array(optional), selectData:array(optional), joinArray:array(optional), groupBy:array(optional), paginationKey:string(optional), dateField:string(optional)

return: Collections

Example:

    $userModel = app(UserInterface::class);
    $searchFields = [
            ['username', __('Username')],
            ['email', __('Email')],
            ['first_name', __('First Name')],
            ['last_name', __('Last Name')],
        ];
        $orderFields = [
            ['first_name', __('First Name')],
            ['users.id', __('Serial')],
            ['last_name', __('Last Name')],
            ['email', __('Email')],
            ['username', __('Username')],
            ['users.created_at', __('Registered Date')],
        ];
        $joinArray = [
            ['user_role_managements', 'user_role_managements.id', '=', 'users.user_role_management_id'],
            ['user_infos', 'user_infos.user_id', '=', 'users.id'],
        ];
        $select = [
            'users.*', 'role_name', 'first_name', 'last_name'
        ];

        $query = $userModel->filters($searchFields, $orderFields, null, $select, $joinArray);

paginate():

Get paginated rows by conditions. By default it take 10 items per page. If you want to change the item number pass the number as 3rd parameter.

parameters: conditions:array, columns:array(optional), perPage:int(optional), paginationKey:string(optional)

return: Paginated Collections

Example:

    $userModel = app(UserInterface::class);

        $conditions = ['first_name' => 'John', 'is_email_verified' => 1];
        $select = ['first_name', 'last_name', 'email'];

        $query = $userModel->paginate($conditions, $select);

simplePaginate():

Get paginated rows by conditions. By default it take 10 items per page. If you want to change the item number pass the number as 3rd parameter.

parameters: conditions:array, columns:array(optional), perPage:int(optional), paginationKey:string(optional)

return: Paginated Collections with minimal paginate data

Example:

    $userModel = app(UserInterface::class);

        $conditions = ['first_name' => 'John', 'is_email_verified' => 1];
        $select = ['first_name', 'last_name', 'email'];

        $query = $userModel->simplePaginate($conditions, $select);

paginateWithFilters():

Get paginated rows by conditions, search keywords,date range with ordering by its column.

parameters: searchFields:array, orderFields:array(optional), conditions:array(optional), selectData:array(optional), joinArray:array(optional), groupBy:array(optional), paginationKey:string(optional), dateField:string(optional)

return: Collections

Example:

    $userModel = app(UserInterface::class);
    $searchFields = [
            ['username', __('Username')],
            ['email', __('Email')],
            ['first_name', __('First Name')],
            ['last_name', __('Last Name')],
        ];
        $orderFields = [
            ['first_name', __('First Name')],
            ['users.id', __('Serial')],
            ['last_name', __('Last Name')],
            ['email', __('Email')],
            ['username', __('Username')],
            ['users.created_at', __('Registered Date')],
        ];
        $joinArray = [
            ['user_role_managements', 'user_role_managements.id', '=', 'users.user_role_management_id'],
            ['user_infos', 'user_infos.user_id', '=', 'users.id'],
        ];
        $select = [
            'users.*', 'role_name', 'first_name', 'last_name'
        ];

        $query = $userModel->paginateWithFilters($searchFields, $orderFields, null, $select, $joinArray);

Where Conditions Format

In the project you need pass where condition as an array. Here are some where conditions example.

Format

['column_name', 'operator', 'value', 'logical_operator']

column_name: table column name

operator: operator sign

value: int|string|array

logical_operator: and|or (by default and)

List of Operators

'=', '<', '>', '<=', '>=', '<>', '!=', '<=>', 'in','notin', 'between', 'notbetween' 'like', 'like binary', 'not like', 'ilike', '&', '|', '^', '<<', '>>', 'rlike', 'regexp', 'not regexp', '~', '~*', '!~', '!~*', 'similar to', 'not similar to', 'not ilike', '~~*', '!~~*'

Example AND:

$condtions = [
    'column_name1' => value1,
    'column_name2' => value2
];
//select * from table_name where column_name1 = 'value1' and column_name2 = 'value2';

Example OR:

$condtions = [
    'column_name1' => value1,
    ['column_name2', '=' value2, 'or']
];
//select * from table_name where column_name1 = 'value1' or column_name2 = 'value2';

Example OR Extended:

$condtions = [
    [
        'column_name1' => value1
        ['column_name2', '=' value2, 'or']
    ]
    [
        ['column_name3', '>=' value3],
        ['column_name4', '!=' value4],
        'or'
    ]
];
//select * from table_name where (column_name1 = 'value1' or column_name2 = 'value2') or (column_name3 >= 'value3' and column_name4 != 'value4');

Example WhereIn:

$condtions = [
    'column_name1' => value1,
    ['column_name2', 'in' [1,2,3]]
];
//select * from table_name where column_name1 = 'value1' and column_name2 in (1,2,3);

Example WhereNotIn:

$condtions = [
    'column_name1' => value1,
    ['column_name2', 'notin' [1,2,3]]
];
//select * from table_name where column_name1 = 'value1' and column_name2 not in (1,2,3);

Example WhereBetween:

$condtions = [
    'column_name1' => value1,
    ['column_name2', 'between' [1,3]]
];
//select * from table_name where column_name1 = 'value1' and column_name2 between 1 and 3;

Example WhereNotBetween:

$condtions = [
    'column_name1' => value1,
    ['column_name2', 'notbetween' [1,3]]
];
//select * from table_name where column_name1 = 'value1' and column_name2 not between 1 and 3;