Template to search notes?

[Montana Harper]

I realize this is probably a really niche thing, but is there any way to search a notes field (e.g., notes attached to an author) for specific text? I want to build a custom column that shows me a check mark if particular text is included in an author note.

I have a number of custom columns built from other columns that do something similar, but since the notes aren't fields with lookup names, I'm not even sure if it's possible to do what I want. Currently I'm using a workaround that involves adding a specific tag to every book by an author whom I want to flag, but that's both time-consuming and easy to forget about when adding a new book, whereas if I can just make a single note for the author it would be so much easier.

TIA!

[Comfy.n]

Originally Posted by Montana Harper

I realize this is probably a really niche thing, but is there any way to search a notes field (e.g., notes attached to an author) for specific text? I want to build a custom column that shows me a check mark if particular text is included in an author note.

I'd be interested in that, as well.

Currently the way I see to do it is using the Notes Browser with its FTS-like search syntax, like this:



In that example I've searched for authors born in 9/11.

Default shortcut is Ctrl + Shift + N. However I can't tell how to bring all results from that search into "main Calibre", so that I could bulk add the "Born in 9/11" tag, for instance.

[chaley]

Originally Posted by Montana Harper

I realize this is probably a really niche thing, but is there any way to search a notes field (e.g., notes attached to an author) for specific text? I want to build a custom column that shows me a check mark if particular text is included in an author note.

I have a number of custom columns built from other columns that do something similar, but since the notes aren't fields with lookup names, I'm not even sure if it's possible to do what I want. Currently I'm using a workaround that involves adding a specific tag to every book by an author whom I want to flag, but that's both time-consuming and easy to forget about when adding a new book, whereas if I can just make a single note for the author it would be so much easier.

TIA!

You can do this with the template function get_note().

You can search a note using a template like this one, suitably modified.

program:
	first_author = sublist($authors, 0, 1, '&');
	note = get_note('authors', first_author, '1');
	if 'something' in note then
		ans = 'Yes'
	else
		ans = 'No'
	fi;
	ans

Notes on the template:

• This uses the first author. You must decide what to do if there are multiple authors. For example, you might want to loop over all the authors checking if any have a note.
• The first argument of the 'in' operator is a regular expression. Keep that in mind when deciding on what search you want to do.
• Using the 'ans' variable isn't really necessary. You can instead use the return value of the 'if'. I use the variable to avoid problems if/when code is added after the if, just to be sure I am getting the return value I want.
• IMPORTANT: While testing this I found a bug in the get_note() function. The 'plain_text' option doesn't work. I have submitted a fix to Kovid. I will post here when the fix is in calibre source. In the meantime use HTML instead of plain text (the third parameter to get_note() is ''). It will be significantly slower but it should work as long as what you are looking for doesn't resemble an HTML tag too closely.

Depending on what you want to do, there are two ways to proceed.

1. You just want a column with a checkmark
   • Create a composite column using the above template, with "show checkmarks" True.
2. You want a column icon
   • Create an advanced rule using the template, but return the image name instead of 'Yes' or 'No'
   or
   • Create a composite column using the above template Use this column in a column icon rule looking for the value 'Yes'. You might want to hide this column in the booklist and book details.
3. You want both. Do the composite column in option 1 then use that column in the second choice for the column icon in option 2.

[Comfy.n]

Originally Posted by chaley

You can search a note using a template like this one, suitably modified.

Is it possible, currently, to run that on the Advanced Search template field?

I get "EXCEPTION: string indices must be integers, not 'str'" in the Template Editor.

I'm trying with: Comparison type - Text, and pasting the code in field Template, while leaving the field Template Value empty.

[chaley]

Originally Posted by Comfy.n

Is it possible, currently, to run that on the Advanced Search template field?

I get "EXCEPTION: string indices must be integers, not 'str'" in the Template Editor.

I'm trying with: Comparison type - Text, and pasting the code in field Template, while leaving the field Template Value empty.

Yes, but the IMPORTANT part of my post is relevant. Until the fix is in calibre source you must use

get_note('authors', first_author, '')

[chaley]

Originally Posted by chaley

IMPORTANT: While testing this I found a bug in the get_note() function. The 'plain_text' option doesn't work. I have submitted a fix to Kovid. I will post here when the fix is in calibre source. In the meantime use HTML instead of plain text (the third parameter to get_note() is ''). It will be significantly slower but it should work as long as what you are looking for doesn't resemble an HTML tag too closely.

The fix is now in calibre source. If you run from source you can now use

get_note('authors', first_author, 1)

and not get the exception. If you don't run from source you must wait until the next calibre preview/release.

[Comfy.n]

Originally Posted by chaley

The fix is now in calibre source.

It works in the template editor, showing "Yes" for a given value when a book whose author's notes contain the string is currently selected.

However when I paste the template in the search bar, it returns no results, so I must be doing something the wrong way

[chaley]

Originally Posted by Comfy.n

It works in the template editor, showing "Yes" for a given value when a book whose author's notes contain the string is currently selected.

However when I paste the template in the search bar, it returns no results, so I must be doing something the wrong way

Works for me when using the advanced search template search builder running from latest source (with the fix). This example finds any note containing the letter 'a'.

Template:

program:
	first_author = sublist($authors, 0, 1, '&');
	note = get_note('authors', first_author, '1');
	if 'a' in note then
		ans = 'Yes'
	else
		ans = ''
	fi;
	ans

The template search dialog:


Note that the template search generated by the dialog contains more than just the template.

[Comfy.n]

Originally Posted by chaley

Create a composite column using the above template, with "show checkmarks" True.

