"Traditional" one-to-many Query with RavenDB

Question

I know the include-feature of RavenDB. It allows me to fetch a referenced document right away in one roundtrip to the database. But my problem is: The document i fetch in the first place is not including a reference to the "other" documents. But the "other" documents have references to the current document.

Imagine a setup where we have sites across the world. Each site may trigger various alarms. Each alarm has a reference to the site via siteId.

Now i would like to get a list of all the sites including all alarms. But it looks like, this is not possible with RavenDB? Since include only accepts a "path" in the site-Document which holds an id (or an array of ids) to the referenced document.

This could be solved by providing an array of alarmIds within the site'-document and referencing this array in include. But in contrast to a lot of examples featuring stuff like an orderwithlineItemswhere the order is a self contained thing, mysite` will be running for years, collecting alarms anywhere between 0 and a million. Which seems to be a bad idea to me.

Of course i could go the other way round: Query all alarms and include the sites via sitesId. But this would not return a site that has zero alarms.

So is this just a design error on my side? To i misunderstand something? Or is it just not possible to do this in one query and prevent a "n+1 query"?

Answer 1

public class A
{
    public string Id { get; set; }
}

public class B
{
    public string Id { get; set; }
    public string A { get; set; }
}

public class MultiMapIndex : AbstractMultiMapIndexCreationTask<MultiMapIndex.Result>
{
    public class Result
    {
        public string Id { get; set; }
        public IEnumerable<string> Bs { get; set; }
    }

    public MultiMapIndex()
    {
        AddMap<A>(items => from a in items
            select new Result {Id = a.Id, Bs = new string[0]});

        AddMap<B>(items => from b in items
            select new Result {Id = b.A, Bs = new[] {b.Id}});

        Reduce = results => from result in results
            group result by result.Id
            into g
            select new Result {Id = g.Key, Bs = g.SelectMany(r => r.Bs)};
    }
}

[Fact]
public async Task TestCase()
{
    using var store = GetDocumentStore();

    await new MultiMapIndex().ExecuteAsync(store);
    using (var session = store.OpenAsyncSession())
    {
        await session.StoreAsync(new B {A = "a/1"}, "b/0");
        await session.StoreAsync(new A(), "a/1");
        await session.StoreAsync(new A(), "a/2");
        await session.SaveChangesAsync();
    }

    WaitForIndexing(store);

    using (var session = store.OpenAsyncSession())
    {
        var results = await session.Query<MultiMapIndex.Result, MultiMapIndex>()
            .Include(r => r.Bs)
            .ToArrayAsync();

        var before = session.Advanced.NumberOfRequests;

        var bs = session.LoadAsync<B>(results[0].Bs);

        Assert.Equal(before, session.Advanced.NumberOfRequests);
    }
}

Answer 2

If you do choose to query all Alarms, as you mention,
then you can create a Map-Reduce index on the Alarms collection which will group-by the Sites.
Then you can query this Map-Reduce index and know per Site the count of Alarms it has or doesn't have...

https://demo.ravendb.net/demos/csharp/static-indexes/map-reduce-index