10-29-2016, 05:11 AM | #1 |
Voracious reader
Posts: 56
Karma: 14644
Join Date: Oct 2009
Device: Kobo Aura One, Sony PRS T2, iPhone 6, Cybook Orizon, Cybook Gen3
|
IF statement in virtual libraries
I'd like to have a virtual library that shows:
— all the books I haven't yet read — AND the books I have already read IF they are part of a series with books still unread. I have a "Read" yes/no/empty custom column (books unread have an empty value) and, of course, the "Series" column. Is there a way I can accomplish this without manually tagging all the books I want to show? Thanks Last edited by sbin; 10-29-2016 at 05:14 AM. |
10-29-2016, 08:16 AM | #2 |
creator of calibre
Posts: 43,771
Karma: 22666666
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
|
No search expressions (and the template language) are restricted to information about the current book only. So there is no way for them to express that a book is part of series that has some condition based on other books in the series.
|
Advert | |
|
10-29-2016, 11:38 PM | #3 |
Deviser
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
|
MCS: Raw SQL Query Tab
@sbin:
If you use a roll-your-own native "SQL Query" using the Multi-Column Search plug-in, I believe so. https://www.mobileread.com/forums/sho...d.php?t=261712 For an example of what I am referring to, see the attached image. If you know SQL, the example SQL code in the SQL Query Tab plus that Tab's ToolTips should tell you what you need to know. If you don't know SQL, I might have time to write the SQL Query for you after tomorrow (Diwali parties are more fun than writing SQL). If so, I will post a template for you to copy-and-change per your specific Custom Column names. Note that the options "Final Filters" and "All Author's Books" may also be specified to be applied after the SQL Query executes but before the results are shown to you. DaltonST |
10-30-2016, 06:46 PM | #4 |
Deviser
Posts: 2,265
Karma: 2090983
Join Date: Aug 2013
Location: Texas
Device: none
|
MCS: Raw SQL Query - Solution
@sbin:
As I promised you, a raw SQL Query for use in the MCS plug-in to give you the books that you requested is shown below. You will have to change the "10" (as in custom_column_10) to whatever the correct number is per the ToolTips in the MCS SQL Query Tab shown when hovering your mouse outside of the input area. See the image in my prior post to see the list of custom columns for the current Library that appears when you hover in the grey area. DaltonST Code:
SELECT id FROM books WHERE (id IN (SELECT book FROM custom_column_10 WHERE custom_column_10.value = 1) AND id IN (SELECT book FROM books_series_link WHERE books_series_link.series IN (SELECT series FROM books_series_link WHERE books_series_link.book <> books.id AND books_series_link.book NOT IN (SELECT book FROM custom_column_10 WHERE custom_column_10.value = 1)))) OR (id IN (SELECT book FROM custom_column_10 WHERE custom_column_10.value = 0)) OR (id NOT IN (SELECT book FROM custom_column_10)) /* Explanation: Custom Column 10 uses non-normalized table custom_column_10, which in this scenario is the boolean (Yes/No) datatype meaning "Has Been Read Already". [1] the top part retrieves all books that have already been read but also simultaneously have a series that is found for other books that have not already been read. [2] the middle part retrieves all books that are explicitly marked as not already having been read. [3] the bottom part retrieves all books that are not marked one way or the other as having been read (i.e., are undefined for that custom column, meaning that they are not even in the table). *\ |
10-31-2016, 08:55 AM | #5 |
Voracious reader
Posts: 56
Karma: 14644
Join Date: Oct 2009
Device: Kobo Aura One, Sony PRS T2, iPhone 6, Cybook Orizon, Cybook Gen3
|
Thanks, I'll definitely give a try to your plugin as soon as I have some free time to tinker
|
Advert | |
|
11-01-2016, 05:34 AM | #6 |
Voracious reader
Posts: 56
Karma: 14644
Join Date: Oct 2009
Device: Kobo Aura One, Sony PRS T2, iPhone 6, Cybook Orizon, Cybook Gen3
|
In my custom column "read", a 0 value means an abandoned book, while an unread book has an empty value. So I just had to change 0 with NULL in your query and it worked like I wanted to.
Thank you! |
Thread Tools | Search this Thread |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Understanding Virtual Libraries | Rellwood | Library Management | 13 | 11-17-2015 08:39 AM |
Virtual Libraries Out of Control | miajoshua | Calibre | 2 | 02-23-2015 10:47 AM |
Virtual Libraries | scottmc | Library Management | 0 | 08-13-2014 07:15 PM |
Virtual Libraries | Ortep | Library Management | 68 | 12-21-2013 04:23 AM |
Virtual Libraries - web? | soundsfromsound | Calibre | 20 | 08-16-2013 02:15 PM |