Multimap/Reduce join not working

[Ståle Eikebråten]

Hi !

 

We have stumbled upon a problem we can’t seem to solve.

 

The following scenario consists of two aggregates, children (Barn in Norwegian) and transportation agreements (skyssavtaler in Norwegian).

We want to do a “join” between the two, using name from Barn, and details from Skyssavtale..

The mapping/index looks like this:

 

    public class SkyssAvtalerMedNavn : AbstractMultiMapIndexCreationTask<SkyssavtaleSokeResultat>

    {

        public SkyssAvtalerMedNavn()

        {

            AddMap<Barn>(barnliste => from barn in barnliste

                                      select new

                                      {

                                          EntityType = "Barn",

                                          BarnId = barn.Id,

                                          AvtaleId = (string)null,

                                          FulltNavn = barn.NavnIBruk.Fornavn + " " + barn.NavnIBruk.Etternavn,

                                          barn.NavnIBruk.Fornavn,

                                          barn.NavnIBruk.Etternavn

                                      });

            AddMap<Skyssavtale>(avtaleliste => from avtale in avtaleliste

                                               select new

                                               {

                                                   EntityType = "Skyss",

                                                   BarnId = avtale.BarnId,

                                                   AvtaleId = avtale.Id,

                                                   FulltNavn = (string)null,

                                                   Fornavn = (string)null,

                                                   Etternavn = (string)null

                                               });

            Reduce = results => from result in results

                                group result by result.BarnId

                                    into g

                                    from skyss in g

                                    where skyss.EntityType == "Skyss"

                                    from barn in g

                                    where barn.EntityType == "Barn"

                                    select new { skyss.EntityType, skyss.BarnId, skyss.AvtaleId, barn.FulltNavn, barn.Fornavn, barn.Etternavn };

            Index(x => x.FulltNavn, FieldIndexing.Analyzed);

            Store(x => x.EntityType, FieldStorage.Yes);

            Store(x => x.AvtaleId, FieldStorage.Yes);

            Store(x => x.BarnId, FieldStorage.Yes);

            Store(x => x.FulltNavn, FieldStorage.Yes);

            Store(x => x.Fornavn, FieldStorage.Yes);

            Store(x => x.Etternavn, FieldStorage.Yes);

         }

    }

 

We expect that this in effect does a join such that each skyssavtale is filled with the appropriate information from barn.

 

The Query:

            using (var session = DocumentStore.OpenSession())

            {

                var results = session.Query<SkyssavtaleSokeResultat, SkyssAvtalerMedNavn>()

                    .AsProjection<SkyssavtaleSokeResultat>()

                    .ToList();

                return results;

            }

 

This does not work, and we get 0 results.

 

When we replace the Reduce with:

 

            Reduce = results => from result in results

                                group result by result.BarnId

                                    into g

                                    select new { Fornavn = g.Count().ToString(), Etternavn = "", AvtaleId = "", BarnId = "", FulltNavn = "", EntityType = "" };

 

Then we get 1 record, with Fornavn = 5 ???, so It seems the grouping has grouped all mapped items into one group. We also have tried removing the reduce entirely, and found that the mapped items (the projections) had the expected fields.

 

Now for the strange part. If the replace the reduce with

 

            Reduce = results => from result in results

                                group result by result.BarnId

                                    into g

                                    select new { Fornavn = g.Count().ToString(), Etternavn = "", AvtaleId = g.Select(x => x.AvtaleId).FirstOrDefault(), BarnId = g.Select(x => x.BarnId).FirstOrDefault(), FulltNavn = "", EntityType = "" };

 

 

We now strangely get the expected grouping; namely two groups, one having 2 records (Fornavn=2), and one having 3 records (Fornavn=3).

 

What are we doing wrong ??  Hope you have a solution to our problem..

 

 

[Oren Eini (Ayende Rahien)]

What is it that you are trying to _do_?

[Ståle Eikebråten]

Aggregate Barn (child)

BarnId ForNavn EtterNavn

1      Eric    Cartman

2      Stan    Broflowski

 

 

Aggregate Transportation (Skyssavtale)

AvtaleId BarnId Transportation Periode

1        1      Bus            2011/08/01 - 2011/12/31

2        1      Taxi           2012/01/01 - 2012/06/30

3        2      Bus            2011/08/01 - 2012/06/30

 

When querying the index, we want a result like this

 

 

Fornavn EtterNavn AvtaleId BarnId Transportation Periode

Eric     Cartman    1       1      Bus            2011/08/01 - 2011/12/31

Eric     Cartman    2       1      Taxi           2012/01/01 - 2012/06/30

Stan  Broflowski 3       2      Bus            2011/08/01 - 2012/06/30

 

..but the Map/Reduce gives us some strange results..

[Oren Eini (Ayende Rahien)]

Stale,

What are you _querying_ on?

You have to make a distinction here between what you are querying on and what you are getting back.

You can query the  Skyssavtale and include the Barn, easily, getting all the info in one go.

[Ståle Eikebråten]

Hi !

 

I’m not sure I understand your question, but if you mean what fields we are querying on, the answer is none, for now..

We will be using Firstname and Lastname to query on later, but for now, we just want to get all the data returned.

 

We want to return all “Skyssavtale” for all children for a given search criteria.. If we want to find all “Skyssavtaler” for a Child named “Eric”,

all children named Eric will appear in the result.. But as we don’t have Name as a property in “Skyssavtale” we tried to do a multimap/Reduce,

to combine to aggregates, but if there is another way of getting the result in one db-fetch, I will be grateful for a solution..

 

Thank you..

[Oren Eini (Ayende Rahien)]

Why not do it like this?

var children = session.Query<Child>(x=>x.Name == "Eric")

  .ToList();

var transports = session.Query<Transport>()

   .Where(x=>x.ChildId.In(children.Select(x=>x.Id))

   .ToList();

Then you merge them for view later on.

Better yet, you can store it as:

{

   "Name": "...",

   "Transports": [ "transports/1", "transports/2"]

}

At which point you can:

var children = session.Query<Child>(x=>x.Name == "Eric").Include(x=>x.Children)

  .ToList();

[Ståle Eikebråten]

Your example of two querys is our backup solution, but we wanted to just do one call to the database, invoking just one query adapter, but

If index cant help us, we will go for this solution..

 

The store-example is not an option, as all info about a child is a completely separate aggregate, just holding all info about child, families, addresses, household members etc etc.

 

But thank you very much for taking time to help us.. J

 

-=Staale=-

[Ryan Heath]

How about storing ayende's example in an index?

Map the children in to a list and reduce by their parent id. Use transformresults to query for the real documents.

Does that make any sense?

// Ryan

Sent from my iPhone