Learning the Basics of Reports

by Barton Chittenden on Jul 11, 2017

Learn more about the basics of SQL, and some of the features of the Koha reports page.

As a quick quick refresher, a basic sql query is composed of SELECT, FROM and WHERE clauses, and looks like this:

SELECT
    borrowers.firstname,
    borrowers.surname,
    biblio.title,
    issues.date_due
FROM
    borrowers
    JOIN issues using (borrowernumber)
    JOIN items using (itemnumber)
    JOIN biblio using (biblionumber)
WHERE
    issues.date_due < CURRENT_DATE
    AND items.homebranch = 'MID'

The meaning of the query is fairly obvious — Show the Borrower’s first and last name, Biblio title, and due date for overdue items where the item’s homebranch is ‘MID’. The SELECT clause lists the columns to be displayed, and the WHERE clause limits the output to overdue items (issues.date_due < CURRENT_DATE) with homebranch at Midway (items.homebranch = 'MID')… what is somewhat less obvious is the meaning of the FROM clause.

The key here is the relationships between these tables — how do we link borrowers to biblio records?

In Koha, borrowers check out physical books, which we call items. The action of checking out the book creates information that we must store — how long the item is checked out, where is the item checked out from — and it also creates a relationship between the borrower and the item. All of this is stored in the ‘issues’ table, so called because, from a library’s point of view, an item is issued to a borrower. Another interesting relationship in Koha is that physical items, although they carry barcodes, are assigned home libraries and physical locations, can be borrowed or put on hold; do not carry bibliographic information. Instead, the bibliographic information is abstracted… it doesn’t matter if you have one physical copy of Harry Potter And The Cursed Child or five pallets full… you can represent this as a single biblio record with a single title.

Understanding the true nature of these relationship is the hardest part of database design. As a computer language, SQL is not difficult to learn — except for the use of INNER JOIN, 90% of queries are no more sophisticated than my example above… but it can take years to learn the ins and outs of the relationships between data.

There are a handful of entities in Koha that have identifiers called ‘keys’; once you know those keys, creating select statements such as the one above becomes like a ‘connect the dots’ puzzle:

borrowers             => borrowernumber
items                 => itemnumber
biblio                => biblionumber
branches              => branchcode 
                         (sometimes referred to as, branch, 
                         homebranch or holdingbranch )
(borrower) categories => categorycode
itemtypes             => itype, itemtype

So — once I can see that the borrowers table has a borrowernumber column, and the issues table has both a borrowernumber and an itemnumber column, it follows that I can join borrowers to items via issues.

Some relationships are more useful than others — both borrowers and items have some sort of branchcode column, but although these relationships are used sometimes in the internal workings of Koha during the checkout process, knowing that a patron and an item are both associated with a library is a bit like knowing that two families live on the same street.

One of the greatest parts of the SQL Reports Library is not using the individual reports cookie cutter fashion, but rather seeing how tables can be joined, and which relationships are really useful.