Advanced queries for use with CRM

Look what I built
1

Here are some queries to be used with a CRM workflow.
The queries assume the following.

  1. People have their own pages
  2. People pages start with @
  3. People pages have a page property contact which lists a contact interval. E.g. weekly.

Last time contacted

#+BEGIN_QUERY
{:title [:h2 "Last contact"]
  :query [:find ?name ?interval (max ?day)
               :keys name interval day
               :where
                 [?jp :block/journal-day ?day]
                 [?mention :block/page ?jp]
                 [?mention :block/refs ?person]
                 [?person :block/original-name ?name] ; gets the name of the page as displayed in Logseq.
                 [?person :block/properties ?prop] ; I'm assuming the use of a page-property here. (i.e. first block on the page)
                 [(get ?prop :contact) ?interval]
  ]
  :result-transform (fn [result] 
    (sort-by 
      (juxt 
        (fn [i] (get i :interval)) 
        (fn [d] (get d :day)) 
      ) result)
  )
  :view (fn [rows]
    [:table 
      [:thead [:tr
        [:th "Name"]
        [:th "Interval"]
        [:th "Date"]
      ] ]
      [:tbody (for [r rows] [:tr
        [:td [:a {:href (str "#/page/" (get r :name))} (get r :name)]]
        [:td (get r :interval)]
        [:td (get r :day)]
      ] ) ]
    ]
  )
}
#+END_QUERY

People that haven’t been contacted longer than x days

#+BEGIN_QUERY
{:title [:h2 "Longer than 7 days"]
  :query [:find (pull ?person [*])
               :in $ ?dl
               :where
                 ; all people with a weekly contact interval
                 [?person :block/original-name ?name]
                 [?person :block/properties ?prop]
                 [(get ?prop :contact) ?interval]
                 [(= ?interval "weekly")]
                 ; who are not mentioned on journal pages of the last 7 days
                 (not [?mention :block/refs ?person]
                 [?mention :block/page ?jp]
                 [?jp :block/journal-day ?day]
                 [(>= ?day ?dl)])
  ]
  :inputs [:7d] ; only accepts dynamic days
}
#+END_QUERY

List of all people pages

#+BEGIN_QUERY
{:title [:h2 "Persons list"]
  :query [:find (pull ?p [*])
    :where
      [?p :page/name ?name]
      [(clojure.string/starts-with? ?name "@")]
      ; [?p :block/properties ?prop]
      ; [(get ?prop :contact)] ; optional, only needed if you have @ pages without property contact that you want to exclude.
  ]
}
#+END_QUERY
2

Example results:

image
image1536Ă—1633 140 KB

4 Likes
3

Really great - think you have saved me on a subscription on a CRM system I was using. Thank you !

2 Likes
4

Hi Siferiax, thanks for sharing your CRM setup.
In the comments you wrote that the input only accepts dynamic dates. I’m not entirely sure what that means. Ideally I would like to set the interval to a number and use that as the input. I haven’t been able to make that work. Is that because of the dynamic date?

5

In the spirit of CRM,what would also be great is to be able to see all open tasks where that person is tagged either with his name or his alias(es).

I tried it with a simple query but did not manage the aliases (except manually). I put this in a template for type person:

{{query (and (todo todo doing waiting) <% current page %>) }}

with manual addition for alias:

{{query (and (todo todo doing waiting) (OR [[Page title]] [[alias of page title])  )}}

Anyone feel free to comment on my simple queries, or let me know how to do this in advanced.

6

From the queries for task management topic:

1 Like
7

I don’t understand what you mean by interval number?
Do you mean that the contact property has as a value a number which is the number of days or weeks or…?
That’s complicated as we do not have “real” date values. We have integers we can compare, but we cannot do math with them.
20230228 - 28 days is 20230200 instead of 20230131.
You can do it less dynamically, meaning the value you compare against isn’t “weekly”, but “12” for example and your input is then :12d.
I hope this makes sense?

8

wow, that is great.

I just tried it , but it did not work for some pages as the status of the block is not TODO, but DOING or WAITING. Even better would probably be all blocks with a marker that is not DONE or CANCELLED.

How should I adapt this code to ensure this ( not DONE or CANCELLED).

thanks

9

Change it to this:

[?b :block/marker ?marker]
(not [(contains? #{"DONE" "CANCELLED"} ?marker)])

You can also remove the (not …) To make it an include list instead btw.

10

thanks a lot.
To be sure
only replace this part:

[?b :block/marker "TODO"]

with this:

[?b :block/marker ?marker]
(not [(contains? #{"DONE" "CANCELLED"} ?marker)])

with the same indentation, right.

Thanks again,
I hope to learn myself advanced queries some day.

11

Thanks, that does make sense. Too bad we can’t use :inputs [:contact] but this is still very useful.

1 Like
12

Yes! As for indentation…
This:

:where
     [?b :block/marker ?marker]
     (not [(contains? #{"DONE" "CANCELLED"} ?marker)])

And this:

:where
[?b :block/marker ?marker]
(not [(contains? #{"DONE" "CANCELLED"} ?marker)])

Is exactly the same :stuck_out_tongue:
It’s more about readability.

13

ah ok, thanks :smiley:

By the way, I see this only works if the page is referenced in the actual task, not if it is in the same block but higher. Let me explain

The TODO below would not appear:

  • blablabla [[Siferiax]]
    * TO DO blablablabla
    However this one does:
  • blablabla [[Siferiax]]
    * TO DO [[Siferiax]] blablablabla

Just to understand and be sure, I will just need to pay attention and mention it in the actual task.

14

We can implement a rule for that!

#+BEGIN_QUERY
{:title ["Query by page & alias"]
 :query [:find (pull ?b [*])
   :in $ ?page %
   :where
     [?b :block/marker "TODO"]
     [?p :block/name ?page]
     (or-join [?b ?p]
       (check-ref ?p ?b) 
       (and 
          [?p :block/alias ?a]
          (check-ref ?a ?b)
       )
     )
 ]
 :rules [
   [(check-ref ?p ?b)
     [?b :block/refs ?p]
   ]
   [(check-ref ?p ?b)
     [?b :block/parent ?t]
     (check-ref ?p ?t)
   ]
 ]
 :table-view? false
 :inputs [:current-page]
}
#+END_QUERY
15

thanks a lot, I’ll try to find out the differences, as I am trying to learn advanced queries.
Appreciate your help as always!

1 Like