Thanks for the reassurance, but I decided I didn't like doing it that way as changing the filter requires a redeploy of the py file each time.
I did a bit more hacking and found an even better way which allowed me to enter sql directly into the search field. I changed search(self, query) in database2 to run an sql query (in the same way refresh does) if the filter string begins with "JOIN "
Code:
def search(self, query):
if not query or not query.strip():
self._map_filtered = list(self._map)
return
if query.find("JOIN ") != -1:
temp = self._db.conn.get('SELECT DISTINCT meta.* FROM meta ' + query)
self._data_filtered = list(itertools.repeat(None, temp[-1][0]+2)) if temp else []
for r in temp:
self._data_filtered[r[0]] = r
self._map_filtered = [i[0] for i in self._data_filtered if i is not None]
self._map_filtered = [id for id in self._map if id in self._map_filtered]
return
matches = sorted(self.parse(query))
self._map_filtered = [id for id in self._map if id in matches]
I also changed refresh to store db in self._db so I can get at it.
I can then just cut and paste a query such as the following to get what I want
Code:
JOIN books_authors_link ON books_authors_link.book=meta.id
JOIN (
SELECT DISTINCT books_authors_link.author AS author_filter
FROM tags
JOIN books_tags_link ON tags.id=books_tags_link.tag
JOIN books ON books_tags_link.book=books.id
JOIN books_authors_link ON books.id=books_authors_link.book
WHERE tags.name="my tag"
) ON author_filter=books_authors_link.author
I suspect the last two lines of code in my if JOIN bit - the copy from data into map and the sort - can probably be combined into one but as I have never programmed in python (just C, C++, Ada, Pascal, Java, Perl, bash ... ahem
) I've just stuck to simple cut&paste editing.
P.S. I'm just posting this for public interest ... it's not a feature request as I suspect most people wouldn't want / need it, I'm just awkward
