Help with search query

[KABx]

I’m sure this is very easy, but I can’t quite get it.....can anyone help with a couple of search queries?

I have 2 custom columns (both comma separated text, like tags, shown in the tag browser).

How do I do search for

columnA not = columnB

and

columnA contains columnB ?

[theducks]

Originally Posted by KABx

I’m sure this is very easy, but I can’t quite get it.....can anyone help with a couple of search queries?

I have 2 custom columns (both comma separated text, like tags, shown in the tag browser).

How do I do search for

columnA not = columnB

and

columnA contains columnB ?

Use the Tag Browser Luke

Set it to Match All at the bottom
Select the first column value (minus is Not)
hold the control key
Select the second column value (Minus is Not)

you should see something like this:

tags:"=Webscription" and  not #usertags:"=Not All Done Yet"

[chaley]

Calibre does not support search expressions referencing more than one column.

One way to do this in the GUI is to create a composite custom column (column built from other columns) that produces a true/false value based on the values in other columns. The computations can be slow, but it does work. For example, the first search would be done by creating a column that uses the list_equals function. Implementation of the second depends on exactly what you mean by 'contains'. If you mean 'all items in columnB are in columnA, then one would use both list_intersection and list_equals. If you mean 'any item in columnB is in columnA', then it would be list_intersection and count. If columnB contains only one value, then you can use in_list. The performance problems can be largely avoided by using custom template functions.

You could write a plugin that does the search. Such a plugin would be faster than custom template functions and would obviate the need for extra columns.

Finally, you can write SQL against calibre's DB. This would be complicated, both because getting the answers back into the GUI is hard and because list manipulation is not easy in SQL.

[KABx]

Originally Posted by theducks

Use the Tag Browser Luke

Set it to Match All at the bottom
Select the first column value (minus is Not)
hold the control key
Select the second column value (Minus is Not)

you should see something like this:

tags:"=Webscription" and  not #usertags:"=Not All Done Yet"

Calibre’s strength flows from the tag browser..... but it is not the solution this time

Originally Posted by chaley

Calibre does not support search expressions referencing more than one column.

One way to do this in the GUI is to create a composite custom column (column built from other columns) that produces a true/false value based on the values in other columns. The computations can be slow, but it does work. For example, the first search would be done by creating a column that uses the list_equals function. Implementation of the second depends on exactly what you mean by 'contains'. If you mean 'all items in columnB are in columnA, then one would use both list_intersection and list_equals. If you mean 'any item in columnB is in columnA', then it would be list_intersection and count. If columnB contains only one value, then you can use in_list. The performance problems can be largely avoided by using custom template functions.

You could write a plugin that does the search. Such a plugin would be faster than custom template functions and would obviate the need for extra columns.

Finally, you can write SQL against calibre's DB. This would be complicated, both because getting the answers back into the GUI is hard and because list manipulation is not easy in SQL.

....and there was me thinking it would be easy!

Give me an IBM mainframe and DB2 and i can manage SQL, but its a bit beyond me on a PC.... so is a calibre plugin. I'm still learning how to use them, can't cope with writing one too


I will have a play with the composite column route and see if i can work it out.

thanks for the suggestions.

[chaley]

Here is one possible template for the column used for the first search:

program: list_equals(field('#columnA'), ',', field('#columnB'), ',', 'yes', 'no')

[KABx]

Originally Posted by chaley

Here is one possible template a the column used for the first search:

program: list_equals(field('#columnA'), ',', field('#columnB'), ',', 'yes', 'no')

thanks - that was just what I needed to get me on the right track - i've not got the hang of the syntax of everything yet. Once I have an example, I can usually work it out.

I've got the A=B bit working now, I suspect the next one will be a bit harder