Left Join, Right Join, Inner Join

Help
1

Introduction

Hello community. I’m a new obsidian user but I’ve managed to advance quite a bit in terms of generating advanced queries (metadata only, not metadataJS) and I need help with something that might yet not be implemented or, if it is, I believe it is not properly documented because I could not find any examples of this.

I have some types of notes, but I’ll focus on 3, each one has a hashtag to identify them properly. They are:

  • #task - a note that I treat as a task that might contain subtasks. It’s frontmatter is below, but for this purpose only the RequestedBy is important, so I’ll omit some information this time:
---
title:  Test task 1
RequestedBy:  [[Someone]]
tags: task
---
  • #person - a note which is a register of a person that works in my company or a third party one. It’s frontmatter (only what matters) and the most important field is Level in this case:
---
title:  Someone
WorksFor: "[[Company A]]"
Position: Account Manager
Level: "[[3 Manager]]"
tags: person
---
  • #level - a note which will contain a level, for example, if the person is a trainee, an operator, a manager, a president, etc. The most important field is Score:
---
title: 3 Manager
Score: 8
tags: level
---

What I’m trying to do

To wrap up, a task is requested by a person that has a level, so, if we treat each type of note as a table, we’ll have 3 tables that would be interconnected, so to say, and I would like to obtain, from a task, the level of the person that requested it; so:

  • Task has a requestor (person) that has a level

Things I have tried

The query is quite long, so I’ll just put here the part that matters for readability:

TABLE
	Status,
	RequestedBy AS ReqBy,
	Stakeholder AS StkH,
	WaitingWhom AS WaiWhom		
FROM
	#task OR
	#person OR
	#level 
WHERE
	Done = false AND
	BlockedByTask = NULL AND
	HideUntil < date(now)

So far so good as it works the way it is, but this is as far as I can go. I’ve tried (of course, with no success) to insert the following arguments after “WaitingWhom AS WaiWhom”:

RequestedBy.Level.Score
RequestedBy(Level(Score))

So, my question is: Is there a way to join tables like this at all? Thanks in advance.

1 Like
2

Hello community. Does anyone has any idea if what I requested is possible or not? Thanks in advance

3

Huh… I saw this some time ago, and thought with myself, I must look into this. Now I did, and it was easier than expected, and it comes down to how you define stuff in the frontmatter.

So for educational purposes, try the following in a file of its own:

---
fieldA: [[Someone]]
fieldB: "[[Someone]]"
---

```dataview
TABLE WITHOUT ID fieldA, typeof(fieldA), fieldB, typeof(fieldB)
WHERE file.name = this.file.name
```

Using the default theme (not the Minimal theme which hides some of this output):

image
image780×131 8.83 KB

FieldA is not a page link, but actually a nested array with one entry at the second level. (One level pr dot of the list).

FieldB on the other hand, is treated as a link by dataview, and when displayed you can see it’s displayed as a link, and the type is link.

So what I basically did was make test files according to your definitions, but knowing this already I made all the seemingly page links, into actual links (as seen by Dataview that is). Aka, I changed [[Someone]] into "[[Someone]]" for all of the [[...]] occurences.

Then I ran the following query:

```dataview
TABLE WITHOUT ID title, RequestedBy, RequestedBy.level.Score
WHERE econtains(file.etags, "#task")
WHERE file.folder = this.file.folder AND file.name != this.file.name
```

Note that the last WHERE clause is just related to my test query, and should be exchanged to suit your situation. This query then output the following:

image
image775×149 10.5 KB

And as can be seen, three levels down, with no issue.

Do also note that I do not target the person and level files in the query. I only start with the task, and build the query related to that information.

Bonus tip: How to present code properly in a forum post

If you want to showcase either markdown, or code blocks, or dataview queries properly in a forum post, be sure to add one line before and one life after what you want to present with four backticks, ````. This will ensure that any other backticks (like for code blocks) is properly shown.

4

Hello Holroy, thanks for clarifying.
I was able to reproduce the behavior I wanted thanks to you and it is basically solved, but now I have other smaller issue.
I’ll (try to) wrap up by showing the piece of code that is working as intended (and thanks for the tip on showing code properly when posting to forums):

