Author & Author ID

[Tanjamuse]

I have a custom column called #author_id/Author ID, which I download from fan fiction sites via FFF. The values are unique within each site; I preface them with a site identifier e.g. FF, BSV etc. The authors/Author(s) column is also downloaded from fan fiction sites via FFF.

An Author Id can use more than one Author(s) name, i.e. the ‘owner’ of an Author Id can submit stories under different pen names or simply change their pen name.

What I want is a list of books that have Author Ids that have multiple associated Author(s). So, in the attached screen shot the books from FF12345 and FF937682 would be displayed, but the book from FF45321 would not be displayed.

The number of Author Ids than have multiple pennames is small, and my library is in excess of a 100,000 books. So visually reviewing a complete list sorted on Author Id and Author is impractical.

Any ideas

[DaltonST]

@Tanjamuse:

Yes, but let me first say that this is worth doing only because you have 100,000 books...

See the attached image showing the "before" and "after" Calibre books selected.

To make this work for you, the following SQL shown in Steps 1-3 must be executed sequentially in a SQLite tool, such as SQLite Manager for Firefox or DBBrowser for SQLite.

To avoid any confusion with Standard Calibre column names, I added "FF" as appropriate.

The "30" is the table number for your FF Author ID custom column in my example, and the "31" is the table number for your FF Author Name custom column in my example. You must change the 30 and 31 to whatever your real numbers are.

-------
Step 1:

CREATE VIEW "__tanjamuse_ff_view_1" AS
SELECT book AS ff_author_id_book,
(SELECT value FROM custom_column_30 WHERE id = books_custom_column_30_link.value) AS ff_author_id,
(SELECT book FROM books_custom_column_31_link WHERE books_custom_column_31_link.book = books_custom_column_30_link.book) AS book,
(SELECT value FROM custom_column_31 WHERE id = (SELECT value FROM books_custom_column_31_link WHERE books_custom_column_31_link.book = books_custom_column_30_link.book)) AS ff_author_name
FROM books_custom_column_30_link

-------
Step 2:

CREATE VIEW "__tanjamuse_ff_view_2" AS
SELECT book,ff_author_id AS authorid,ff_author_name AS authorname
FROM __tanjamuse_ff_view_1
WHERE ((SELECT count(ff_author_id) FROM __tanjamuse_ff_view_1 WHERE ff_author_id = authorid ) > 1)

-------
Step 3:

CREATE VIEW "__tanjamuse_ff_view_3" AS
SELECT book as id, authorid as myauthorid,authorname as myauthorname
FROM __tanjamuse_ff_view_2
WHERE EXISTS
(SELECT book FROM __tanjamuse_ff_view_2 WHERE book <> id
AND authorname <> myauthorname
AND authorid = myauthorid )


-------
Step 4:

Using the MultiColumnSearch plug-in, execute this query in the Raw SQL Query Tab:

SELECT id FROM __tanjamuse_ff_view_3



Good luck.



DaltonST

[Terisa de morgan]

Dalton, I have a question. If we weren't taking into account 100000 books (I think performance is the issue but I can be wrong), could we use directly MCS with inter book search, something like:

• Left Side: authors <> authors
• Right side: #author_id = #author_id

Because I perhaps can use it in another kind of searches.

[DaltonST]

Originally Posted by Terisa de morgan

Dalton, I have a question. If we weren't taking into account 100000 books (I think performance is the issue but I can be wrong), could we use directly MCS with inter book search, something like:

• Left Side: authors <> authors
• Right side: #author_id = #author_id

Because I perhaps can use it in another kind of searches.

Terisa,

My comment to Tanjamuse that "let me first say that this is worth doing only because you have 100,000 books..." was a recognition of the time and effort that the solution would take a non-technical person to find, install, learn and use the SQLite tool to implement it. The solution then becomes cost-effective for them only when a manual solution becomes impractical due to library size.

For a person who already has the requisite knowledge and skillset, it is cost-effective from the start.

MCS Inter-Book queries require the "=" operator on both sides, so it cannot derive the correct answer to this problem. See the attached image showing the results, and then compare that to the image in my prior post showing the correct results for Tanjamuse's scenario using an MCS Raw SQL query.


DaltonST

[Terisa de morgan]

Originally Posted by DaltonST

MCS Inter-Book queries require the "=" operator on both sides, so it cannot derive the correct answer to this problem. See the attached image showing the results, and then compare that to the image in my prior post showing the correct results for Tanjamuse's scenario using an MCS Raw SQL query.

That's the problem then, because the plugin says nothing but it changes my operator.

[DaltonST]

The ToolTips explain the rules, and give an example. It auto-fixes errors that it can fix, but gives an error message when it cannot. Hover your mouse over each widget to see the specific ToolTips for that widget.

[Terisa de morgan]

Originally Posted by DaltonST

The ToolTips explain the rules, and give an example. It auto-fixes errors that it can fix, but gives an error message when it cannot. Hover your mouse over each widget to see the specific ToolTips for that widget.

Yes, the auto-correction goes well... if you expect the plugin to do it, of course. Thank you for the info.