How do I filter for people with no marriage? There is a family filter which sounds like it would do the trick (guess it translates to ‘people without marriages’ in english) but it does not work because it seems to not find people in a family in state “married” but without any marriage event. So what I want is to select all people without a marriage event(!) no matter if they are in a family or not. How to achive that?
My greater goal is to find all events between 1500 and 1880 which have no citation and are related to people which (died before 1880 or not died at all) and (married before 1880 or not married at all) and (have a residence before 1880 or no residence at all). Currently I did it with SQL but I would like to get a gramps filter for that. But can’t because of the above mentioned problem. The absence of personal or family events seems not to be supported in general.
my SQL:
WITH marriage AS (
SELECT a.handle
,json_extract(f.json_data,'$.date.dateval[2]') marriage_year
FROM person a
JOIN family d ON d.father_handle=a.handle OR d.mother_handle=a.handle
JOIN reference e ON d.handle = e.obj_handle AND e.ref_class='Event'
JOIN event f ON e.ref_handle = f.handle
WHERE json_extract(f.json_data,'$.type.value') = 1
), death AS (
SELECT a.handle
,json_extract(c.json_data,'$.date.dateval[2]') death_year
FROM person a
JOIN reference b ON b.obj_handle=a.handle AND b.ref_class='Event'
JOIN event c ON b.ref_handle = c.handle
WHERE a.death_ref_index != -1 AND json_extract(c.json_data,'$.type.value') = 13
), residence AS (
SELECT a.handle
,json_extract(c.json_data,'$.date.dateval[2]') residence_year
FROM person a
JOIN reference b ON b.obj_handle=a.handle AND b.ref_class='Event'
JOIN event c ON b.ref_handle = c.handle
WHERE json_extract(c.json_data,'$.type.value') = 42
)
SELECT
'^('||group_concat(aa.gramps_id,'|')||')$'
-- aa.surname
-- ,aa.given_name
-- ,aa.gramps_id
-- ,json_extract(ae.json_data,'$.date.dateval[2]')
FROM person aa
LEFT OUTER JOIN marriage ab ON aa.handle=ab.handle
LEFT OUTER JOIN death ac ON aa.handle=ac.handle
LEFT OUTER JOIN residence ag ON aa.handle=ag.handle
JOIN reference ad ON aa.handle=ad.obj_handle AND ad.ref_class='Event'
JOIN event ae ON ad.ref_handle = ae.handle
LEFT OUTER JOIN reference af ON af.obj_handle = ae.handle AND af.ref_class='Citation'
WHERE (ab.marriage_year <= 1880 or ab.marriage_year is null)
AND (ac.death_year <= 1880 or ac.death_year is null)
AND (ag.residence_year <= 1880 or ag.residence_year is null)
AND json_extract(ae.json_data,'$.date.dateval[2]') between 1 and 1880
AND af.ref_class is null;
currently I copy the resulting string as regexp for the persons filter ID in the persons view of gramps
You are correct. This filter finds people without a family relationship to any Spouse.
To find two people in a Family without a marriage event…
Create a Family filter with the Event filters rule: Families with the <event>. Set the Family event to Marriage. In the Define Filter window, select the option Return values that do not match the filter rules.
Now create the Person filter with the General filters rule: People who are part of families matching <filter> Select the filter you created in Families and deselect Include Children
Things to know when filtering on date information.
When setting the filter rule, it is best to use between <date1> and <date2>. If you use a rule before <date> the Limit you have set in Preferences will be used. Instead of returning every event before the date in the rule, the range set in Limits (default of 50 years) will be returned.
Likewise, an event with a date before <date>, after <date>, or about <date> will return as true for a date filter if the event date and the limit set in Preferences (default 50 years) brings the possible event date into the filter range. An example: A filter wants births that occurred in 1900. An event with the date before 1945 would return as valid for the filter.
FYI: I have set my about range to 4 years and the before and after ranges to 9 years.
Yes - I always use between X and Y for such searches because I have set my about range to just 1 (for other reasons).
I’ll try your family filter suggestion but since my upgrade from 6.0.3 to 6.0.5 the filters in Gramps became so slow it renders them close to unusable. No idea what was done between those two version but man…. it’s slower than slow. 6.0.3 was slow already but 6.0.5 is another league.
Several filter-related issues and bugs were fixed in 6.0.4, 5, and 6. You can find a list of changes in each release on the Gramps GitHub releases page.
The performance regression you mentioned hasn’t been reported yet, so it would be great to start a new thread on that with more details, and examples. It would be best if the performance problem can be reproduced with the Gramps sample family tree to it can be duplicated easily. Thanks.
Manually tested serveral 6.0.x Gramps versions (flatpak) and can’t reproduce it with at least simple filters. Didn’t investigated it any further. Might be just a misalignment of my perception
I assume the filtering is stil done in Python without any prefiltering SQL-wise? Its definitly a huge task, but using SQL for processing the filters might result in massive performance gains.
For example my filter in SQL utilizing the sqlite json functions (PostgreSQL hast json functions as well)
select * from event where json_extract(json_data,'$.type.value') = 22 and json_extract(json_data,'$.date.dateval[2]') between 1881 and 1885
[....]
Run Time: real 0.138 user 0.109184 sys 0.020986
