Keeping the Report Logic in the Database

Keeping the Report Logic in the Database

Imagine that we have a very simple non-relational table for apartment bookings as described below.

CREATE TABLE `apartments_bookings` (
 `apartments_bookings_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `apartment_name` char(255) NOT NULL,
 `date_booked` datetime NOT NULL,
 `booked` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`apartments_bookings_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

When running and displaying reports, often in grid format, it is common to see this kind of language construct

Select from database

SELECT apartment_name FROM apartments_bookings 
WHERE date_booked BETWEEN '2017-06-05' AND '2017-06-12' AND booked = '0';

Summarise the number of bookings per apartment for the date range in the code

<?php $apartments_bookings = $db->query($sql_above)->fetchAll();
$apartments = [];
foreach ($apartments_bookings as $apartment_bookings) { 
    $apartments[$apartment_bookings['apartment_name']] = 
        $apartments[$apartment_bookings['apartment_name']] + 1; 
}
rsort($apartments); // Sort by number of bookings descending
?>

This gives us a list of total bookings for each apartment. We can sort this list and use it to produce a report thus:

<table>
  <tbody>
  <?php foreach ($apartments as $key => $value): ?>  
    <tr>    
      <td><?php echo $key; ?></td>    
      <td><?php echo $value; ?></td>
    </tr>
    <?php endif; ?>
  </tbody>
</table>

What is wrong with this?

1. If the report is long it cannot be split into ordered pages using the SQL LIMIT since we do not know the number of bookings until we have compiled the results
2. Connected with point 1 we can only sort by number of bookings if we retrieve all the results into PHP and then display the first n records
3. We cannot search for example for 8 bookings or more or less than 8 bookings without getting the entire resultset and the adding a further to condition to remove records that do not conform to the search criteria
4. All the above can become very expensive in terms of code, loops and complexity

What is the solution?

Group the records and use COUNT()

SELECT apartment_name, COUNT(*) as no_apartments FROM apartments_bookings 
WHERE date_booked BETWEEN '2017-06-05' AND '2017-06-12' AND booked = '0'
GROUP BY apartment_name 
ORDER BY no_apartments DESC 
LIMIT 10
OFFSET 0;

Then all we have to do is:

$apartments_bookings = $db->query($sql_above)->fetchAll();

And produce our HTML table as before. We can then search by a specific criteria based on the number of bookings and the

HAVING

clause, which is used instead of the

WHERE

in aggregate functions where the value is calculated, in this case using

COUNT()

.

SELECT apartment_name, COUNT(*) as no_apartments FROM apartments_bookings 
WHERE date_booked BETWEEN '2017-06-05' AND '2017-06-12' AND booked = '0'
GROUP BY apartment_name 
HAVING no_apartments > 8
ORDER BY no_apartments DESC 
LIMIT 10
OFFSET 10;

Notice also that we have moved to page 2 of the results by stipulating

OFFSET 10

We would wrap this SQL in a method and use a database abstraction layer to retrieve the results. It is then possible to call the class and method from out PHP code and assign the results to a template without needless repetion of looping PHP code