choice(
	typeof(RequestedBy.level.Score) = "number",
	(RequestedBy.level.Score),
	(sum(RequestedBy.level.Score)/length(RequestedBy.level.Score)))

First of all, not too often but sometimes RequestedBy has more than one person and it returns an array type instead of a number. As this piece of code is part of a SUM function, it needs to return a number, not an array, so this piece of code makes sure that, if the typeof is a number, it’ll just return the plain number as it should and, in case it is an array, it takes a simple medium (the array sum divided by the number of occurrences) so it is a good solution

The problem comes when RequestedBy is empty, though. I’m not sure if this is a bug but, some reason, if RequestedBy is empty, the file is omitted from the resulting query. And I’m sure it is this piece of code because, if I remove it from the SUM, then it show all the other ones with RequestedBy empty. What is more strange to me is that it is just inside of a SUM function; there are no other filters set on my WHERE clause (check below by WHERE clause):

WHERE
	Done = false AND
	BlockedByTask = NULL AND
	HideUntil < date(now) AND
	!contains(Status,"meeting")

Holroy, do you have any idea on why it behaves like this and if there’s a solution?

Thanks a lot in advance :slight_smile:

5

I’ve seen this in some circumstances, and if the value for some conditions of FLATTEN and/or a WHERE clause is falsy, the lines is not included.

What proved to be icky in your situation is that sum(...) is valid to do on even an empty field, but if you do length( ... ) on that item, even within a choice() it turns out to be a falsy value, and the line is ignored.

However, there are ways around it, so try out the following in a note of itself, and you’ll see some of the cases and alternatives to get values out even when they are not considered truthy.


## Base query
```dataview
TABLE WITHOUT ID item[0], item[1], typeof(item[1])
FLATTEN list(
  ["none", null],
  ["one", 1],
  ["two", [20, 22]]
  ) as item
WHERE file.name = this.file.name
```


## Check all types + average
```dataview
TABLE WITHOUT ID item[0], item[1], typeof(item[1]), mySum
FLATTEN list(
  ["none", null],
  ["one", 1],
  ["two", [20, 22]]
  ) as item
FLATTEN
  choice( typeof(item[1]) = "number", item[1],
    choice( typeof(item[1]) = "array", average(item[1]),
      choice( !item[1] , 0, 999))) as mySum
WHERE file.name = this.file.name
```


## Default + average
```dataview
TABLE WITHOUT ID item[0], item[1], typeof(item[1]), mySum
FLATTEN list(
  ["none", null],
  ["one", 1],
  ["two", [20, 22]]
  ) as item
FLATTEN default(
  choice(
    typeof(item[1]) = "number",
    item[1],
    average(item[1])),
  0) as mySum
WHERE file.name = this.file.name
```


## Just average
```dataview
TABLE WITHOUT ID item[0], item[1], typeof(item[1]), mySum
FLATTEN list(
  ["none", null],
  ["one", 1],
  ["two", [20, 22]]
  ) as item
FLATTEN
  choice(
    typeof(item[1]) = "number",
    item[1],
    average(item[1])) as mySum
WHERE file.name = this.file.name
```

So my recommendation is to use either average() on its own, or in combination with default.

image
image860×627 16.7 KB

With default you’ll get a value when there’s no value for the field, and without you’ll just get an empty row returned.

6

Update:
I was thinking that, perhaps when RequestedBy is empty, not only it is NULL but also RequestedBy.level.Score is NULL. I thought that, because this is not tested, it would result in my resulting table omitting files that has the column RequestedBy empty, so I tried the solution below:

choice(
	RequestedBy.level.Score = NULL,
	0,
	choice(
		typeof(RequestedBy.level.Score) = "array",
		(sum(RequestedBy.level.Score))/length(RequestedBy.level.Score),
		(RequestedBy.level.Score)
	)
)

However and, unfortunately, the output still omits files that has RequestedBy empty/Null.

So I still have this issue… thanks

