Quick Search queries

[brendan-pike]

There appears to be a bug in the quick search feature or I am not understanding the sql queries. According to my media statistics, my "Number of albums" is 7240. Making a new quick search for testing I tried this;
genre ='Folk Rock' I get 6177 albums. But if I use
album.year BETWEEN 1970 AND 2022 I still get 6177 results. But if I use
genre ='Folk Rock' and album.year BETWEEN 1970 AND 2022 I get just 1 result.
If I go to normal genre and select "Folk Rock" I get 854 albums.

How do I go about debugging this?

[ArturSierzant]

Indeed, 'quick search' and 'genre' give different results for some genres. I'll try to figure it out - thanks for reporting.

To debug simply try to write query with error, like:
genre1 ='Folk Rock' and album.year BETWEEN 1970 AND 2022

Selecting this in quick search you will see error message with full query string:
SELECT album, album.artist, artist_alphabetic, album.year, month, genre_id, image_id, album.album_id FROM album, track WHERE album.album_id=track.album_id AND (genre1 ='Folk Rock' and album.year BETWEEN 1970 AND 2022) GROUP BY track.album_id ORDER BY album.artist, year, album

Then you can play with it in mysql (I use phpMyAdmin).

[ArturSierzant]

OK, i see now - it will not work for albums with multi-genres. I'll try to fix it.

Try this:
genre like '%Folk Rock%' and album.year BETWEEN 1970 AND 2022

[ArturSierzant]

In e892d74 I modified slightly query used in quick search. But query like genre ='Folk Rock' and album.year BETWEEN 1970 AND 2022 worked for me even before this modification... Maybe it will help in your configuration.

Did genre like '%Folk Rock%' and album.year BETWEEN 1970 AND 2022 worked for you?

Using % in query should work for genres that names are not part of other ones. Problem is when you want to limit results to only rock - then %rock% will give you all kinds of rock, i.e. folk rock, progressive rock, classic rock and so on. In that case you can use regexp: genre regexp '(^|[^[:space:]])rock' - this should give fine results in majority cases. One can try to modify this to not match rockabilly :)

[brendan-pike]

Yes this worked Artur thank you :)

This would mean the default example of Pop of the 80's might be better as genre regexp '(^|[^[:space:]])pop(^|[^[:space:]])' and album.year BETWEEN 1980 AND 1989 although a general catch for Pop anything could be suitable too I guess.
You may also want to update your https://ompd.pl/quick-search page to elaborate a bit since its referenced on the admin side.

I reckon a GUI filter would be an excellent addition really. Even though I bang on about increasing what album/track attributes are indexed having a GUI to filter on the existing stuff would be a useful tool. eg.

• a multi-select for existing genres
• a single or year range that filters against album.year
• country (all, or select specific)
• Results shown via AJAX to avoid the need for page refresh

That's all that is indexed currently but even having that would let people explore their music in a different way and we could build from there.

Many thanks as always 👍

[ArturSierzant]

Thanks again for suggestions, I appreciate that.

As for 'Pop' example: it should actually be:
genre regexp '(^|[^[:space:]])pop($|[^[:space:]])'
First ^ in brackets means 'start of string' while $ - 'end of string'