[Koha] Koha 18.11 - SQL report to select ISBNs by Subject
Craig Butosi
cbutosi at gmail.com
Thu Mar 21 05:46:50 NZDT 2019
Hi everyone,
Just a follow up for the community :)
Thanks to the savvy brain trust that is the Koha list, I am now
successfully using the following report to query ISBNs (+biblionumber,
+title as required by the coverflow plugin) based on subject:
SELECT DISTINCT
biblioitems.isbn,biblio.title,biblio.biblionumber, c.imagenumber AS
localcover,
ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]')
AS Subject
FROM items
LEFT JOIN biblioitems ON (items.biblioitemnumber =
biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
LEFT JOIN biblio_metadata ON (biblio_metadata.biblionumber =
biblio.biblionumber)
LEFT JOIN biblioimages c ON (items.biblionumber=c.biblionumber)
WHERE items.dateaccessioned BETWEEN "2000-01-01" AND CURDATE()
AND
ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]')
LIKE "%Art%"
AND (isbn IS NOT NULL OR isbn !='')
This report also includes any local cover that has been uploaded to a bib
record. Could be useful in cases where libraries have not created a custom
fallback cover for records without a cover. You'll also notice that the
choice of subject is hard-coded into the report on the second last line. In
this case, I chose %Art% to capture any book that has a subject with the
word 'Art' in it. Final result: http://library.craigbutosi.ca/
Again, thanks to everyone.
Craig Butosi, MA, MLIS, B Mus (Hons)
Library: library.craigbutosi.ca
On Tue, Mar 19, 2019 at 11:49 AM Craig Butosi <cbutosi at gmail.com> wrote:
> Hi all,
>
> Holger, it looks your syntax works. I've modified your report to reflect a
> few other things. I managed to get the following to work successfully:
>
> SELECT * FROM
> (
> SELECT
> biblioitems.isbn,
> items.barcode,
> items.itemcallnumber,
> biblio.title,
> biblio.author,
>
> ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]')
> AS Subject
> FROM items
> LEFT JOIN biblioitems ON (items.biblioitemnumber =
> biblioitems.biblioitemnumber)
> LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
> LEFT JOIN biblio_metadata ON (biblio_metadata.biblionumber =
> biblio.biblionumber)
> WHERE
> items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>>
> AND <<and (yyyy-mm-dd)|date>>
> ) AS t
> WHERE
> Subject LIKE concat('%',<<Subject>>,'%') AND isbn IS NOT NULL AND isbn
> != ''
> LIMIT 20
>
> Thanks to all of you for your assistance.
>
>
> Craig Butosi, MA, MLIS, B Mus (Hons)
> Library: library.craigbutosi.ca
>
>
> On Tue, Mar 19, 2019 at 11:10 AM Holger Meissner <
> Holger.Meissner at hs-gesundheit.de> wrote:
>
>> Hi Craig,
>>
>> does it work like this?
>>
>> SELECT * FROM
>> (
>> SELECT
>> items.dateaccessioned,
>> items.barcode,
>> items.itemcallnumber,
>> biblio.title,
>> biblio.author,
>> biblioitems.publishercode,
>>
>> ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]')
>> AS Subject
>> FROM items
>> LEFT JOIN biblioitems ON (items.biblioitemnumber =
>> biblioitems.biblioitemnumber)
>> LEFT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber)
>> LEFT JOIN biblio_metadata ON (biblio_metadata.biblionumber =
>> biblio.biblionumber)
>> WHERE
>> items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>>
>> AND <<and (yyyy-mm-dd)|date>>
>> ) AS t
>> WHERE
>> Subject LIKE concat('%',<<Subject>>,'%')
>> ORDER BY dateaccessioned DESC
>>
>> Regards,
>> Holger
>>
>> -----Ursprüngliche Nachricht-----
>> Von: Koha <koha-bounces at lists.katipo.co.nz> Im Auftrag von Craig Butosi
>> Gesendet: Dienstag, 19. März 2019 15:23
>> An: Barton Chittenden <barton at bywatersolutions.com>
>> Cc: koha <koha at lists.katipo.co.nz>
>> Betreff: Re: [Koha] Koha 18.11 - SQL report to select ISBNs by Subject
>>
>> Hi everyone,
>>
>> Mohammad, Barton, thanks very much for this. Unfortunately, I've already
>> tried this report (I found it on the Koha SQL reports page online) and it
>> returns the following error (even after I replace the "biblioitems.marcxml"
>> statement with "biblio_metadata.metadata"):
>>
>> *The following error was encountered:*
>> The database returned the following error:
>> Unknown column 'biblio_metadata.metadata' in 'field list'
>> Please check the log for further details.
>>
>> which is strange. Here's what my report looks like:
>>
>> SELECT *
>> FROM(SELECT
>> items.dateaccessioned,
>> items.barcode,
>> items.itemcallnumber,
>> biblio.title,
>> biblio.author,
>> biblioitems.publishercode,
>> (SELECT
>>
>> ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]'))
>> AS Subject
>> FROM items
>> LEFT JOIN biblioitems ON
>> (items.biblioitemnumber=biblioitems.biblioitemnumber)
>> LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
>> WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND
>> <<and
>> (yyyy-mm-dd)|date>>) AS t
>> WHERE Subject LIKE concat('%',<<Subject>>,'%') ORDER BY dateaccessioned
>> DESC
>>
>> No errors in /var/log/mysql either.
>>
>> Any ideas?
>>
>> Many thanks,
>>
>> Craig Butosi, MA, MLIS, B Mus (Hons)
>> Library: library.craigbutosi.ca
>>
>>
>> On Tue, Mar 19, 2019 at 8:42 AM Barton Chittenden <
>> barton at bywatersolutions.com> wrote:
>>
>> > The data formerly found in biblioitems.xml moved to
>> > biblio_metadata.metadata in Koha 17.05.
>> >
>> > On Tue, Mar 19, 2019, 3:10 AM Mohammad Nashbat <MNashbat at alfaisal.edu>
>> > wrote:
>> >
>> >> Sorry, below is the correct one.
>> >>
>> >> SELECT *
>> >> FROM(SELECT
>> >> items.dateaccessioned,
>> >> items.barcode,
>> >> items.itemcallnumber,
>> >> biblio.title,
>> >> biblioitems.isbn,
>> >> biblioitems.publishercode,
>> >> (SELECT
>> >>
>> ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]'))
>> >> AS Subject
>> >> FROM items
>> >> LEFT JOIN biblioitems ON
>> >> (items.biblioitemnumber=biblioitems.biblioitemnumber)
>> >> LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
>> >> WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND
>> >> <<and (yyyy-mm-dd)|date>>) AS t WHERE Subject LIKE
>> >> concat('%',<<Subject>>,'%') ORDER BY dateaccessioned DESC
>> >>
>> >>
>> >> Thanks & Best Regards,
>> >>
>> >> Mohammed Nashbat
>> >> Library Instructor
>> >>
>> >>
>> >> -----Original Message-----
>> >> From: Mohammad Nashbat
>> >> Sent: Tuesday, March 19, 2019 10:04 ص
>> >> To: 'Craig Butosi'; koha at lists.katipo.co.nz
>> >> Subject: RE: [Koha] Koha 18.11 - SQL report to select ISBNs by
>> >> Subject
>> >>
>> >> Hi Craig,
>> >>
>> >> We are using the below SQL report, I hope it do the needful for you.
>> >>
>> >> SELECT *
>> >> FROM(SELECT
>> >> items.dateaccessioned,
>> >> items.barcode,
>> >> items.itemcallnumber,
>> >> biblio.title,
>> >> biblioitems.isbn,
>> >> biblioitems.publishercode,
>> >> (SELECT
>> >>
>> ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]'))
>> >> AS Subject
>> >> FROM items
>> >> LEFT JOIN biblioitems ON
>> >> (items.biblioitemnumber=biblioitems.biblioitemnumber)
>> >> LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
>> >> WHERE items.dateaccessioned BETWEEN '2019-02-01' AND '2019-03-19') AS
>> >> t WHERE Subject LIKE concat('%','Medicine','%') ORDER BY
>> >> dateaccessioned DESC
>> >>
>> >>
>> >> Thanks & Best Regards,
>> >>
>> >> Mohammed Nashbat
>> >> Library Instructor
>> >> Alfaisal University
>> >>
>> >>
>> >> -----Original Message-----
>> >> From: Koha [mailto:koha-bounces at lists.katipo.co.nz] On Behalf Of
>> >> Craig Butosi
>> >> Sent: Monday, March 18, 2019 07:29 م
>> >> To: koha at lists.katipo.co.nz
>> >> Subject: [Koha] Koha 18.11 - SQL report to select ISBNs by Subject
>> >>
>> >> Hi everyone,
>> >>
>> >> Running Koha 18.11 on Ubuntu 16.04 w/ MariaDB. Having a bit of
>> >> trouble understanding the db structural changes made in 18.xx and how
>> >> SQL reports are built in Koha 18.11. I know some tables and such have
>> >> changed since the old 16.xx days.
>> >>
>> >> *I'd like to create a report that draws a list of ISBNs, along with
>> >> call number and title, for my coverflow carousel based on subject
>> >> (i.e., 650$a) keyword*. I'd like to start featuring subject-based
>> >> resources on the carousel, not just new items.
>> >>
>> >> Any help would be very much appreciated. Just a note: there are a few
>> >> reports on the Koha SQL reports wiki that involve querying on the 650
>> >> field; but these do not work for what I would like to achieve.
>> >>
>> >> Many thanks,
>> >>
>> >> Craig Butosi, MA, MLIS, B Mus (Hons)
>> >> Library: library.craigbutosi.ca
>> >> _______________________________________________
>> >> Koha mailing list http://koha-community.org Koha at lists.katipo.co.nz
>> >> https://lists.katipo.co.nz/mailman/listinfo/koha
>> >>
>> >> ________________________________
>> >>
>> >> DISCLAIMER: This electronic mail transmission contains confidential
>> >> information intended only for the person(s) named. Any use,
>> >> distribution, copying or disclosure by any other person is strictly
>> >> prohibited. If you received this transmission in error, please notify
>> >> the sender by reply e-mail and then destroy the message. Opinions,
>> >> conclusions, and other information in this message that do not relate
>> >> to the official business of Alfaisal University shall understand to
>> >> be neither given nor endorsed by Alfaisal University. The contents of
>> >> any attachment to this e-mail may contain software viruses, which
>> could damage your own computer system.
>> >> While “Alfaisal University” has taken every reasonable precaution to
>> >> minimize this risk, we cannot accept liability for any damage which
>> >> you sustain as a result of software viruses. You should carry out
>> >> your own virus checks before opening the attachment.
>> >> _______________________________________________
>> >> Koha mailing list http://koha-community.org Koha at lists.katipo.co.nz
>> >> https://lists.katipo.co.nz/mailman/listinfo/koha
>> >>
>> >
>> _______________________________________________
>> Koha mailing list http://koha-community.org Koha at lists.katipo.co.nz
>> https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
More information about the Koha
mailing list