Locate iMessages with unanimous reactions

query.sql

-- run with `sqlite3 -readonly ~/Library/Messages/chat.db`
with top as (
  select
    chat.display_name,
    associated_message_guid,
    associated_message_type,
    count(*) -- count(*)
  from
    message
    left outer join handle on handle_id = handle.rowid
    inner join chat_message_join on chat_message_join.message_id = message.rowid
    inner join chat on chat.rowid = chat_message_join.chat_id
  where
    -- These indicate reactions
    associated_message_type >= 2000
  group by
    1,
    2,
    3
  having
    count(*) >= 3
  order by
    4 desc
)
select
  display_name,
  case
    -- https://github.com/ReagentX/imessage-exporter/blob/9862a2b46afbeb8ad63043c02060e0fe7acd156f/imessage-database/src/tables/messages.rs#L867-L871
    when top.associated_message_type = 2000 then 'Loved'
    when top.associated_message_type = 2001 then 'Liked'
    when top.associated_message_type = 2002 then 'Disliked'
    when top.associated_message_type = 2003 then 'Laughed'
    when top.associated_message_type = 2004 then 'Emphasized'
    when top.associated_message_type = 2005 then 'Questioned'
    else top.associated_message_type
  end kind,
  -- nanoseconds since January 1, 2021, because Core Data
  -- https://github.com/ReagentX/imessage-exporter/blob/9862a2b46afbeb8ad63043c02060e0fe7acd156f/imessage-database/src/util/dates.rs#L4C82-L4C99
  DATETIME(
    ROUND(date / 1000000000) + 978325200,
    'unixepoch'
  ) date,
  -- Quick and dirty NSArchive parsing https://github.com/ReagentX/imessage-exporter/blob/9862a2b46afbeb8ad63043c02060e0fe7acd156f/imessage-database/src/util/streamtyped.rs#L9-L17
  cast(
    substring(
      attributedBody,
      instr(attributedBody, x '012b') + 3,
      instr(attributedBody, x '8684') - instr(attributedBody, x '012b') - 3
    ) as char
  ) msg,
  guid,
  handle.id
from
  top
  inner join message on message.guid = substr(top.associated_message_guid, -36)
  left outer join handle on handle_id = handle.rowid
order by
  date desc