Dataviewjs How to filter out the same value from all fields?

[Funyuneun]

Sorry, my native language is not English, so I can only use examples to express what I mean

Here’s what I’m hoping to achieve, thanks a lot!

---

【file1.md】
foodname: rice
like: A、b
hate: x、z

【file2.md】
foodname: soup
like: b
hate:A、z

【file3.md】
foodname: noodle
like: A

【dataviewjs.md】

A’s dietary preferences

preference	food
like	rice、noodle
hate	soup

[holroy]

It’s easiest using dataview to produce the alternate table with the various food as rows, having like and hate as columns, instead of doing like and hate as rows, collating the files having those values. So here is the easiest solution:

## A's dietary preferences
```dataview
TABLE WITHOUT ID link(file.link, foodname) as Food, A_likes as Likes, A_hates as Hates
FLATTEN choice(contains(like, "A"), "❤️", "") as A_likes
FLATTEN choice(contains(hate, "A"), "⛔", "") as A_hates
WHERE file.folder = this.file.folder 
WHERE foodname AND (contains(like, "A") OR contains(hate, "A"))
```

Which produces this output:

With preferences as rows

I’m not sure how to do this variant with a pure DQL query, if it’s at all possible, so for me it’s easier to switch to dataviewjs, but it’s not as nice looking as the previous query. (Could possibly be a little nicer, but I need to think on that for a little while… )

So here is the code:

```dataviewjs
const person = '"A"'
const result = await dv.query(`
  TABLE WITHOUT ID link(file.link, foodname) as Food, likes, hates
  FLATTEN choice(contains(like, ${ person }), "Yes", "No") as likes
  FLATTEN choice(contains(hate, ${ person }), "Yes", "No") as hates
  WHERE file.folder = this.file.folder 
  WHERE foodname AND 
    ( contains(like, ${ person }) OR 
      contains(hate, ${ person }) )
`)

if ( result.successful ) {
  const values = result.value.values
  
  let likes = []
  let hates = []
  values.forEach(i => {
    if ( i[1] == "Yes" )
      likes.push( i[0] )
    if ( i[2] == "Yes" )
      hates.push( i[0] )
  })
  
  dv.table(["preference", "food"],
    [ 
      [ "like", likes.join(", ") ],
      [ "hate", hates.join(", ") ]
    ])
} else
  dv.paragraph("~~~~\n" + result.error + "\n~~~~")
```

Which produces this output:

[holroy]

Note that your syntax in the files needs to be changed to be actual lists, and not just one string, so in both my previous reply, and this reply I used this syntax in my test files:

---
like: [b]
hate: [A, z]
---

If you don’t do that, it’ll try to do partial matches agains the entire string, which could lead to false positives.

Alternate dataviewjs solution

I also wrote this variant as a solution to your original request:

```dataviewjs
const person = 'A'
const foodPages = dv.pages().where(p => p.foodname)
const likes = foodPages
  .where(p => p.like && dv.func.contains(p.like, person))
  .map(p => dv.func.link(p.file.link, p.foodname))
const hates = foodPages
  .where(p => p.hate && dv.func.contains(p.hate, person))
  .map(p => dv.func.link(p.file.link, p.foodname))

dv.table(
  ["preference", "food"],
  [ [ "like", likes.join(", ") ],
    [ "hate", hates.join(", ") ]
  ]) 
```

Which still produces the wanted output of:

[Funyuneun]

@holroy

```dataviewjs
const person = 'A'
const foodPages = dv.pages().where(p => p.foodname)
const likes = foodPages
  .where(p => p.like && dv.func.contains(p.like, person))
  .map(p => dv.func.link(p.file.link, p.foodname))
const hates = foodPages
  .where(p => p.hate && dv.func.contains(p.hate, person))
  .map(p => dv.func.link(p.file.link, p.foodname))

dv.table(
  ["preference", "food"],
  [ [ "like", likes.join(", ") ],
    [ "hate", hates.join(", ") ]
  ]) 

Thanks for your patient guidance, the code worked successfully!

I have reasons why I need to do this, but maybe it’s like you said

It may be better to change a thinking logic
It’s just that I’m not familiar with JS and SQL, and I didn’t think of a better way