From time to time, I have to write my own queries using an exported database in SQLite. It’s a bit inconvenient to export the database and connect to it every time, although it’s not critical - I do it not every day 
. However, the queries I write for my specific purposes may also be useful to other Gramps users.
Therefore, I’m curious about how complicated it is to write your own queries to the database using the API or directly to the database? Here’s an example that I needed recently to prepare a complete list of people by tag with their full names, IDs, and dates of birth and death. This is to be able to post a list of residents of a certain locality on the forum for other genealogical researchers.
I’m not sure, but maybe there are already some plugin builders that allow constructing custom queries for individual tasks? Though I doubt that something like this can be implemented.
WITH PersonHandles AS (
SELECT p.handle AS person_handle
FROM person AS p
INNER JOIN link AS lpn ON p.handle = lpn.from_handle AND lpn.from_type = 'person' AND lpn.to_type = 'name'
INNER JOIN name ON name.handle = lpn.to_handle AND lpn.to_type = 'name'
INNER JOIN link AS lns ON name.handle = lns.from_handle AND lns.from_type = 'name' AND lns.to_type = 'surname'
INNER JOIN surname AS s ON s.handle = lns.to_handle AND lns.to_type = 'surname'
INNER JOIN link AS lpt ON p.handle = lpt.from_handle AND lpt.from_type = 'person' AND lpt.to_type = 'tag'
INNER JOIN tag ON tag.handle = lpt.to_handle AND lpt.to_type = 'tag'
WHERE tag.name = 'my tag'
AND p.private = 0
),
PersonDetails AS (
SELECT
person.handle AS handle,
person.private AS private,
person.gid,
name.first_name,
MAX(CASE WHEN surname.origin_type0 = 3 THEN surname.surname END) AS given_surname,
MAX(CASE WHEN surname.origin_type0 = 4 THEN surname.surname END) AS taken_surname,
GROUP_CONCAT(CASE WHEN surname.origin_type0 NOT IN (3,4) THEN surname.surname END, ', ') AS other,
joined_table_birth.date_text AS birth,
joined_table_death.date_text AS death
FROM link AS link_person_name
INNER JOIN person ON person.handle = link_person_name.from_handle AND link_person_name.from_type = 'person'
INNER JOIN name ON name.handle = link_person_name.to_handle AND link_person_name.to_type = 'name'
LEFT JOIN link AS link_name_surname ON name.handle = link_name_surname.from_handle AND link_name_surname.from_type = 'name'
LEFT JOIN surname ON surname.handle = link_name_surname.to_handle AND link_name_surname.to_type = 'surname'
LEFT JOIN (
SELECT
event.handle AS handle,
GROUP_CONCAT(date.text, ', ') AS date_text
FROM event
LEFT JOIN link AS link_event_date ON event.handle = link_event_date.from_handle AND link_event_date.from_type = 'event'
LEFT JOIN date ON link_event_date.to_handle = date.handle AND link_event_date.to_type = 'date'
GROUP BY event.handle
) AS joined_table_birth ON person.birth_ref_handle = joined_table_birth.handle
LEFT JOIN (
SELECT
event.handle AS handle,
GROUP_CONCAT(date.text, ', ') AS date_text
FROM event
LEFT JOIN link AS link_event_date ON event.handle = link_event_date.from_handle AND link_event_date.from_type = 'event'
LEFT JOIN date ON link_event_date.to_handle = date.handle AND link_event_date.to_type = 'date'
GROUP BY event.handle
) AS joined_table_death ON person.death_ref_handle = joined_table_death.handle
GROUP BY person.handle, person.gid, name.first_name, name.private
)
SELECT DISTINCT
person_details.gid || '. ' ||
COALESCE(person_details.given_surname, '') ||
CASE WHEN person_details.taken_surname IS NOT NULL THEN ' (' || person_details.taken_surname || ')' ELSE '' END || ' ' ||
COALESCE(person_details.first_name, '') || ' ' ||
COALESCE(person_details.other, '') || '. ' ||
'(Birth: ' || COALESCE(person_details.birth, '-') || '. Death: ' || COALESCE(person_details.death, '-') || ').' AS person_details_concatenated
FROM PersonHandles
LEFT JOIN PersonDetails AS person_details ON PersonHandles.person_handle = person_details.handle
WHERE (
strftime('%Y', 'now') - CAST(strftime('%Y', person_details.birth) AS INTEGER) >= 100
OR person_details.death IS NOT NULL
OR person_details.birth IS NULL
)
AND (
person_details_concatenated NOT LIKE '%surname1%'
AND person_details_concatenated NOT LIKE '%surname2%'
);