That works fine,



Now I'm trying to fix my advanced search issue, hopefully I can come up later with more detailed info. I think my notes database has grown too much lately, and when I do the search as indicated in your screenshot for Advanced Search, Calibre enters some seemingly infinite database lookup.

This has happened other times and I solved it by recreating my Bio column. (deleting it, then re-adding). Today this didn't seem to solve the issue. Process Monitor shows this:

[chaley]

Originally Posted by Comfy.n

Now I'm trying to fix my advanced search issue, hopefully I can come up later with more detailed info. I think my notes database has grown too much lately, and when I do the search as indicated in your screenshot for Advanced Search, Calibre enters some seemingly infinite database lookup.

Your problem is almost certainly provoked by the size of your library. The template I posted above is not optimized for looking at >70,000 books, which is IIRC what you have in your library. It does a notes lookup for each book in the library. If that process takes 2 milliseconds per book then the search will run for 140 seconds. And who knows if 2ms is anywhere close to right.

This python template is optimized for the search problem. When it is first called (the first book) it gets all the note values and caches them using more optimized API calls. Then for each subsequent book it checks the cache, not the database. It would be best done as a stored template so the field and value could be passed as arguments. I didn't bother to do that because I don't know if this solves your problem.

The template:

python:
def evaluate(book, context):
	# Set these to what you want
	field_name = 'authors'
	search_value = 'aaa'

	db = context.db.new_api

	# check if we have already cached the notes
	note_items = context.globals.get('items_with_notes', None)
	if note_items is not None:
		# We have. Get the cached note values
		note_values = context.globals['note_values']
	else:
		# We haven't. Cache the note item ids and their values
		# First get all the item ids with notes and cache the result
		note_items = db.get_all_items_that_have_notes(field_name)
		context.globals['items_with_notes'] = note_items

		# Now get the note values for each item id with a note
		note_values = {}
		for note_item in note_items:
			note = db.notes_data_for(field_name, note_item)
			if note:
				# Get the plain text of the note
				note = note['searchable_text'].partition('\n')[2]
			# Put the value of the note into the cache.
			note_values[note_item] = note
		# Write the cached values to the globals
		context.globals['note_values'] = note_values

	# Check if this book is a match -- the field has a note containing the right text
	# get the item_id for the value of the desired field
	fv = book.get(field_name)
	# if the field is multi-valued, use the first value
	if isinstance(fv, list):
		fv = fv[0]
	# Now get the internal ID of the value in field_name
	item_id = db.get_item_id(field_name, fv)

	# Return the empty string if the item doesn't have a note
	if item_id not in note_items:
		return ''

	# Get the note value from the cache	
	val = note_values.get(item_id, None)
	if val is None: # This shouldn't happen, but ...
		return ''

	# use a case insensitive compare to check if the search value is in the note
	from calibre.utils.icu import primary_contains
	return 'Yes' if primary_contains(search_value, val) else ''

[Comfy.n]

Originally Posted by chaley

It would be best done as a stored template so the field and value could be passed as arguments. I didn't bother to do that because I don't know if this solves your problem.

Well, it took a while but it did complete the query, this time. I haven't timed it, but it finished much sooner than expected, considering my previous attempts.

Thanks for taking your time to try and help!

I had thought of asking for a stored template like "item_has_notes", but I think this is not the case at the moment.

[chaley]

Moderator Notice
Moved to it's own thread

[chaley]

I've thought of a way to significantly speed up the search template, calculating the text search result once per author instead of for every book. I'll post the new version later today.

[chaley]

Here is the improved template. I'd be interested if you think it is faster.

python:
def evaluate(book, context):
    # Set these to what you want
    field_name = 'authors'
    value_in_note = 'a'

    db = context.db.new_api

    # check if we have already cached the notes
    note_items = context.globals.get('items_with_notes', None)
    if note_items is not None:
        # We've already fetched which items have notes.
        # Get the cached search result values
        note_search_results = context.globals['note_search_results']
    else:
        # First time. Get the items with notes and initialize
        # the search value cache.
        note_items = db.get_all_items_that_have_notes(field_name)
        context.globals['items_with_notes'] = note_items
        note_search_results = {}
        context.globals['note_search_results'] = note_search_results

    # Check if this book is a match -- that the field has a note containing
    # the desired text.
    
    # We must first get the item_id for the value in the field to be checked.
    field_value = book.get(field_name)
    # if the field is multi-valued, use the first value
    if isinstance(field_value, list):
        field_value = field_value[0]
    # Now get the internal ID of the value in field_name
    item_id = db.get_item_id(field_name, field_value)

    # Does the item have a note? If not, give up now.
    if item_id not in note_items:
        return ''

    # The item has a note. Have we already checked it?
    if item_id not in note_search_results:
        # Item has a note but we haven't seen it before. Do the compare
        # on the plain text version of the note.
        result = ''
        # Get the note.
        note = db.notes_data_for(field_name, item_id)
        if note:
            # Get the plain text of the note.
            note = note['searchable_text'].partition('\n')[2]
        if note:
            # use a case insensitive compare to check if the search value is in the note
            from calibre.utils.icu import primary_contains
            result = 'Yes' if primary_contains(value_in_note, note) else ''
        # Cache the result of the comparison
        note_search_results[item_id] = result
        context.globals['note_search_results'] = note_search_results
    # Return the cached value
    return note_search_results.get(item_id, '')

[Comfy.n]

Originally Posted by chaley

Here is the improved template. I'd be interested if you think it is faster.

Today on v7.18, it took about the same time as yesterday while running from source, on a timed attempt. ~ 12 minutes.