Group By on two levels

[ThurMeijer]

Hi (again),

I’ve got this database, and for an overview page, I’d like to group in two levels… Data is hierarchical. Let me give an example.

I’ve got my inline field as follows:
File 1

Network:: A
Website:: 1

File 2

Network:: A
Website:: 2

File 3

Network:: B
Website:: 3

File 4

Network:: B
Website:: 3

---

Now I would like to see a table something like:

Network	Website	Number of files
A	1	1
(empty but ‘A’)	2	1
B	3	2

---

How can I get this?

[mnvwvnm]

I can give you an approximated solution… but without hide repetitions in Network column:

TABLE WITHOUT ID
	rows.Network[0] AS Network,
	Web AS Website,
	length(rows) AS "Number of files"
WHERE Network
GROUP BY Website as Web

[mnvwvnm]

Or maybe this (I’m nor sure about this group by... + group by...

TABLE WITHOUT ID
	Net AS Network,
	rows.Web AS Website,
	map(rows, (r) => length(r.rows)) as "Number of files"
WHERE Network
GROUP BY Website as Web
GROUP BY rows.Network[0] as Net

[ThurMeijer]

@mnvwvnm

TABLE WITHOUT ID
	Net AS Network,
	rows.Web AS Website,
	map(rows, (r) => length(r.rows)) as "Number of files"
WHERE Network
GROUP BY Website as Web
GROUP BY rows.Network[0] as Net

Thanks @mnvwvnm, this is exactly what I was looking for!

I however look to learn from it, and not just only copy your solution.

I’m having trouble understanding the sequence in which I should order the requested information. Perhaps you could explain some more?

---

Line 2:

Net AS Network

makes sense, since I want Networks in the table

---

Line 3:

rows.Web AS Website

Why/how do I need to use rows?

---

Line 4:

map(rows, (r) => length(r.rows)) as "Number of files"

What does map do? Off course I’ve looked into in on the Dataview Functions page, but I didn’t quite understand what it does.

---

Line 7:

GROUP BY rows.Network[0] as Net

How does the ‘rows.Network[0]’ work?

---

Further: Apparently there’s no need to flatten this anywhere before grouping?

Lots of questions, hope to really learn something!

[ThurMeijer]

What if I wanted the number of files not in a separate column, but behind the ‘name’ of the website between brackets?

Network	Website
A	1 (1)
_	2 (1)
B	3 (2)

---

I’ve tried to compile it as

rows.Web + "(" + map(rows, (r) => length(r.rows)) + ")" AS Website

but that doesn’t work… I then get a sequence of Websites and a sequence of numbers…

If I’d know how the data is fetched and processed, perhaps I could figure it out myself… But unfortunately I can’t seem to find the inner workings of these queries.

Thanks in advance for the help!

[mnvwvnm]

Well, where to start?
Not easy to explain, mainly because I’m not a coder or similar and I need to synthesize some info in a comprehensible way, even with some lack of accuracy and technical knowledge.

But let’s start by the end. To achieve your last request, try this:

map(rows, (r) => r.Web + " (" + length(r.rows) + ")") as Website

---

@mnvwvnm

TABLE WITHOUT ID
	Net AS Network,
	rows.Web AS Website,
	map(rows, (r) => length(r.rows)) as "Number of files"
WHERE Network
GROUP BY Website as Web
GROUP BY rows.Network[0] as Net

1 - A basic rule: in dataview the order of the commands matters and the column declarations are, in fact, the last thing to consider;

2 - when we use GROUP BY command, this action produce this kind of results:

• key: the thing grouped
• rows: an array of metadata for each page that matched to the key

You can try this:

TABLE WITHOUT ID
	key,
	rows
WHERE Network
GROUP BY Website as Web

• simplifying, this means that after the group by command we need to add the prefix rows the access to the metadata now grouped (because this command change the data structure)

3 - The query I gave you complicates things because we are grouping things already grouped. So, at the second group command we are dealing with “rows of rows”.

4 - Why GROUP BY rows.Network[0] as Net? The first group is related with “Website”. So, there’re cases where you have a list of rows.Network (more than one) to the same key. See your example “B”. We group by the website and we have two cases with the same “Network” value (B). But if we don’t want to see two times the “B”, then we write rows.Network[0] (to choose only the first in the list). If you remove the [0] you’ll see the duplications.

5 - map() command is a more complicated thing… in some way related with js. But in a non-technical language, it works in this way:

• I have a list/array of things (the rows) and I want to apply a rule/function/etc to each element inside that list;
• in the case, because we’re dealing with a second group, we want to explore each rows (from the first group) nested in the actual rows (from the last group command);

6 - map(rows, (r) => r.Web + " (" + length(r.rows) + ")") as Website

• that is the logic applied here: we need to build the expression (with the concatenation) for each rows.

I know this is not very clear, mainly because we’re dealing with a special query with two levels of group. To understand things it’s better start with only one group command, not two. (use two levels of group isn’t a usual procedure because things get really messy).

[ThurMeijer]

@mnvwvnm

map(rows, (r) => r.Web + " (" + length(r.rows) + ")") as Website

Thanks, this worked like a charm.

And also lots of gratitude for explaining the whole thing. I still have some trouble grasping the ‘map’ construct, but I’ve found that there’s a lot of info on that on the Web.