Htykuut

I have 2 collections of documents in my ravenDB, location and address like this:

public class Location()

{

    public string Id{get;set;}

    public string Name{get;set;}

    public List<Address> Addresses{get;set;}



}



public class Address()

{

    public string Id{get;set;}

    public string StreetName {get;set;}

    public string PostalCode {get;set;}

}

So a Location can have zero og many addresses. And I can have many addresses that are not assigned to a Location.

If I have data like this:

//2 Location document

{

Id:"Location/1",

Name: "My Location 1"

Addresses: [{id:"Address/1"},

            {id:"Address/2"}

            ]

}



{

Id:"Location/2",

Name: "My Location 2"

Addresses: [

            {id:"Address/2"}

            ]

}





//3 Address document

{

Id: "Address/1",

StreetName: "Street1 10",

PostalCode: "1000"

}



{

Id: "Address/2",

StreetName: "Street1 11",

PostalCode: "1000"

}



{

Id: "Address/3",

StreetName: "Street1 12",

PostalCode: "2000"

}

I'm struggleing to find the best way to create an index of this which will give me a resultset like this:

StreetAddress PostalCode    Location

Street1 10  1000    My Location 1

Street2 11  1000    My Location 2

Street2 11  1000    My Location 1

Street3 12  2000

I would appreciate any input on this:-)

One option is to model the other way around, i.e.

public class Location()

{

    public string Id {get;set;}

    public string Name {get;set;}



}



public class Address()

{

    public string Id {get;set;}

    public string StreetName {get;set;}

    public string PostalCode {get;set;}

    // Locations is a list with the Location documents IDs

    public List<string> Locations {get;set;}



}

And then the index can be:

from address in docs.Addresses

select new {

     StreetAddress = address.StreetName,

     PostalCode = address.PostalCode

}

And then use the following Query:

from index 'AddressesIndex' as x

load x.Locations as locations

select {

      StreetName: x.StreetName,

      PostalCode: x.PostalCode,

      Locations: locations.map(x => x.Name)

}

===========================================================================

A second option is to use the same models as you have in the question description
and define a
multi-map-reduce index.

The multi-map-reduce index will be:

The first map:

from locationDoc in docs.Locations

from addressId in locationDoc.Addresses

let address = LoadDocument(addressId, "Addresses")

select new {

       StreetName = address.StreetName,

       PostalCode = (string)null,

       LocationNames = new {locationDoc.Name},

}

The second map:

from address in docs.Addresses

select new{

       StreetName = address.StreetName,

       PostalCode = address.PostalCode,

       LocationNames = (string)null

}

The reduce:

from result in results

group result by new { result.StreetName } into g

select new {

       StreetName = g.Key.StreetName,

       PostalCode = g.Select(x => x.PostalCode).FirstOrDefault(x => x != null),

       LocationNames = g.SelectMany(x => x.LocationNames)

}