Re: [RavenDB] Map/Reduce index with DateTime.Now

[Oren Eini (Ayende Rahien)]

This is applied AFTER the index have run.

What you probably want to do is to group by the debtor and the debt date (usually month), then you can query the previous debts.

On Fri, Aug 10, 2012 at 2:49 AM, keperro <sandra...@gmail.com> wrote:

Hi all,

I am having some problems creating map/reduce indexes.
I have the following structure defining my DEBT documents:

public class Debt
{
      public string DebtorName {get;set;}
      public DateTime DueDate {get;set;}
      public decimal Amount {get;set,}
}

I want to group all the debts by debtor name up to now. So basically, I want to know how much a debtor owes until now, adding up all the amounts of his debts that are due to be paid.

I have tried to create an index for that but I am not sure how to do it. I understand why I cannot use DateTime.Now in my index.


    public class PresentDebts : AbstractIndexCreationTask<Debt>
    {
        public   PresentDebts  ()
        {
            Map = debts=> from debt in debts
                               select new
                                          {
                                              DebtorName = debt.DebtorName,
                                              Amount = debt.Amount
                                          };

            Reduce = results => from result in results
                                group result by result.DebtorName
                                    into g
                                    select new
                                               {
                                                   DebtorName = g.Key,
                                                   Amount = g.Sum(x => x.Amount)
                                               };
        }
    }

How I use my index:

           var debts = session.Query<Debt, PresentDebts>().Where(x => x.DueDate < DateTime.Now);

It returns 0 results. Is the "where" clause applied to the dataset being mapped in the index or afterwards on the grouped results ?

What is the best approach for this situation? Is it possible to do it with an index?
I have way more than 128 debts prior to today in my db, so I just wanted to avoid grabbing them all, and group them outside the db.

Thanks.

[Oren Eini (Ayende Rahien)]

You do the second order grouping after you load the data in a transform results.

On Fri, Aug 10, 2012 at 5:02 PM, keperro <kep...@gmail.com> wrote:

But I still need to group them all in one debt per debtor afterwards, and I don't know how to do it.
Modifying my index as you said grouping by debtor and debt date, and filtering by previous debts afterwards, I still get a collection of debts per debtor and what I want is a unique debt per debtor with all the amounts added up.

I would still need something like this but that is not supported because of the GroupBy. I wanted to avoid doing ToList() before the GroupBy.

var debts = session.Query<Debt, PresentDebts>().Where(x => x.DueDate < DateTime.Now).GroupBy(x => x.DebtorName).Select(g => new Debt{ DebtorName = g.Key, Amount = g.Sum(x => x.Amount});

Is it possible to do it without fetching them all from the db?

[keperro]

Sorry but I am quite new in raven! How does it work exactly? 

Do you mean that I should do my filtering by past debt dates in the transform results itself? And then group by debtor name in the transform results and aggregate all the amounts? My index becoming something like:

  TransformResults= (database, results) => from result in results

                               where result.DueDate.Date < DateTime.Now.Date

                                group result by result.DebtorName
                                    into g
                                    select new
                                               {
                                                   DebtorName = g.Key,
                                                   Amount = g.Sum(x => x.Amount)
                                               }; 

And then my query:

 var debts = session.Query<Debt, PresentDebts>(); 

Is it what you are suggesting? Sorry but I am a complete newbie!

Thanks again for your reply.

[Oren Eini (Ayende Rahien)]

No, like this:

TransformResults= (database, results) => from result in results

                                group result by result.DebtorName
                                    into g
                                    select new
                                               {
                                                   DebtorName = g.Key,
                                                   Amount = g.Sum(x => x.Amount)
                                               }; 

 var debts = session.Query<Debt, PresentDebts>().Where(x=>x.Date < DateTime.Today); 

[keperro]

Ok, thanks I will try that.

How does the transform results work then? It transforms the results after the filtering?

[Oren Eini (Ayende Rahien)]

Yes