[Koha] Local covers and the Coverflow plugin

[Alvaro Cornejo]

Hi

You need to "discard" items with no covers from your query.

We use this:

SELECT DISTINCT biblio.title, biblio.biblionumber, c.imagenumber AS
localcover
FROM items i
LEFT JOIN biblioitems USING (biblioitemnumber)
LEFT JOIN biblio ON (i.biblionumber=biblio.biblionumber)
LEFT JOIN biblioimages c ON (i.biblionumber=c.biblionumber)
  WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND
biblioitems.isbn IS NOT NULL  AND c.imagenumber != ''
  ORDER BY rand()
LIMIT  50

Hope helps

Regards

Alvaro


|----------------------------------------------------------------------------------------|
 Stay safe / Cuídate/  Reste sécurisé
*7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
mesure.
 *q *Recycle always / Recicla siempre / Recyclez toujours
 P Print only if absolutely necessary / Imprime solo si es necesario /
Imprimez seulement si nécessaire


Le lun. 19 juin 2023 à 02:16, library at ssst.edu.ba <library at ssst.edu.ba> a
écrit :

> Hello Alvaro,
>
> I don't know how to do that, since I never even touched SQL before. I can
> copy and paste code from the SQL list, but don’t really understand what it
> does and how to change it to do what I need.
>
> I just checked the report, it is public. The code I'm using is from the
> plugin configuration:
>
> SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ' ', 1) AS isbn, b.title
>   FROM items i
>   LEFT JOIN biblioitems m USING (biblioitemnumber)
>   LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
>   WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= i.dateaccessioned AND
> m.isbn IS NOT NULL AND m.isbn != ''
>   GROUP BY biblionumber
>   HAVING isbn != ""
>   ORDER BY rand()
>   LIMIT 30
>
> The instructions then go on to say:
>
> Note: You can add an additional column 'localcover' - this should be blank
> if the biblio doesn't have a localcover and can contain any other data if
> it does. If this column is populated a local cover will be used. Example
> below:
>
> SELECT DISTINCT biblio.title, biblio.biblionumber,
> SUBSTRING_INDEX(biblioitems.isbn, ' ', 1) AS isbn, c.imagenumber AS
> localcover
> FROM items
> LEFT JOIN biblioitems USING (biblioitemnumber)
> LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
> LEFT JOIN cover_images c ON (items.biblionumber=c.biblionumber)
> WHERE biblioitems.isbn IS NOT NULL AND biblioitems.isbn !=''
> ORDER BY RAND()
> LIMIT 15;
>
>
> But when I tried replacing the first code with the second, I didn't get
> any results. Right now I'm back to the original code, and have turned the
> local covers option off, so I have several "No image available" images.
> Where do I change what to get local covers?
>
> Best regards
>
> Aida Đikić
>
>
> From: Alvaro Cornejo <cornejo.alvaro at gmail.com>
> Sent: Friday, June 16, 2023 4:27 PM
> To: library at ssst.edu.ba
> Cc: koha at lists.katipo.co.nz
> Subject: Re: [Koha] Local covers and the Coverflow plugin
>
> Hi Aida
>
> You only need to change/edit the report that pulls information for
> coverflow. Also remember to define the report as public.
>
> Regards
>
> Alvaro
>
>
> |----------------------------------------------------------------------------------------|
>  Stay safe / Cuídate/  Reste sécurisé
>  Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
> mesure.
>   Recycle always / Recicla siempre / Recyclez toujours
>   Print only if absolutely necessary / Imprime solo si es necesario /
> Imprimez seulement si nécessaire
>
>
> Le ven. 16 juin 2023 à 03:41, mailto:library at ssst.edu.ba <mailto:
> library at ssst.edu.ba> a écrit :
> Hello again,
>
> I'm trying to find a way to have local covers show in the Coverflow plugin
> on the OPAC page for titles not added via Z39.50.
>
> I had originally set it up using the first example in the configuration
> (items added in the last 30 days), but going back I see there's another
> example for adding additional columns. Since it said, "you can add an
> additional column" I thought I was supposed to paste it below the already
> existing code. That... ended up with an error yeah :) So I replaced it and
> ran the report but could not get a local cover.
>
> What am I doing wrong this time? I uploaded the cover in the holdings
> area. Is there another location I should do it instead? I checked old posts
> on the mailing list, but could not find any solutions for this. I would
> also like to work out a way to show covers of our collections (e.g. CS
> textbooks, ECON textbooks, donated books etc.) but for that I need to
> figure out lists??
>
> I'm on Koha 23.05.00 and Ubuntu 22.04.2 LTS.
>
> Best regards
>
> Aida Đikić
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> mailto:Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>