numofbooks / numofauthors

drMerry · 09-08-2011, 09:58 AM

Is there a way to find these sort of books:

A: Authors that do have x books, less than x books, more than x books
B: Books that have y authors, less than y authors, more than y authors

B is maybe possible with regexp, but I do not know how I can search over the & character?

kovidgoyal · 09-08-2011, 10:56 AM

Sort the Tag Browser by popularity for A.
There is no way to do B. You can make sql queries against metadata.db using any sql viewer program instead.

drMerry · 09-08-2011, 12:05 PM

thanks

Even so, I will need to use sql for the next I think?

C: Authors with books in language x.

(In that case, I would like the return of all books by that authors, e.g. so I can tag the books without language set)

kovidgoyal · 09-08-2011, 12:16 PM

Use a search restriction for that. Restrict to language x and the Tag Browser will show only the authors with books that have that language.

Manichean · 09-08-2011, 12:41 PM

Moderator Notice
Moved out from the development forum. Please see the sticky there.

drMerry · 09-08-2011, 03:44 PM

Quote:

Originally Posted by kovidgoyal

Use a search restriction for that. Restrict to language x and the Tag Browser will show only the authors with books that have that language.

Yes, that is an option for finding the books.
But I was looking for something else:

I want to select the AUTHORS that do have books in language X

E.g.

Author 1 book a Lang X
Author 1 book b Lang Y
Author 1 book c Lang Y
Author 2 Book d Lang Y
Author 3 Book e Lang x
Author 3 Book f Lang x
Author 3 Book g Lang (NONE)

If I search for authors wilt lang x, I would get book a, b, c (author 1) and e, f, g (author 3) as return.

In the case of Author 3, I do see 3 books with lang x and 1 without lang. It is than more easy to assign the lang (most likely book g is in lang x)

drMerry · 09-08-2011, 03:57 PM

Quote:

Originally Posted by Manichean

Moved out from the development forum. Please see the sticky there.

I think it is in the right place. It are questions about functions for developing plugins...

Manichean · 09-08-2011, 05:38 PM

Quote:

Originally Posted by drMerry

I think it is in the right place. It are questions about functions for developing plugins...

That may be. But a) that's not clear, as the thread discusses both GUI functionality or third-party tools, and b) it doesn't discuss Calibre code, APIs, coding techniques or anything of the kind. Thus: Moved.

theducks · 09-08-2011, 05:39 PM

Quote:

Originally Posted by drMerry

Yes, that is an option for finding the books.
But I was looking for something else:

I want to select the AUTHORS that do have books in language X

E.g.

Author 1 book a Lang X
Author 1 book b Lang Y
Author 1 book c Lang Y
Author 2 Book d Lang Y
Author 3 Book e Lang x
Author 3 Book f Lang x
Author 3 Book g Lang (NONE)

If I search for authors wilt lang x, I would get book a, b, c (author 1) and e, f, g (author 3) as return.

In the case of Author 3, I do see 3 books with lang x and 1 without lang. It is than more easy to assign the lang (most likely book g is in lang x)

If you set a Restrict to filter on Language, All you see in the Tag Browser IS those that make it past the Filter.

Step 1: pick the Language in the Tag Browser.
Step 2: Set the Restrict to filter to "*Current Search"
Step 3: Set to Sort by popularity (at the bottom)
Expand Authors
and Presto: the Count of Each Author Shows (in descending order)

drMerry · 09-08-2011, 07:53 PM

Quote:

Originally Posted by theducks

If you set a Restrict to filter on Language, All you see in the Tag Browser IS those that make it past the Filter.

Step 1: pick the Language in the Tag Browser.
Step 2: Set the Restrict to filter to "*Current Search"
Step 3: Set to Sort by popularity (at the bottom)
Expand Authors
and Presto: the Count of Each Author Shows (in descending order)

Well, this won't result in the list I'm asking for.
This will show a list of books in language x.
I do want a list of authors writing in language x (and their books)
so, if I search for authors_language:"=x", I want a, b, c, e, f, g to be returned.

Authors 1 and 3 do write in x.
Books a, b, c, e, f, g are written by 1 and 3.
books b, c and g are not in language x, but while they are written by 1 or 3, I want those books also to be returned...

(this makes it easy for me to, for example assign book g the language x)
This search-function could also return book g:
authors_language:"=x" and language:false

So, seeing the reactions, I think I will have to write an sql-query...

drMerry · 09-08-2011, 07:53 PM

Quote:

Originally Posted by Manichean

That may be. But a) that's not clear, as the thread discusses both GUI functionality or third-party tools, and b) it doesn't discuss Calibre code, APIs, coding techniques or anything of the kind. Thus: Moved.

NP

theducks · 09-08-2011, 08:20 PM

Quote:

