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!

2

Welcome.

  • You are asking for a lot at once.
    • Should have tried easier steps before.
  • Expect the answer to need considerable effort before it works in your case:
    {:query
 [:find (pull ?c [*])
   :in $ ?day-ago ?week-ago ?month-ago ; match to :inputs
   :where
     ;; match to :in
     [?ago-1 :block/title "1 day ago"]
     [?ago-7 :block/title "7 days ago"]
     [?ago-30 :block/title "30 days ago"]
  
     [?c-tag :block/title "contact"]
     [?i-tag :block/title "interaction"]
  
     [?c :block/tags ?c-tag]
     [?c :user.property/ago-cNiwCclG ?max-days] ; replace cNiwCclG with own value
  
     ;; match to :in
     (or-join [?day-ago ?week-ago ?month-ago ?ago-1 ?ago-7 ?ago-30 ?max-days ?days-ago]
       (and
         [(= ?max-days ?ago-1)]
         [(* 1 ?day-ago) ?days-ago]
       )
       (and
         [(= ?max-days ?ago-7)]
         [(* 1 ?week-ago) ?days-ago]
       )
       (and
         [(= ?max-days ?ago-30)]
         [(* 1 ?month-ago) ?days-ago]
       )
     )
  
     (not
       [?i :user.property/with-PcEx77FX ?c] ; replace PcEx77FX with own value
       [?i :block/tags ?i-tag]
  
       [?i :block/page ?p]
       [?p :block/journal-day ?last-date]
       [(>= ?last-date ?days-ago)]
     )
 ]
 :inputs [:-1d :-7d :-30d] ; replace with own values
}
    • This is for hypothetical days of no interaction 1, 7 and 30.
  • You need to:
    • pay attention to the comments
    • make a change that should not be needed, but in the current state of test.logseq.com it is:
      • Introduce in your graph the nodes right under line ;; match to :in
      • Instead of number-property max-days, use a node-property with values the nodes of the previous point.
        • In this case I have named it ago, but it can be given any available name.
3

I know my question is a lot at once :sweat_smile: Thank you for your reply, it helps me a lot.