Dataview query WHERE is not

What I’m trying to do

I’m trying to filter project notes that contain some properties and that doesn’t contain other:
filter projects that are not completed, whose “type” property is “project” and whose “status” is NOT “done”

Things I have tried

This is my query:

status AS "Status",
priority AS "!!!",
choice(length(filter(file.tasks, (x) => all(x.text, !x.completed))), "–", "Yes") AS "Needs tasks"
FROM #agenda
WHERE !completed AND type = "project" AND status != "done"
SORT priority ASC

In the WHERE clause the first two criteria work while the third doesn’t.
I guest it has to do with the way values are written in YAML: the “status” property is an array so I guest I shouldn’t use the “!=” and use the “contains” but I can’t figure out how
Could somebody help? Thanks!

try !status = instead of status !=

You say that status could be an array, but you don’t say which values it can hold and what qualifies for it not to be done. Should all values in a given array/note be different from done? Just one?

It doesn’t work unfortunately, I checked dataview and “is not” is actually “!=” thanks anyway!

Thanks @holroy, maybe “array” is not the correct word…sorry!
Here’s how appears the property in source mode:

  - done

The property is set to be a “list” type; I use four statuses (in-progress; on-hold; done; waiting-on) and every note can have just one status.
What I’m trying to achieve is to have notes that are tagged as “project” and whose status is not “done”. Is it clear? (sorry I’m not a native speaker) :upside_down_face:
Thanks for your help

Try AND !contains(status,"done").

1 Like

The lazy approach would then be to do status[0] != "done". This should cover most of your cases, but it’ll get a problem if in some notes status is of the list type. This checks the first item in the list

A next step could be flat(list(status))[0] != "done", where we enforce also the single values to become a list so that we could check the first item.

A full blown solution would enforce the list, and map each status, and require them all to not match “done”. Something like:

none(map(  flat(list(status)), (s) => s = "done"))

But this is a bit of a stretch for something which could/should have just been: status != "done" given that status was just text, and not a list type.

Sorry if this confuses you more than educates you… :slight_smile:

Thanks so much @holroy,
I’d say that’s just the opposite: it doesn’t confuse me, it helps me understand, I appreciate!
So to make it clear: I tried the first option and it works perfectly but let me see if I understand correctly:
status[0] != "done". I guess that means: in a property that is a list (in terms of the new obsidian properties) get the first item (“[0]”) and this first item mustn’t be “don”. Is it correct?

I don’t know where to put the other two options inside the query, should I put them in the “WHERE” clause? and if yes, how? Could you explain how the two other options would be better, provided that the “status” property should always be a list?
Thanks so much


The other two options could replace the status[0] != "done" part, but are more verbose and thorough. They counter for the following variants:

- todo
- done
status: done

If you imagine doing this query against multiple files, either variant could happen in some of the files, and the first naive options would fail for both of these variants. If however, you used some of the alternate versions, you’d counter for some of the possible scenarios. With having the status: done case is the most pressing one, I think. It’s too easy to have the status in one of the files suddenly become a single value.

A more complete test note

In the note below, which you could copy to your vault and fiddle around with, you could see varying cases on checking for “done” against either a list or a single value.

I’m using list( this.singleton, this.status) to combine the two variant into the same, which would be similar to grouping on the same name, or looking at it from a multi file view point. this. is needed to just pick the values from this current file in an inline query.

In the last query I do some magic to keep the lists as list, when combining list(flat(list(...))), just to keep the combined values as a values when intermediate storing them using FLATTEN. Focus rather on the table columns, and know the values of values and tests are as in the previous query.

Tags: f74678
- done
singleton: done

## Naive approach

first list item: `= this.status[0] = "done" ` – OK

as a singleton: `= this.singleton[0] = "done" ` – It _only_ compares against the first letter: `d`, and that's not `done`
correct for a single value: `= this.singleton = "done" ` – Now it compares against the entire string

## List combinations

combined list
LIST WITHOUT ID list(this.status, this.singleton)
WHERE file = this.file

flattened combined list:
LIST WITHOUT ID flat(list(this.status, this.singleton), 4)
WHERE file = this.file

The order of the list shouldn't change the outcome:
- v1: `= list(this.status, this.singleton)[0] = "done" ` – This is false, since the first element is a list
- v2: `= list(this.singleton, this.status)[0]= "done" `  – This is true, since we now got the singleton first
 - Bad v1: `= list(this.status, this.singleton)[0][0] = "done" ` – This is true _only when the first element is a list_
 - Bad v2: `= list(this.singleton, this.singleton)[0][0] = "done" ` – This becomes false, since we know against the first character of the singleton

Back to the good using the flattened list, we can check the first item reliably:
- v1: `= flat(list(this.status, this.singleton))[0] = "done" ` 
- v2: `= flat(list(this.singleton, this.status))[0] = "done" ` 

No matter which variant is first, we check it.. But we do ignore the second variant all together. (Try changing either, and see the changes )

## Checking all values

mapped list check:
  flat(list(this.status, this.singleton)) as values,
  map( flat(list(this.status, this.singleton)), (m) =>
    m = "done" ) as tests
WHERE file = this.file

The final test shows the various alternatives after mapping:

  values, tests,
WHERE file = this.file
FLATTEN list(flat(list(this.status, this.singleton))) as values
FLATTEN list( map(values, (m) => m = "done")) as tests

Now go back, and change either value to something else, and see the changes. Or even add more values to the `status`. 

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.