Grouped Reads (groups)

gom.groups groups a fragment’s nodes by a key and gives numbers about each group: a count, a sum, a ranking. It is one statement on every engine, Oracle included, and nothing is loaded to be tallied in the application.

Each part of a grouped read is a clause of the query you would write by hand:

Here	Cypher	SQL
groups.load(T, ...)	MATCH (n:T)	FROM t
where	WHERE before the WITH	WHERE
keys: { page: key(q.page) }	WITH n.page AS page, …	GROUP BY page
keys: { user: pathKey(User, [...]) }	MATCH (n)-[…]->(user:User), then WITH user, …	a join, then GROUP BY its id
aggregates: { votes: count() }	count(n) AS votes	COUNT(*) AS votes
aggregates: { likes: count(q.helpful.eq(true)) }	sum(CASE WHEN … THEN 1 ELSE 0 END) AS likes	the same
having	WHERE after the WITH	HAVING
orderBy, limit, skip	ORDER BY, LIMIT, SKIP	ORDER BY, OFFSET, FETCH FIRST

The examples use a piece of feedback on a docs page:

@NodeFragment({ labels: ['Feedback'] })
export class Feedback {
    @NodeId() id: string;
    page: string;
    helpful: boolean;
    createdAt: Date;
}

Group and count. “For each page, how many?”

const votes: Map<string, number> = await gom.groups.countBy(Feedback, 'page');
// Map { '/agents' => 2, '/install' => 3 }

Several numbers, and the three filters.

const ratings = await gom.groups.load(Feedback, (q) => ({
    where: q.createdAt.gte(march),                                // 1. which nodes are read at all
    keys: { page: key(q.page) },
    aggregates: { votes: count(), likes: count(q.helpful.eq(true)) }, // 2. which nodes count toward one number
    having: [['votes', 'gte', 3]],                                // 3. which groups are returned
    orderBy: [['votes', 'desc']],
    limit: 10
}));
// [{ page: '/install', votes: 3, likes: 2 }, …]: each row is typed by its keys and aggregates

Filter	Removes
where	nodes, before anything is grouped
a filtered aggregate	nothing: it decides which nodes that one number counts
having	whole groups, after they are counted

The mistake to look for is helpful.eq(true) moved into where. It is valid, and then votes counts only the helpful ones, so likes equals votes on every row.

Group by a node, not a value. “For each user, how many messages did they write?” A path says how a message reaches its owner, and the key comes back as the node, mapped as a load of its fragment maps it:

const top = await gom.groups.load(Message, (q) => ({
    where: q.role.eq('USER'),
    keys: { user: pathKey(GuideUser, [hop('HAS_MESSAGE', Direction.INCOMING, 'ChatSession'), hop('OWNED_BY')]) },
    aggregates: { messages: count() },
    orderBy: [['messages', 'desc']],
    limit: 5
}));
top.map((it) => [it.user.username, it.messages]);

The aggregates are count(filter?), sum(property, filter?), avg, min and max. having compares an aggregate with a number by eq, neq, gt, gte, lt or lte, its entries joined by AND.

What every engine agrees on:

• A group exists because a node is in it.
• A node that reaches no key node is in no group. One that reaches several is in each, and once in each, however many ways lead there.
• sum, avg, min and max skip a node with no value. Over no values sum is 0 and the others are undefined.
• An aggregate’s filter never takes a node out of its group: a group none of whose nodes meet it still has 0.
• Ties are broken by the keys, ascending, so a top 5 is the same five on every engine.

Where Oracle differs: min and max of a text skip a value stored as "", which Oracle reads as no value. A sum or an avg of decimals is decimal arithmetic, so 0.1 + 0.2 is 0.3 there.