Reporting on Deleted Records

by Andrew Fuerste-Henry on Feb 7, 2019

There are a handful of questions I can safely assume I will get in every training I conduct. One of these is "What happens to my circulation data when an item gets deleted?" The short answer is, thankfully, that data is still there. The same is true for deleted borrower and biblio records. Koha holds onto this data, we just need to look for it a little bit differently.

Since deleting records doesn't really get rid of them, the primary purpose it serves is to hide them from the OPAC and the staff client. That means that when we want to see details about something we've deleted we can't just pull up its record like we would with a regular patron or item. Instead, we need to use reports.

Biblio, Borrowers, and Items

When introducing folks to reports, I usually point them first to the biblio, borrowers, and items tables. They're fairly straightforward and it's easy to think of useful questions to use as examples -- things like "How many patrons do I have in each of my ZIP codes?" or "Which books in adult non-fiction are more than 15 years old?" For this post, let's start with the example question "How many times has this book checked out?" That's pretty simple!

SELECT issues
FROM items
WHERE barcode=<<Enter barcode>>

If we save this as a report and run it, Koha will ask us for a barcode and then tell us how many times that item has been checked out.

Deletedbiblio, deletedborrowers, and deleteditems

When biblio records, borrowers, or items get deleted in Koha they're moved to the deletedbiblio, deletedborrowers, or deleteditems table, respectively. These tables are structured just like their non-deleted counterparts, which makes it simple to translate a report to look for deleted records. If we want to adjust our previous report to look at deleted items instead we just need to change the table in the FROM clause.

SELECT issues
FROM deleteditems
WHERE barcode=<<Enter barcode>>

Combining current and deleted records

Of course it's not ideal to have to keep two separate reports, one for current items and one for deleted items. With a slightly fancier query we can tell our report to look at both the items and deleteditems tables and give us whatever it finds in each. We can do that with a UNION.

SELECT issues
FROM items
WHERE barcode=<<enter barcode>>
 UNION ALL
SELECT issues
FROM deleteditems
WHERE barcode=<<Enter barcode>>

Essentially, we're displaying the results of two separate queries on top of each other -- all the current items that match our barcode and then all the deleted items the match our barcode. This option is quick and easy and works well for simple reports.

Deleted records referenced in other tables

Things get a little more complicated when we start dealing with other tables that refer back to records that might have been deleted. Here's a report that will ask for a date and list the titles of all the books with holds that were cancelled on that day.

SELECT title
FROM old_reserves
 LEFT JOIN biblio USING (biblionumber)
WHERE cancellationdate=<<enter date|date=>>

We can tell this report is only going to work for biblio records that haven't been deleted because it only looks at the biblio table. If we want to also find titles for deleted records we'll need to pull in the deletedbiblio table. We could use a UNION as we did before, but now that we're starting with the old_reserves table and joining to the biblio records we have a new option.

SELECT IFNULL(biblio.title,deletedbiblio.title)
FROM old_reserves
 LEFT JOIN biblio USING (biblionumber)
 LEFT JOIN deletedbiblio USING (biblionumber)
WHERE cancellationdate=<<enter date|date=>>

The IFNULL() function lets us name two fields. If the first field has a value it is returned. If the first field is null, the second field's value is returned instead. Because IFNULL() lets us accomplish our whole query with just one SELECT statement its a bit faster and less resource-intensive than using two SELECT statements connected with a UNION.

IFNULL() also makes it easier to look at more than one type of record at a time. For example, we could write a report that looks at how many holds patrons of each category place on items of each item type in the past year to get a sense of which materials are popular with which patrons. We know we want to start in the old_reserves table. To find patron categories we'll need the borrowers table and then deletedborrowers for anyone who's been deleted. Likewise, we'll need items and deleteditems for itemtypes. Here's what that report would look like.

SELECT IFNULL(borrowers.categorycode,deletedborrowers.categorycode) as category,
 IFNULL(items.itype,deleteditems.itype) as itemtype,
 COUNT(reserve_id)
FROM old_reserves
 LEFT JOIN borrowers USING (borrowernumber)
 LEFT JOIN deletedborrowers USING (borrowernumber)
 LEFT JOIN items USING (itemnumber)
 LEFT JOIN deleteditems USING (itemnumber)
WHERE reservedate >= date_sub(curdate(), interval 1 year)
GROUP BY category,itemtype

If we wanted to run this same report with UNION instead of IFNULL() we'd have to combine 4 separate reports for every combination of current and deleted borrowers and items.

Old_issues and old_reserves

So far I've been talking about deleted records, but these same functions can be used with issues and reserves. When a book is checked out that's recorded in the issues table and when it's returned that line moves to the old_issues table. Likewise, when a hold is placed it goes into the reserves table and when it's either completed or cancelled it moves into old_reserves. If you need to write a report looking at both current and old checkouts or holds, you can use either UNION or IFNULL() just like we did above.