setCustomQuery

setCustomQuery(string $selectQuery[, string $countQuery])

Set a custom select query for datagrid data retrieval. You can use setCustomQuery to provide your own SQL query for the listing/datagrid operation. This is useful when you need to have complex queries that involve joins, subqueries, or specific filtering.

⚠️ There are some limitations when using setCustomQuery:

Currently, we are not supporting filtering and sorting. We may add support for these features in future releases.

Pagination is only supported when you provide the second parameter $countQuery.

You always need to return the primary key of the table in the select query for proper functioning of the CRUD operations.

The filtering on the custom query will not filter the results of the CRUD operations (edit, delete, etc). In case you need to filter the results of the CRUD operations you will either need to use $crud->where() or have custom validation on "before" callbacks for all the operations.

Pagination is only supported when you provide the second parameter $countQuery. If you do not provide it then all the data will be fetched in the datagrid without pagination.

Example Usage

$selectQuery = "SELECT users.id, users.name, roles.role_name 
                FROM users 
                JOIN roles ON users.role_id = roles.id 
                WHERE users.active = 1";
$countQuery = "SELECT COUNT(*) as num 
                FROM users 
                JOIN roles ON users.role_id = roles.id 
                WHERE users.active = 1";
$crud->setCustomQuery($selectQuery, $countQuery);

or without pagination:

$selectQuery = "SELECT users.id, users.name, roles.role_name 
                FROM users 
                JOIN roles ON users.role_id = roles.id 
                WHERE users.active = 1 AND roles.role_name = 'Manager'";
$crud->setCustomQuery($selectQuery);
$crud->unsetPagination();

Full Example

You can find a full working example below:

$selectQuery = "
    SELECT
        f.film_id,
        f.title,
        f.release_year,
        GROUP_CONCAT(a.fullname SEPARATOR ', ') AS actors
    FROM film f
    JOIN film_actor fa ON f.film_id = fa.film_id
    JOIN actor a ON fa.actor_id = a.actor_id
    WHERE f.rating = 'PG'
    GROUP BY f.film_id, f.title, f.release_year
";
$countQuery = "
    SELECT COUNT(DISTINCT f.film_id) AS num
    FROM film f
    JOIN film_actor fa ON f.film_id = fa.film_id
    JOIN actor a ON fa.actor_id = a.actor_id
    WHERE f.rating = 'PG'
";
$crud->setCustomQuery($selectQuery, $countQuery);

$crud->setSubject('Film', 'PG Rating Films');
$crud->columns(['title', 'release_year', 'actors']);
$crud->setTable('film'); // Table name is still required for CRUD operations

// Some additional configurations which will make more sense for this example
$crud->unsetAdd();
$crud->readOnlyEditFields(['rating']);
// Using where to filter CRUD operations so users with no access to not be able to
// edit/delete films that are not 'PG'
$crud->where(['rating' => 'PG']);
$crud->setRelationNtoN(
    'actors', 'film_actor',
    'actor', 'film_id',
    'actor_id', 'fullname',
    null, null, 'priority'
);
$crud->setRelationNtoN(
    'categories', 'film_category',
    'category', 'film_id',
    'category_id', 'name',
    null, null, 'priority'
);

// Last changes, since we are using a custom query, we need to disable filtering and ordering
// and do some changes on the default field types for columns:
$crud->fieldTypeColumn('title', 'varchar');
$crud->fieldTypeColumn('release_year', 'varchar');
$crud->fieldTypeColumn('actors', 'varchar');
$crud->unsetFilters();
$crud->unsetSearchColumns(['title', 'release_year', 'actors']);
$crud->unsetSortingColumns(['title', 'release_year', 'actors']);
$crud->unsetDeleteMultiple();

$output = $crud->render();

You can see the results for the above example at the below demo: