View Single Post
Old 10-02-2019, 04:13 PM   #8
DirCat
Enthusiast
DirCat invented the internet.DirCat invented the internet.DirCat invented the internet.DirCat invented the internet.DirCat invented the internet.DirCat invented the internet.DirCat invented the internet.DirCat invented the internet.DirCat invented the internet.DirCat invented the internet.DirCat invented the internet.
 
Posts: 26
Karma: 84276
Join Date: Aug 2011
Location: Tuscaloosa, AL
Device: Samsung Tablet
Talking Figured it out!

Quote:
Originally Posted by DirCat View Post
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:
Code:
/* 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:
Code:
/* 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.)
DirCat is offline   Reply With Quote