Conditional Group By #18260

MohMehrnia · 2024-03-18T05:19:55Z

MohMehrnia
Mar 18, 2024

Why this code not work?

from "VideoPlayUserInfos" 
where State='StartPlay'
group by UserName
select UserName, count(UserName) as  co

ayende · 2024-03-22T15:37:35Z

ayende
Mar 22, 2024
Maintainer

The issue is that you are trying to first filter the results then group them, but that would mean that we need to do the aggregation on the fly.

Instead, write the query like so:

from "VideoPlayUserInfos" 
group by UserName, State
where State='StartPlay'
select UserName, count() as  co

That allows RavenDB to do the appropriate aggregations ahead of time and give you the results quickly, regardless of the data size.

0 replies

MohMehrnia · 2024-03-23T11:00:27Z

MohMehrnia
Mar 23, 2024
Author

Thanks, I need the below scenario

group by state, sort with log date, and select first record in each record

My Sample Document is

{
    "UserID": "eaba13f6-3acd-4acc-be2f-0cacb219e908",
    "FileID": "52a840a4-0099-49ca-acf2-11a3e8c58f42",
    "LogDateTime": "2024-03-22T21:16:01.7892262",
    "CurrentTime": 5.09628,
    "UserName": "moh.mehrnia",
    "ContentDuration": "00:02:14.3319777",
    "State": "Pause",
    "FullName": "محمد مهرنیا",
    "@metadata": {
        "@collection": "VideoPlayUserInfos",
        "Raven-Clr-Type": "Sin.Course.DTO.JsonObjects.VideoPlayUserInfo, Sin.Course.DTO"
    }
}

3 replies

ayende Mar 24, 2024
Maintainer

Assuming this document structure, you can define a map/reduce index like so:

// map
from v in docs.VideoPlayUserInfos
// where v.State == "StartPlay" // if you want to filter at the source
select new { v.State, v.UserName, Count = 1, FirstSeen = v.LogDateTime }

// map
from r in results
group r by new { r.State, r.UserName } into g
select new {
   g.Key.State,
   g.Key.UserName,
   FirstSeen = g.OrderByDescending(x=>x.FirstSeen).First().FirstSeen,
   Count = g.Sum(x=>x.Count)
}

And then you can do that immediately