Register Guidelines E-Books Search Today's Posts Mark Forums Read

Go Back   MobileRead Forums > E-Book Software > Calibre > Library Management

Notices

Reply
 
Thread Tools Search this Thread
Old 10-29-2016, 05:11 AM   #1
sbin
Voracious reader
sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.
 
sbin's Avatar
 
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.
sbin is offline   Reply With Quote
Old 10-29-2016, 08:16 AM   #2
kovidgoyal
creator of calibre
kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.kovidgoyal ought to be getting tired of karma fortunes by now.
 
kovidgoyal's Avatar
 
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.
kovidgoyal is offline   Reply With Quote
Advert
Old 10-29-2016, 11:38 PM   #3
DaltonST
Deviser
DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.
 
DaltonST's Avatar
 
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
Attached Thumbnails
Click image for larger version

Name:	Multi-Column Search SQL Queries Tab Only.jpg
Views:	202
Size:	560.4 KB
ID:	152681  
DaltonST is offline   Reply With Quote
Old 10-30-2016, 06:46 PM   #4
DaltonST
Deviser
DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.DaltonST ought to be getting tired of karma fortunes by now.
 
DaltonST's Avatar
 
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).
*\
DaltonST is offline   Reply With Quote
Old 10-31-2016, 08:55 AM   #5
sbin
Voracious reader
sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.
 
sbin's Avatar
 
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
sbin is offline   Reply With Quote
Advert
Old 11-01-2016, 05:34 AM   #6
sbin
Voracious reader
sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.sbin is less competitive than you.
 
sbin's Avatar
 
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!
sbin is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Forum Jump

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


All times are GMT -4. The time now is 06:26 AM.


MobileRead.com is a privately owned, operated and funded community.