Aggregating Data Counts and Results in a Single Query
When building an admin dashboard or generating reports, it is common to need three distinct pieces of information from a single table: the total number of records matching a filter, the full dataset for pagination or display, and a specific subset of recent entries. In standard SQL, you might attempt to solve this with UNION statements or window functions like ROW_NUMBER(). However, in CodeIgniter, trying to combine these logic paths into one database call is often inefficient or impossible depending on the version and driver.
The most robust approach is not to chain multiple queries together blindly, but to leverage the database's native capabilities to return a single result set that contains everything you need. By using an aggregate function alongside window functions, you can retrieve the total count and the latest records in one go, avoiding unnecessary round trips to the database server.
Using Window Functions for Efficiency
If your database driver supports it (MySQL 8.0+, PostgreSQL, SQL Server), the most elegant solution is to execute a single query that calculates the total count while simultaneously ordering rows by insertion time. You can then split this result in your application logic if you strictly need the full dataset and the recent subset separately.
Here is how you can modify your model method to achieve this:
public function getreq()
{
// Select necessary columns, total count as a separate column using aggregation
// Use ROW_NUMBER() partitioned by status to handle the 'latest' requirement
$this->db->select('id, fullname, created_at');
// Calculate total rows matching the condition in the same query
// Note: You typically cannot return both aggregate and non-aggregate
// columns in a standard SELECT without subqueries or window functions.
// A safer single-query approach for the 'latest' subset uses LIMIT/OFFSET logic,
// but for total count + all data + latest, a UNION ALL is often cleaner.
$this->db->where('status', 'pending');
// Option 1: Return full list with metadata if your DB supports window functions well
// This query returns the full list. You calculate count separately in PHP or use MySQL's COUNT() trick.
$query = $this->db->get('instanthire');
// To get the 3 latest, you would typically run a second query with LIMIT 3 OFFSET 0
// UNLESS you use a subquery to wrap the main result.
return $query->result();
}
Actually, for the specific requirement of "Total Count + All Data + 3 Latest" in one go, a UNION ALL approach is often more performant and readable than complex window functions, especially if you don't need to expose the count as a column in the main table rows.
The UNION ALL Strategy
In CodeIgniter, you can execute two queries seamlessly within one method call. This avoids loading the entire dataset into memory just to filter the top 3, while still giving you the count immediately.
public function getreq()
{
$db = $this->db;
// Query 1: Get total count
$countQuery = $db->query('SELECT COUNT(*) as total FROM instanthire WHERE status = "pending"');
$totalRows = $countQuery->row('total');
// Query 2: Get all data and the 3 latest separately?
// Actually, fetching ALL rows AND the 3 latest usually implies you want the full list.
// If you want to avoid N+1 problems or excessive memory usage for huge tables:
$data['full_list'] = $db->where('status', 'pending')->get('instanthire')->result();
// To get the 3 latest specifically, we use LIMIT and ORDER BY
$latestQuery = $db->order_by('created_at', 'DESC')
->limit(3)
->where('status', 'pending')
->get('instanthire');
$data['recent_3'] = $latestQuery->result();
// Pass both to the view or return a structured array
$data['total_count'] = $totalRows;
// If you strictly need one object back with all three properties:
// You might need to construct an associative array in the model.
return [
'total' => $totalRows,
'all_rows' => $data['full_list'],
'recent_3' => $data['recent_3']
];
}
Handling Large Datasets
Fetching entire data from a table can be problematic if the instanthire table grows large. Loading thousands of rows into memory just to display them in a view is a performance bottleneck. If your goal is to paginate the full list while still showing the total count and recent items, you should separate the concerns:
- Count Query: Always run a lightweight
COUNT(*)query first. This determines pagination limits or displays the badge count. - Pagination Query: Use CodeIgniter's limit/offset for the main table data.
- Recent Query: Run a separate
LIMIT 3query ordered bycreated_at.
This separation ensures your application remains responsive even as the database scales. While it involves two or three queries, the cost is negligible compared to trying to force a single complex query that returns millions of rows into a standard result set.
Conclusion on Implementation
While the source material suggests applying parameters separately for each case, combining the logic in the model provides cleaner separation of concerns. By calculating the count via COUNT(*) and fetching the specific subsets using ORDER BY and LIMIT, you maintain code clarity without sacrificing performance. If you are looking for a resource on advanced database optimization patterns within PHP frameworks, LaravelCompany offers insights into similar architectural decisions, though CodeIgniter handles these scenarios well through its standard Active Record methods.
