Advanced query (db): all contacts with no interaction for n days

Questions & Help Queries
,
1

Hello,

I’m trying to write an advanced query but I’m struggling.

I have 2 new tags : #contact and #interaction.

#interaction has a property with which contains a list of #contact nodes.
#contact has a property max-days which is a number.

I would like to list all contacts which have no interaction in the last max-days days. The day of the #interaction should be considered to be the journal day in which the #interaction block is.

I would know how to do it with SQL (Will it be possible in the future, now sqlite is used?).
But with the query language of logseq, I’m struggling.

I tried by myself then I tried to do it with the help of AI but nothing ever works.

Here a by-AI query (not working at all!):

[:find (pull ?c [*])
 :where
 ;; Get all contacts
 [?c :block/tags ?tag]
 [(= ?tag "#contact")]

 ;; Get the max-days property
 [?c :block/properties ?props]
 [(get ?props :max-days) ?maxDays]

 ;; Get last interaction date with this contact
 (or-join [?c ?maxDays ?lastDate]
   ;; Case 1: There is at least one interaction
   (and
     [?i :block/tags ?itag]
     [(= ?itag "#interaction")]
     [?i :block/properties ?iprops]
     [(get ?iprops :with) ?withList]
     [(contains? ?withList ?c)]
     ;; Get journal date
     [?i :block/journal-day ?lastDate])
   ;; Case 2: There is no interaction
   (and
     [(missing? ?c :interaction)]
     [(identity nil) ?lastDate])
 )

 ;; Calculate days since last interaction
 [(if ?lastDate
       (let [today (js/Date.) 
             last (js/Date. ?lastDate)
             diff-ms (- (.getTime today) (.getTime last))
             diff-days (/ diff-ms 86400000)]
         diff-days)
       9999) ?daysSince]

 ;; Only include contacts exceeding max-days
 [(>= ?daysSince ?maxDays)]
]

Anyone to help me?

(Is it at least possible to get debug information somewhere to identify where the problem is?)

Thanks!