Missing Media - Create Group
thejerrybryan
I recall seeing a post awhile back from your about this topic -- I experimented with Creating a group for missing media by fact. The code is close I think to what might be needed. Given your skills to find problems -- I am sharing. I did by desc so the cavaet would caution needed if two facttypes have the same string of characters. Alternately, you could instead use the factypeID. Although it would have less readability -- let me know your thoughts.
-- Create Named Group if it does not exist 'SQL: Media Missing DEATH'
INSERT OR IGNORE INTO TagTable
VALUES
(
(SELECT TagID FROM TagTable WHERE TagName LIKE 'SQL: Media Missing DEATH')
,0
,(SELECT IFNULL(MAX(TagValue),0)+1 FROM TagTable)
,'SQL: Media Missing DEATH'
,'SQLite query'
,julianday('now') - 2415018.5
)
;
-- Delete all members of the named group
DELETE FROM GroupTable
WHERE GroupID =
(SELECT TagValue
FROM TagTable
WHERE TagName
LIKE 'SQL: Media Missing DEATH'
)
;
-- Add members to the named group
INSERT INTO GroupTable
SELECT
null
,(SELECT TagValue
FROM TagTable
WHERE TagName
LIKE 'SQL: Media Missing DEATH'
)
,PersonID AS StartID
,PersonID AS EndID
,(julianday('now') - 2415018.5) AS UTCModDate
FROM
(
Select nt.OwnerID as PersonID
FROM EventTable ete
LEFT JOIN MediaLinkTable mlt ON (mlt.OwnerID = ete.OwnerID)
LEFT JOIN FactTypeTable ft ON (FactTypeID = ete.EventType)
LEFT JOIN EventTable etm ON (etm.OwnerID = Ete.OwnerID)
LEFT JOIN NameTable nt ON (nt.OwnerID = ete.OwnerID)
WHERE Name LIKE '%Draft%'
GROUP BY ete.EventID, FactTypeID
HAVING count(distinct mlt.MediaID) =0
)
While you've addressed this to @thejerrybryan I'll throw in my 2 cents.
I found your query showed me a couple of techniques I've not explored before.
- the count(distinct mlt.MediaID); i.e., the use of DISTINCT within the COUNT()
- HAVING ... one I'm aware of but not exploited or so rarely I've forgotten!
I don't follow the relationship between the Group name ("... Death") and the constraint "LIKE '%Draft%'". I'm guessing that was just a loss of coordination from playing around with the filter. You could use a runtime variable and a supporting sqlite manager to have the group name and the filter controlled by a user's single entry.
I'd suggest a couple of changes as shown below to:
- restrict to only Individual type facts; Family type requires additional handling.
- remove unneeded JOIN to EventTable and NameTable.
...
FROM
(
Select ete.OwnerID as PersonID
FROM EventTable ete
LEFT JOIN MediaLinkTable mlt ON (ete.OwnerID = mlt.OwnerID)
LEFT JOIN FactTypeTable ft ON (ete.EventType = FactTypeID)
WHERE ft.Name LIKE '%Death%'
AND NOT ft.OwnerType -- no Family-type facts
GROUP BY ete.EventID, ft.FactTypeID
HAVING count(distinct mlt.MediaID) =0
)
When I first built the select table (before adding the update part) I tested looking at name to make sure I was close. I forgot about the Owner type part. "HAVING" definitely comes in handy (Just have to remove the statement order which I usually forget) I have write very few update queries so this was my first dabble writing update query for groups (and joins). usually I write update on single table. Thanks for the feedback.
I have recently become well acquainted with the HAVING clause. It has to be used instead of the WHERE clause to access the results from the COUNT() function or other aggregate functions.
The HAVING clause essentially is just an alternate WHERE function, but semantically the value from the COUNT() function or other aggregate function is not yet available at the point in the script where the WHERE clause must appear. Also, the WHERE clause must appear before any GROUP BY clause. These two factors lead to the need for a HAVING clause to supplement the WHERE clause.
Here is a highly simplified example of how I have been using the HAVING clause in my scripts to compare RM7 and RM9 databases. Such a script assumes that an ATTACH has already been executed to attach both an RM7 database and an RM9 database.
SELECT COUNT(A.Version) AS V_count, A.Version, A.PersonID
FROM
(
SELECT 7 AS Version, P.PersonID
FROM RM7.PersonTable AS P
UNION
SELECT 9 AS Version, P.PersonID
FROM RM9.PersonTable AS P
) AS A
GROUP BY A.PersonID
HAVING V_Count != 2
The query will produce no results (the desired outcome) if RM7.PersonTable and RM9.PersonTable contain exactly the same PersonID rows. If either RM7 or RM9 has a PersonID row value that doesn't match a row in the other table, then the row with the unmatched PersonID will be output.
As far as the original question, I think I would do it a little differently. I think I would just identify the events without media as you already are doing and color code them in an otherwise unused color coding set in RM9. Color coding is so much easier in SQLite than is making a group. Then I would go into the RM9 user interface and make the group based on the color coding that you just accomplished.
I grant you that when a script is completed to do everything to make the group, it's easier just to run the script to make the group than it is to run the script to color code and then to go into RM9 to make the group. But color coding from a script just seems less fraught that does making a group from a script.
I share Tom's concern about family facts. Again, I think that would be easier to deal with if you color code in the script instead of making a group.
Here's a sample script that I have tested which I think would do everything you need with respect to color coding and which handles both individual facts and family facts. I keep think that there has to be some way to enter the name of the fact only once, but this will work. Note that if you only want to do individual facts, you only need the first of the three subqueries that are joined together. The Marriage Record fact that I tested with is a custom fact in my database. You would obviously change it to whichever fact you are interested in. Color #1 is red, and you could pick any color. I picked color set #9, but you could pick any color set.
UPDATE PersonTable
SET Color9 = 1
WHERE PersonID IN
(
SELECT E.OwnerID AS RIN
FROM FactTypeTable AS FT
JOIN EventTable AS E ON FT.Name LIKE 'Marriage Record' AND E.OwnerTYPE = 0 AND E.EventType = FT.FactTypeID
LEFT JOIN MediaLinkTable AS ML ON ML.OwnerType = 2 AND ML.OwnerID = E.EventID
WHERE ML.LinkID IS NULL
UNION
SELECT F.FatherID AS RIN
FROM FactTypeTable AS FT
JOIN EventTable AS E ON FT.Name LIKE 'Marriage Record' AND E.OwnerTYPE = 1 AND E.EventType = FT.FactTypeID
JOIN FamilyTable AS F ON F.FamilyID = E.OwnerID
LEFT JOIN MediaLinkTable AS ML ON ML.OwnerType = 2 AND ML.OwnerID = E.EventID
WHERE ML.LinkID IS NULL
UNION
SELECT F.MotherID AS RIN
FROM FactTypeTable AS FT
JOIN EventTable AS E ON FT.Name LIKE 'Marriage Record' AND E.OwnerTYPE = 1 AND E.EventType = FT.FactTypeID
JOIN FamilyTable AS F ON F.FamilyID = E.OwnerID
LEFT JOIN MediaLinkTable AS ML ON ML.OwnerType = 2 AND ML.OwnerID = E.EventID
WHERE ML.LinkID IS NULL
)
I realize that the script above looks silly with respect to the first subquery looking for a Marriage Record fact type with E.OwnerType = 0. Similarly, the second and third subqueries would look silly if they were looking for a Death fact type with E.OwnerType = 1. But I wanted just one overall query. The subqueries that don't make sense for any particular FactTypeTable.Name simply don't return anything.
Ah yes I believe this would be easier to use color -- then you could create groups based on need, If Fact Exist & Color Red for example. I will play with that.
none of my other groups use red -- because I save RED for problems.
Do make note that the script I posted updates color set 9 if you use it as written. If you want to keep all your colors in the same set, you can just change Color9 to Color. Also, I attached a new version of the same basic script that's organized so you only have to change the name of the fact type in one place.
Uploaded files:Thanks ! That is great -- I can adapt and use!
thank you.