[Koha] Click Tracker

[Eric Phetteplace]

Hi Victoria,

This sounds like something COALESCE could do, a function I only learned
about from this Koha US video: https://www.youtube.com/watch?v=35UvrpcYFFA

Otherwise you could also do a convoluted IF() expression, since it sounds
like the issue is working around a value that is sometimes null and
sometimes not. Like:

IF(borrowers.surname IS NOT NULL, CONCAT(borrowers.firstname, '
',borrowers.surname), 'anonymous')

You have no "FROM" statement in your SQL query so something must be
missing, presumably "FROM linktracker" which connects the biblio and
borrowers tables. Anyways, you probably want to JOIN these tables in such a
way that even the records with no borrowernumber (e.g. anonymous users) are
included. So

...JOIN biblio USING (biblionumber)
LEFT JOIN borrowers USING (borrowernumber)

A LEFT JOIN retains every row so far in the record set, but connects them
to the following table if possible. So it should work whether the rest of
your query has a borrowernumber or not. You said your query is working if
you just delete the CONCAT() though, which seems odd to me, but if that's
so then the LEFT JOIN probably is not necessary.

Best,

ERIC PHETTEPLACE Systems Librarian, Libraries (he/him)

ephetteplace at cca.edu | o 510.594.3660 (cca)

5212 Broadway | Oakland, CA | 94618

CCA is situated on the traditional unceded lands of the Ohlone peoples.

Black-owned bookstores in Oakland: Ashay by the Bay
<https://ashaybythebay.com/>, Marcus Books
<https://www.facebook.com/marcus.books/>

:(){ :|: & };:


On Sun, Jan 31, 2021 at 6:25 PM Ma. Victoria H. Silva-Manuel <
mavicsilva at gmail.com> wrote:

> Good day.
>
> I am using the SQL report below to track clicks from tag 856$u
>
> SELECT count(*) AS 'No. of Times Clicked', biblio.title AS 'Title',
> > CONCAT(borrowers.firstname, ' ',borrowers.surname) AS 'Patron', URL FROM
> > linktracker
> >
> > JOIN biblio USING(biblionumber)
> >
> > JOIN borrowers USING(borrowernumber)
> >
> > WHERE date(timeclicked) BETWEEN '2021-01-01' AND '2021-01-31'
> >
> > GROUP BY url
> >
>
> The above report only shows the links clicked by log in users.
> When I delete the concat, it shows the links clicked by all users but does
> not show the names of logged in users.
> Is there a way to create a report that will show links clicked by all users
> and show the names if the users are logged in?
>
> Thanks.
>
>
>
> --
> Ma. Victoria H. Silva-Manuel
> Registered Librarian, 3892
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>