Search series and tags

[DirCat]

(I use FanFicFare to download fanfiction. However, this is purely a search question, so I posted it here. If it needs to go somewhere else, please let me know.)

I have some books where the series is incomplete, where the first {x} books are done (series:true and tags has "Completed") but there are one or more books where the series is incomplete (series:true and tags has "In-Progress").

In addition, I have some series that just have one book in them for now (series:true, but just one book in the series).

Is there a way to find all books within a series that has at least one books in the particular series with the "In-Progress" tag, as well as find any books with a series marked but just one book in the series?

[theducks]

you want the 'count' operator

You can search for the number of items in multiple-valued fields such as tags. These searches begin with the character #, then use the same syntax as numeric fields. For example, to find all books with more than 4 tags use tags:#>4. To find all books with exactly 10 tags use tags:#=10.

[DirCat]

Originally Posted by theducks

you want the 'count' operator

Thanks! However, this doesn't fit the question.

At first I thought it fit the second part of the question (finding out how if a series just has one book in it). I'm not looking for the number of series in a tag-like column. I am looking for the number of books that a particular series has.

What page had the count operator? Maybe it'll have more of what I am looking for.

[BetterRed]

I think he means the count function in calibre's template language see The calibre template language — Function classification - it's a List function, i.e. it operates on lists.

BR

[theducks]

Originally Posted by DirCat

Thanks! However, this doesn't fit the question.

At first I thought it fit the second part of the question (finding out how if a series just has one book in it). I'm not looking for the number of series in a tag-like column. I am looking for the number of books that a particular series has.

What page had the count operator? Maybe it'll have more of what I am looking for.

It only works for fields that can contain more than 1 member (Authors, Tags). series can only have 1 member in the field.

[BetterRed]

@DirCat - the Manage Series list has a Book Count column, which you can sort.

BR

[DirCat]

After doing a little more research, I found a plugin that might help - Multi-Column Search. I'm going to play around with it for a bit and, if I can't figure it out, I'll ask in their forum. Thanks!

[DirCat]

Originally Posted by DirCat

After doing a little more research, I found a plugin that might help - Multi-Column Search. I'm going to play around with it for a bit and, if I can't figure it out, I'll ask in their forum. Thanks!

And that's exactly what I needed to do. I had to install that plugin, install a SQLite viewer (to get the column names), and create 2 SQL statements (one to mark books that met the first conditions (series incomplete), and one to unmark books that no longer met the conditions (series completed).

Now I can add the marked column to my search to find which books need to be downloaded to my tablet.

In case you're interested, here's the first query:

Spoiler:

/* Series has missing books or just one book */

SELECT id 
FROM books 
WHERE (id IN 
	(SELECT book 
	 FROM books_series_link 
	 WHERE series IN
		(SELECT	series
		 FROM	books_series_link
		 WHERE	book IN
			(SELECT	book
			 FROM	_mcs_tags_by_book
			 WHERE	tagname = 'In-Progress')
		)
	)
OR id IN
	(SELECT book 
	 FROM 	books_series_link 
	 GROUP BY series
	 HAVING	count(series) = 1)
)
AND id NOT IN
	(SELECT	book
	 FROM	custom_column_16
	 WHERE	value = '1')

And here's the second one:

Spoiler:

/* Reverse of above, to unmark books when the series is completed */

SELECT id 
FROM books 
WHERE (id NOT IN 
	(SELECT book 
	 FROM books_series_link 
	 WHERE series IN
		(SELECT	series
		 FROM	books_series_link
		 WHERE	book IN
			(SELECT	book
			 FROM	_mcs_tags_by_book
			 WHERE	tagname = 'In-Progress')
		)
	)
AND id NOT IN
	(SELECT book 
	 FROM 	books_series_link 
	 GROUP BY series
	 HAVING	count(series) = 1)
)
AND id IN
	(SELECT	book
	 FROM	custom_column_16
	 WHERE	value = '0')

(NOTE: The table custom_column_16 is my special table to say whether to ignore the series or not. If set to Yes (1), it means that the missing pieces aren't important (a substory or the like) and to ignore them in the first script. If set to No (0), once the conditions fail to match anymore, it will allow the second script to find them.)