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

Go Back   MobileRead Forums > E-Book Software > Calibre

Notices

Reply
 
Thread Tools Search this Thread
Old 05-19-2024, 03:59 PM   #1
Trel
Connoisseur
Trel began at the beginning.
 
Posts: 64
Karma: 10
Join Date: Jan 2010
Device: Onyx Boox Poke 3
What would be the best/correct way do query for

I have a custom read/unread column.
I'm aware of calibredb, but not very familiar with it.

What I tried to do was

Code:
calibredb --library-path=/path/to/library list --fields "series,series_index" --search 'series:True series_index:1.0 #readstatus:"=Read"' --sort-by 'series'
I got it mostly working, but three issues.

1. The sort is reverse alphabetical (I want alphabetical)
2. The sort is done where "insignificant" words like "A" or "The" are disregarded (I want them included)
3. It still prints the ID, despite me not including it in --fields (I don't want the ID shown).

I was able to handle this with a mixture of various Linux text processing tools like cut, sed, and sort, but ideally I wouldn't have to do this somehow?

Once I get that down, I want to additionally select the MAX series index for each series, but I have no clue how I'd do that.

Last edited by Trel; 05-19-2024 at 04:03 PM.
Trel is offline   Reply With Quote
Old 05-19-2024, 08:36 PM   #2
jmurphy
Zealot
jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.
 
Posts: 100
Karma: 1133068
Join Date: Sep 2007
Device: ipaq
It won't get rid of the id, but try:

list --fields series,series_index --search "series:true and series_index:1.0 and #readstatus:Read" --sort-by series --ascending
jmurphy is online now   Reply With Quote
Advert
Old 05-20-2024, 04:05 PM   #3
Trel
Connoisseur
Trel began at the beginning.
 
Posts: 64
Karma: 10
Join Date: Jan 2010
Device: Onyx Boox Poke 3
Quote:
Originally Posted by jmurphy View Post
It won't get rid of the id, but try:

list --fields series,series_index --search "series:true and series_index:1.0 and #readstatus:Read" --sort-by series --ascending

That addresses the reverse sort, but it also breaks filtering on readstatus because "read" matches "read" and "unread", it's gotta be "=Read". It unfortunately doesn't help the issue with 'a' and 'the' not getting sorted alphabetically either.
Trel is offline   Reply With Quote
Old 05-20-2024, 10:29 PM   #4
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: 44,020
Karma: 22669822
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
If you want to sort including leading articles create a custom column in the library that uses a template to generate the value of series. It will sort using the value as a simple string.
kovidgoyal is offline   Reply With Quote
Old 05-21-2024, 12:28 PM   #5
jmurphy
Zealot
jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.jmurphy ought to be getting tired of karma fortunes by now.
 
Posts: 100
Karma: 1133068
Join Date: Sep 2007
Device: ipaq
Quote:
Originally Posted by kovidgoyal View Post
If you want to sort including leading articles create a custom column in the library that uses a template to generate the value of series. It will sort using the value as a simple string.

Maybe a built-in Series Sort field similar to Author Sort and Title Sort would make sense and then allowing the user to sort by either the literal field or the sort field.

Is there any way to suppress the output of the id field?
jmurphy is online now   Reply With Quote
Advert
Old 05-21-2024, 12:52 PM   #6
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: 44,020
Karma: 22669822
Join Date: Oct 2006
Location: Mumbai, India
Device: Various
Quote:
Originally Posted by jmurphy View Post
Maybe a built-in Series Sort field similar to Author Sort and Title Sort would make sense and then allowing the user to sort by either the literal field or the sort field.

Is there any way to suppress the output of the id field?
Too much UI overhead for a niche use case.

And no you cannot suppress ids without them you can get duplicate rows. If you want full control use --for-machine which will output json and then postp process however you like using a tool such as jq or similar.
kovidgoyal is offline   Reply With Quote
Old 06-11-2024, 07:23 PM   #7
Trel
Connoisseur
Trel began at the beginning.
 
Posts: 64
Karma: 10
Join Date: Jan 2010
Device: Onyx Boox Poke 3
I know it's considered poor form to hit the database directly, but I managed to get this to so far grab the latest book in all series, I'm going to incorporate my custom Read/Unread column soon. Is there any real harm/risk in opening the DB in read only mode and running selects?

Code:
SELECT
    series_id
    ,book_id
    ,MAX(series_index) AS series_index
    ,series
    ,title
FROM
(
    SELECT
        s.sort AS series
        ,b.id AS book_id
        ,b.title AS title
        ,s.id AS series_id
        ,b.series_index AS series_index
    FROM
        books_series_link AS bs
    LEFT JOIN books AS b
        ON bs.book = b.id
    LEFT JOIN series AS s
        ON bs.series = s.id
)
GROUP BY
    series_id
ORDER BY
    series ASC;
Trel is offline   Reply With Quote
Old 06-11-2024, 07:55 PM   #8
chaley
Grand Sorcerer
chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.chaley ought to be getting tired of karma fortunes by now.
 
Posts: 11,791
Karma: 7029971
Join Date: Jan 2010
Location: Notts, England
Device: Kobo Libra 2
FWIW: a search for selecting the highest Series_Index ?
chaley is offline   Reply With Quote
Old 06-11-2024, 11:57 PM   #9
Trel
Connoisseur
Trel began at the beginning.
 
Posts: 64
Karma: 10
Join Date: Jan 2010
Device: Onyx Boox Poke 3
I'm trying to do this to be able to export things like CSV and similar.

My final query which looks to be working is

Code:
SELECT                                          
    series_id                                   
    ,book_id                                    
    ,MAX(series_index) AS series_index          
    ,series                                     
    ,title                                      
    ,read_status                                
FROM                                            
(                                               
    SELECT                                      
        s.sort AS series                        
        ,b.id AS book_id                        
        ,b.title AS title                       
        ,s.id AS series_id                      
        ,b.series_index AS series_index         
        ,cc2.value AS read_status               
    FROM                                        
        books_series_link AS bs                 
    LEFT JOIN books AS b                        
        ON bs.book = b.id                       
    LEFT JOIN series AS s                       
        ON bs.series = s.id                     
    LEFT join books_custom_column_2_link AS bcc2
        ON b.id = bcc2.book                     
    LEFT join custom_column_2 AS cc2            
        ON bcc2.value = cc2.id                  
)                                               
GROUP BY                                        
    series_id                                   
ORDER BY                                        
    series ASC;
I can add a WHERE clause to the outer query to filter on the read status

EDIT: this is all precursor for me searching out and finding if I've completed a completed series and adding custom metadata to that effect, but also something I frequently try to look up

Last edited by Trel; 06-12-2024 at 12:02 AM.
Trel 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
Query re editor johngross Editor 0 10-07-2019 04:46 PM
meaning and how to correct ralphiedee Sigil 4 10-17-2012 03:48 PM
Metadata query iain robinson ePub 4 08-08-2012 12:07 PM
Hello and query MPDuke Introduce Yourself 8 12-13-2011 10:27 AM
Query - Kindle DX US ukhant Kindle Developer's Corner 2 07-20-2010 03:23 AM


All times are GMT -4. The time now is 02:27 PM.


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