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
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
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.