'Cross-table' queries

Hi all,
I am a doctoral student in anthropology and I would like to use Logseq as an ethnographic database.

I know that Logseq is not specifically designed to manage relational databases like MySQL or PostgreSQL, but these do not allow direct access to multimedia files unlike Logseq.

I would like to use Logseq to combine all my data (link to videos, scan of notes, recordings, photos…) in the same workspace and annotate them with properties and keywords, to then make queries.

Here’s the problem I’m having: I’m trying to do a query that’s similar to cross-table queries, a sort of nested query.

For example, each participant has properties: name, age, gender, residence, etc.

name:: John Doe
age:: 55
gender:: male
residence:: City A

And each observation (video, photo, notebook note, etc.) has properties: participants, keywords, location, etc.

Observation 1

participants:: [[John Doe]]
keywords:: Wood, Farm
location:: Location A

I would now like to do the advanced query “find among all my observations those where there are male participants over 50 years old”

any hint here?
thanks a lot

Welcome. Try something like this:

{:query [:find (pull ?o [*])
     [?p :block/original-name ?name]
     [?p :page/properties ?p-props]
     [(get ?p-props :gender) ?gender]
     [(= ?gender "male")]
     [(get ?p-props :age) ?age]
     [(> ?age 50)]
     [?o :block/name]
     [?o :block/properties ?o-props]
     [(get ?o-props :participants) ?participants]
     [(contains? ?participants ?name)]

It’s perfect !

From that I added the lines:

[(get ?o-props :tags) ?tags]
[(contains? ?tags "...")]

Which also allows me to search with tags.

Thank you for the precision and speed of the response, this will help me a lot in my research!

Consider also [?o :block/tags ...]