7

Sorry, we basically posted almost at the same time. I’ll try to work with this and will let you know, thanks a lot!

8

No problem, I saw you was typing something, so I was kind of wondering what…

Of course you’ll need to adapt my queries into your scenario, but it should be as easy as replacing item[0] with RequestedBy.level.Score hopefully.

9

Hello again, @holroy .
I’ve tested and got an interesting result.

At first, I tried to apply average directly on my code after I understood the logic you presented. It worked as long as RequestedBy != NULL.

So, I’ve tried to adapt to my code following your suggestions, it did not work after some different attempts. So, I narrowed it down to the simplest implementation possible, which was copying your code the way it was with only examples (the version “Default + average” and working my way on to my implementation little by little and testing.

I came up with this result:

TABLE item[0], item[1], mySum
FLATTEN list(
	["Impact", Impact], 
	["Urgency", Urgency]
	) as item 
FLATTEN default(
	choice(
		typeof(item[0]) = "number",
		item[0],
		average(item[0])),
	0) as mySum
FROM
	#task OR
	#taskP

Now, I’ll comment:
I’ve tried to further simplify only for testing and to go towards my implementation better defining it, but it always gives me the error: Dataview: Unrecognized query operation ‘binaryop’

  • Impact and Urgency are number values; to simplify, I removed them and inserted fixed numerical values, but the same error is given as output, so I believe that’s not the issue
  • Even though the choice function would never be evaluated as other than number, returning the item specified, I’ve tried to put item[1] instead and even removing completelly the choice function and mySum, it returns the same error
  • the FROM is required so the code runs smoothly, but if removed… same error

So, I believe that the best alternative (unless you have yet another idea) is to use this version below, making sure that RequestedBy is never empty when creating a new note and redoing existing ones the same way. Check what I did that worked inside the sum() function (as long as RequestedBy != NULL (the integer numbers are there just to better illustrate the sum function without making the coode too polluted to read):

sum(
   3,
   4,
   choice(
	typeof(RequestedBy.level.Score) = "number",
	(RequestedBy.level.Score),
	average(RequestedBy.level.Score)
        ),
   6,
   7
)

So, to wrap up, it works for me this way unless you have another idea, so I’ll wait for your reply and, depending on what you say, we can consider this as resolved :slight_smile:

10

Some comments on your adaption

You can’t do FROM after FLATTEN. FROM needs to be the first thingy after the TABLE (or LIST or TASK) besides the column you want listed. This is most likely the cause of your error.

Secondly, in this example you’re splitting the text name versus the field name, and then you do item[0] which refers to the text name, so your definition of mySum will always be trying to do the average of a text name. That won’t work.

It could work with item[1] where in half the cases it would use the Impact field, and the other half would use the Urgency field, and the resulting query would still be with twice as many rows, as if you hadn’t included that FLATTEN for item statement.

If you remove the FROM (and don’t do any WHERE clause), the FLATTEN for item is combined with every file in your vault. So that’s going to take some time to compute.

Back to the real question

I understand your original query is a mockup, but the order of the element do matter, so if we insert my last snippet, default+average, into that mockup, it’ll look like this, and that should work with both null, number and array values in RequestedBy.level.Score.

```dataview
TABLE
  Status,
  RequestedBy AS ReqBy,
  Stakeholder AS StkH,
  WaitingWhom AS WaiWhom,
  mySum as LevelScore
FROM
	#task OR
	#person OR
	#level 
FLATTEN default(
  choice(
    typeof( RequestedBy.level.Score ) = "number",
    RequestedBy.level.Score,
    average(RequestedBy.level.Score)),
  0) as mySum
WHERE
	Done = false AND
	BlockedByTask = NULL AND
	HideUntil < date(now)
```

I also do believe that last WHERE clause could be simplified to:

WHERE !Done AND !BlockedByTask AND HideUntil < date(now)

But do test the whole query first, and then change the WHERE clause if you feel like it.

11

Solved, solved, solved!!! That was it!!!

@holroy , you’re a genius!! Thank you so much!

1 Like
12

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