Originally Posted by drMerry

Well, this won't result in the list I'm asking for.
This will show a list of books in language x.
I do want a list of authors writing in language x (and their books)
so, if I search for authors_language:"=x", I want a, b, c, e, f, g to be returned.

Authors 1 and 3 do write in x.
Books a, b, c, e, f, g are written by 1 and 3.
books b, c and g are not in language x, but while they are written by 1 or 3, I want those books also to be returned...

(this makes it easy for me to, for example assign book g the language x)
This search-function could also return book g:
authors_language:"=x" and language:false

So, seeing the reactions, I think I will have to write an sql-query...

Yikes, you want a inner join linked on the first search.

mbovenka · 09-12-2011, 07:05 AM

Quote:

Originally Posted by kovidgoyal

There is no way to do B. You can make sql queries against metadata.db using any sql viewer program instead.

Kovid, isn't that just 'authors:#=<n>' and friends? Seems to do what he wants, if I understand correctly (find books with <n> authors, in this case).

kovidgoyal · 09-12-2011, 12:33 PM

Didn't he want number of authors for books of a specific language? That will give him the list of books that have n authors. But maybe that's what he wants.

drMerry · 09-16-2011, 06:34 AM

authors:#=<n>

That's what I was looking for for A and B.
I knew calibre had advanced options I could not remember

But Kovid's last reaction is true for C.

I have made some other queries to find some more info.

Does one of you know how to put this in a plugin?

I did make these queries:
# of books per tag (already available in browser)
# of tags per book (the mentioned search will show this)
# tags per author.
Not available yet (as far as I know)

The sql I use is:

Code:

SELECT authors.name, COUNT(DISTINCT books_tags_link.tag) AS NumberOfTags
FROM books_tags_link INNER JOIN books_authors_link
ON books_tags_link.book = books_authors_link.book JOIN authors
ON authors.id = books_authors_link.author
GROUP BY authors.name
ORDER BY NumberOfTags

How can I put this in a plugin?

09-08-2011, 09:58 AM	#1
drMerry Addict Posts: 293 Karma: 21022 Join Date: Mar 2011 Location: NL Device: Sony PRS-650	numofbooks / numofauthors Is there a way to find these sort of books: A: Authors that do have x books, less than x books, more than x books B: Books that have y authors, less than y authors, more than y authors B is maybe possible with regexp, but I do not know how I can search over the & character?

09-16-2011, 06:34 AM	#15
drMerry Addict Posts: 293 Karma: 21022 Join Date: Mar 2011 Location: NL Device: Sony PRS-650	authors:#=<n> That's what I was looking for for A and B. I knew calibre had advanced options I could not remember But Kovid's last reaction is true for C. I have made some other queries to find some more info. Does one of you know how to put this in a plugin? I did make these queries: # of books per tag (already available in browser) # of tags per book (the mentioned search will show this) # tags per author. Not available yet (as far as I know) The sql I use is: Code: SELECT authors.name, COUNT(DISTINCT books_tags_link.tag) AS NumberOfTags FROM books_tags_link INNER JOIN books_authors_link ON books_tags_link.book = books_authors_link.book JOIN authors ON authors.id = books_authors_link.author GROUP BY authors.name ORDER BY NumberOfTags How can I put this in a plugin?

09-08-2011, 10:56 AM	#2
kovidgoyal creator of calibre Posts: 45,359 Karma: 27182818 Join Date: Oct 2006 Location: Mumbai, India Device: Various	Sort the Tag Browser by popularity for A. There is no way to do B. You can make sql queries against metadata.db using any sql viewer program instead.

09-08-2011, 12:05 PM	#3
drMerry Addict Posts: 293 Karma: 21022 Join Date: Mar 2011 Location: NL Device: Sony PRS-650	thanks Even so, I will need to use sql for the next I think? C: Authors with books in language x. (In that case, I would like the return of all books by that authors, e.g. so I can tag the books without language set)

09-08-2011, 12:16 PM	#4
kovidgoyal creator of calibre Posts: 45,359 Karma: 27182818 Join Date: Oct 2006 Location: Mumbai, India Device: Various	Use a search restriction for that. Restrict to language x and the Tag Browser will show only the authors with books that have that language.

09-08-2011, 12:41 PM	#5
Manichean Wizard Posts: 3,130 Karma: 91256 Join Date: Feb 2008 Location: Germany Device: Cybook Gen3	Moderator Notice Moved out from the development forum. Please see the sticky there.

09-12-2011, 12:33 PM	#14
kovidgoyal creator of calibre Posts: 45,359 Karma: 27182818 Join Date: Oct 2006 Location: Mumbai, India Device: Various	Didn't he want number of authors for books of a specific language? That will give him the list of books that have n authors. But maybe that's what he wants.