Complex MulitMap / Reduce - From Stackoverflow
Paul
I want to list a paged view of audio items on my website, an audio
item has several standard properties such as title & description. It
also stores multiple Tags & a Genre document, but I also want to
display some statistics for each of my audio items, there are 10 in
total:
- Total Downloads
- Total Plays
- Total Likes
- Total Favourites
- Total Comments
- This Weeks Comments
- This Weeks Plays
- This Weeks Likes
- This Weeks Favourites
- This Weeks Comments
Here are the related documents:
Audio
- Title
- Desc
- ArtistName
- AudioComment
- Tags (embed a collection of tag documents here)
- Genre (embed a genre document here)
AudioComment
- AudioId
- Comments (collection)
Genre
- Id
- Code
- Name
Tag
- Id
- Name
AudioCounters
- AudioId
- Type (play / download)
- Ip
- DateTime
Likes
- AudioId
- AccountId
- DateTime
Favs
- AudioId
- AccountId
- DateTime
The weekly counters are basically a count of the stats for the past 7
days only.
The map reduce index I have at the moment even though it looks pretty
complex works very well and pulls back results in under 10ms.
Here is the index again:
public class AudioWithCounters :
AbstractMultiMapIndexCreationTask<AudioWithCounters.AudioViewModel>
{
public class AudioViewModel
{
public string Id { get; set; }
public string ArtistName { get; set; }
public string Name { get; set; }
public string Identifier { get; set; }
public string Description { get; set; }
public IList<Tag> Tags { get; set; }
public Genre Genre { get; set; }
public DateTimeOffset? DateAdded { get; set; }
public UserImage Image { get; set; }
public int TotalComments { get; set; }
public int TotalDownloads { get; set; }
public int TotalPlays { get; set; }
public int TotalLikes { get; set; }
public int TotalFavourites { get; set; }
public int WeeksComments { get; set; }
public int WeeksDownloads { get; set; }
public int WeeksPlays { get; set; }
public int WeeksLikes { get; set; }
public int WeeksFavourites { get; set; }
}
public AudioWithCounters()
{
AddMap<Audio>(audios => from audio in audios
select new
{
Id = audio.Id,
ArtistName = audio.ArtistName,
Name = audio.Name,
Identifier = audio.Identifier,
Description =
audio.Description,
Tags = audio.Tags,
Genre = audio.Genre,
DateAdded = audio.DateAdded,
Image = audio.Image,
TotalDownloads = 0,
TotalComments =
audio.CommentsCount,
TotalPlays = 0,
TotalLikes = 0,
TotalFavourites = 0,
WeeksDownloads = 0,
WeeksPlays = 0,
WeeksComments = 0,
WeeksLikes = 0,
WeeksFavourites = 0
});
AddMap<AudioComments>(comments => from audioComment in
comments
from comment in
audioComment.Comments
where comment.CreatedAt
>= DateTimeOffset.Now.AddDays(-7)
select new
{
Id = audioComment.Audio.Id,
ArtistName = (string)null,
Name = (string)null,
Identifier = (string)null,
Description = (string)null,
Tags = (object)null,
Genre = (object)null,
DateAdded = (object)null,
Image = (object)null,
TotalDownloads = 0,
TotalComments = 0,
TotalPlays = 0,
TotalLikes = 0,
TotalFavourites = 0,
WeeksDownloads = 0,
WeeksPlays = 0,
WeeksComments = 1,
WeeksLikes = 0,
WeeksFavourites = 0
});
AddMap<AudioCounter>(counters => from counter in counters
where counter.Type ==
Core.Enums.Audio.AudioCounterType.Download
select new
{
Id = counter.AudioId,
ArtistName = (string)null,
Name = (string)null,
Identifier = (string)null,
Description = (string)null,
Tags = (object)null,
Genre = (object)null,
DateAdded = (object)null,
Image = (object)null,
TotalDownloads = 1,
TotalComments = 0,
TotalPlays = 0,
TotalLikes = 0,
TotalFavourites = 0,
WeeksDownloads = 0,
WeeksPlays = 0,
WeeksComments = 0,
WeeksLikes = 0,
WeeksFavourites = 0
});
AddMap<AudioCounter>(counters => from counter in counters
where counter.Type ==
Core.Enums.Audio.AudioCounterType.Play
select new
{
Id = counter.AudioId,
ArtistName =
(string)null,
Name = (string)null,
Identifier =
(string)null,
Description =
(string)null,
Tags = (object)null,
Genre = (object)null,
DateAdded =
(object)null,
Image = (object)null,
TotalDownloads = 0,
TotalPlays = 1,
TotalComments = 0,
TotalLikes = 0,
TotalFavourites = 0,
WeeksDownloads = 0,
WeeksPlays = 0,
WeeksComments = 0,
WeeksLikes = 0,
WeeksFavourites = 0
});
AddMap<AudioCounter>(counters => from counter in counters
where counter.Type ==
Core.Enums.Audio.AudioCounterType.Download
where counter.DateTime >=
DateTimeOffset.Now.AddDays(-7)
select new
{
Id = counter.AudioId,
ArtistName =
(string)null,
Name = (string)null,
Identifier =
(string)null,
Description =
(string)null,
Tags = (object)null,
Genre = (object)null,
DateAdded =
(object)null,
Image = (object)null,
TotalDownloads = 0,
TotalPlays = 0,
TotalComments = 0,
TotalLikes = 0,
TotalFavourites = 0,
WeeksDownloads = 1,
WeeksPlays = 0,
WeeksComments = 0,
WeeksLikes = 0,
WeeksFavourites = 0
});
AddMap<Like>(likes => from like in likes
select new
{
Id = like.AudioId,
ArtistName =
(string)null,
Name = (string)null,
Identifier =
(string)null,
Description =
(string)null,
Tags = (object)null,
Genre = (object)null,
DateAdded =
(object)null,
Image = (object)null,
TotalDownloads = 0,
TotalPlays = 0,
TotalComments = 0,
TotalLikes = 1,
TotalFavourites = 0,
WeeksDownloads = 0,
WeeksPlays = 0,
WeeksComments = 0,
WeeksLikes = 0,
WeeksFavourites = 0
});
AddMap<Favourite>(favs => from fav in favs
select new
{
Id = fav.AudioId,
ArtistName = (string)null,
Name = (string)null,
Identifier = (string)null,
Description = (string)null,
Tags = (object)null,
Genre = (object)null,
DateAdded = (object)null,
Image = (object)null,
TotalDownloads = 0,
TotalPlays = 0,
TotalComments = 0,
TotalLikes = 0,
TotalFavourites = 1,
WeeksDownloads = 0,
WeeksPlays = 0,
WeeksComments = 0,
WeeksLikes = 0,
WeeksFavourites = 0
});
AddMap<AudioCounter>(counters => from counter in counters
where counter.Type ==
Core.Enums.Audio.AudioCounterType.Play
where counter.DateTime >=
DateTimeOffset.Now.AddDays(-7)
select new
{
Id = counter.AudioId,
ArtistName =
(string)null,
Name = (string)null,
Identifier =
(string)null,
Description =
(string)null,
Tags = (object)null,
Genre = (object)null,
DateAdded =
(object)null,
Image = (object)null,
TotalDownloads = 0,
TotalPlays = 0,
TotalComments = 0,
TotalLikes = 0,
TotalFavourites = 0,
WeeksDownloads = 1,
WeeksPlays = 0,
WeeksComments = 0,
WeeksLikes = 0,
WeeksFavourites = 0
});
AddMap<Like>(likes => from like in likes
where like.DateCreated >=
DateTimeOffset.Now.AddDays(-7)
select new
{
Id = like.AudioId,
ArtistName = (string)null,
Name = (string)null,
Identifier = (string)null,
Description = (string)null,
Tags = (object)null,
Genre = (object)null,
DateAdded = (object)null,
Image = (object)null,
TotalDownloads = 0,
TotalPlays = 0,
TotalComments = 0,
TotalLikes = 0,
TotalFavourites = 0,
WeeksDownloads = 0,
WeeksPlays = 0,
WeeksComments = 0,
WeeksLikes = 1,
WeeksFavourites = 0
});
AddMap<Favourite>(favs => from fav in favs
where fav.DateCreated >=
DateTimeOffset.Now.AddDays(-7)
select new
{
Id = fav.AudioId,
ArtistName = (string)null,
Name = (string)null,
Identifier = (string)null,
Description = (string)null,
Tags = (object)null,
Genre = (object)null,
DateAdded = (object)null,
Image = (object)null,
TotalDownloads = 0,
TotalPlays = 0,
TotalComments = 0,
TotalLikes = 0,
TotalFavourites = 0,
WeeksDownloads = 0,
WeeksPlays = 0,
WeeksComments = 0,
WeeksLikes = 0,
WeeksFavourites = 1
});
Reduce = results => from result in results
group result by result.Id
into g
select new
{
Id = g.Key,
ArtistName = g.Select(x =>
x.ArtistName).Where(x => x != null).FirstOrDefault(),
Name = g.Select(x =>
x.Name).Where(x => x != null).FirstOrDefault(),
Identifier = g.Select(x =>
x.Identifier).Where(x => x != null).FirstOrDefault(),
Description = g.Select(x =>
x.Description).Where(x => x != null).FirstOrDefault(),
Tags = g.Select(x =>
x.Tags).Where(x => x != null).FirstOrDefault(),
Genre = g.Select(x =>
x.Genre).Where(x => x != null).FirstOrDefault(),
DateAdded = g.Select(x =>
x.DateAdded).Where(x => x != null).FirstOrDefault(),
Image = g.Select(x =>
x.Image).Where(x => x != null).FirstOrDefault(),
TotalDownloads = g.Sum(x =>
x.TotalDownloads),
TotalPlays = g.Sum(x =>
x.TotalPlays),
TotalComments = g.Sum(x =>
x.TotalComments),
TotalLikes = g.Sum(x =>
x.TotalLikes),
TotalFavourites = g.Sum(x =>
x.TotalFavourites),
WeeksComments = g.Sum(x =>
x.WeeksComments),
WeeksDownloads = g.Sum(x =>
x.WeeksDownloads),
WeeksPlays = g.Sum(x =>
x.WeeksPlays),
WeeksLikes = g.Sum(x =>
x.WeeksLikes),
WeeksFavourites = g.Sum(x =>
x.WeeksFavourites)
};
Index(x => x.Genre.Code, FieldIndexing.Analyzed);
Store(x => x.Genre.Code, FieldStorage.Yes);
}
}
If it doesn't format properly you can see it here on SO:
http://stackoverflow.com/questions/9035452/multimap-reduce-index-field-issue/9038753?iemail=1#9038753
The issue I have at the moment is that in the reduce part of the index
I am returning the Tags & Genre as hydrated objects and I think this
is confusing the Index when I want to query by the Genre.Code.
Sorry about the long post, just wanted to ensure I give you all the
details...if you need any more details let me know.
Paul
Paul
Can anybody help with this query, am I going about this the wrong way?
I thought about using denormalized counters that sit within the audio
document, but someone advised against it on stack overflow.
Thank You,
Paul
Oren Eini (Ayende Rahien)
Paul Hinett
Yes I understand that now which is no longer an issue, however is the design of this index ok, would it scale well?
There will be thousands of new AudioCounter documents added daily.
Also, for the Weekly counters, in my index I am limiting the results by date for the last 7 days…is this good design?
I am just looking for approval on the way I am implementing this, or to see if there is a better alternative.
Thank You,
Paul
Oren Eini (Ayende Rahien)
Paul Hinett
The same index code was posted on StackOverflow in a related question…
http://stackoverflow.com/questions/9035452/multimap-reduce-index-field-issue/9038753#9038753
Oren Eini (Ayende Rahien)
where comment.CreatedAt >= DateTimeOffset.Now.AddDays(-7)
You can't assume that the Now would be stable.
Paul
to this.
I'm not quite sure what I need to do here to get away from using
DateTime.Now in my index.
You say I should use something like:
do pass to the index the current week to filter / aggregate on?
Thank You.
Oren Eini (Ayende Rahien)
Paul Hinett
Ok, I understand that part.
But my query returns stats from several documents, both weekly and totals…if I query by that specific week how would it include the stats from each document and include the overall totals?
I need:
TotalComments
WeeksComments
TotalDownloads
WeeksDownloads
TotalPlays
WeeksPlays
And a few more…
Sorry if these are basic questions, it’s just something I haven’t wrapped my head around yet.
From: rav...@googlegroups.com [mailto:rav...@googlegroups.com] On Behalf Of Oren Eini (Ayende Rahien)
Sent: 01 March 2012 16:07
To: rav...@googlegroups.com
Subject: Re: [RavenDB] Re: Complex MulitMap / Reduce - From Stackoverflow
Inside your multi map, this gives you an output of 2012-15 (where 15 is the week number)
Oren Eini (Ayende Rahien)
Paul Hinett
Struggling to get this to work how I want…
I have managed to get it working with 3 indexes.
1) Audio_Index
2) AudioStatsTotals_Index
3) AudioStatsWeekly_Index
I query my Audio_Index and filter on several properties, then query the 2 stats indexes and merge them together into my ViewModel, this works fine.
But I need to be able to OrderBy on the statistics, either on the weekly or total statistics but I can’t do this until I have merged them which is too late.
I have also tried to get a single index to work which just includes my weekly statistics, ignoring the total stats. Here is my index code:
This brings back incorrect results, they either have a NULL WeekNumber but with valid Audio properties, or a valid WeekNumber but null Audio properties.
Is this going to be possible, I really hope I can use some indexing magic rather than denormalize my statistic counters.
Sorry to be such a pain, I am 95% done re-writing my website to use RavenDb and I’m VERY happy so far, just apart from a couple of indexing issues, probably due to my lack of understanding.
Paul
Oren Eini (Ayende Rahien)
Paul
I have zipped up a small test project containing a single self-
contained test with my index and POCO classes.
http://www.house-mixes.com/raven/HM.Tests.Individual.zip
Sorry if this has duplicated, Google Groups just doesn't seem to be
posting my reply for some reason.
Thank You,
Paul
On Mar 2, 8:32 am, "Oren Eini (Ayende Rahien)" <aye...@ayende.com>
wrote:
> On a first glance, you indexing code looks good.
> Can you try creating a failing test that demonstrate what you are trying to
> do?
>
>
>
>
>
>
>
>
> > Struggling to get this to work how I want…****
>
> > ** **
>
> > I have managed to get it working with 3 indexes.****
>
> > ** **
>
> > **1) **Audio_Index****
>
> > **2) **AudioStatsTotals_Index****
>
> > **3) **AudioStatsWeekly_Index****
>
> > ** **
> > I query my Audio_Index and filter on several properties, then query the 2
> > ***
>
> > ** **
> > But I need to be able to OrderBy on the statistics, either on the weekly
> > or total statistics but I can’t do this until I have merged them which is
>
> > ** **
> > I have also tried to get a single index to work which just includes my
>
> >http://pastie.org/3500540****
>
> > ** **
> > This brings back incorrect results, they either have a NULL WeekNumber but
> > with valid Audio properties, or a valid WeekNumber but null Audio
>
> > ** **
> > Is this going to be possible, I really hope I can use some indexing magic
>
> > ** **
> > Sorry to be such a pain, I am 95% done re-writing my website to use
> > RavenDb and I’m VERY happy so far, just apart from a couple of indexing
>
> > ** **
>
> > Paul****
>
> > ** **
>
> > *From:* rav...@googlegroups.com [mailto:rav...@googlegroups.com] *On
> > Behalf Of *Oren Eini (Ayende Rahien)
> > *Sent:* 01 March 2012 16:14
>
> > *To:* rav...@googlegroups.com
> > *Subject:* Re: [RavenDB] Re: Complex MulitMap / Reduce - From
> > Stackoverflow****
>
> > ** **
>
> > You have two different indexes, one for per week, one for total.****
>
> > On Thu, Mar 1, 2012 at 6:13 PM, Paul Hinett <p...@ukcreativedesigns.com>
> > wrote:****
>
> > Ok, I understand that part.****
>
> > ****
> > But my query returns stats from several documents, both weekly and
> > totals…if I query by that specific week how would it include the stats from
>
> > ****
>
> > I need:****
>
> > TotalComments****
>
> > WeeksComments****
>
> > TotalDownloads****
>
> > WeeksDownloads****
>
> > TotalPlays****
>
> > WeeksPlays****
>
> > ****
>
> > And a few more…****
>
> > ****
> > Sorry if these are basic questions, it’s just something I haven’t wrapped
>
> > ****
>
> > *From:* rav...@googlegroups.com [mailto:rav...@googlegroups.com] *On
> > Behalf Of *Oren Eini (Ayende Rahien)
> > *Sent:* 01 March 2012 16:07****
>
> > *To:* rav...@googlegroups.com
> > *Subject:* Re: [RavenDB] Re: Complex MulitMap / Reduce - From
> > Stackoverflow****
>
> > ****
> > Inside your multi map, this gives you an output of 2012-15 (where 15 is
>
> > And you can do queries on that.****
>
> > On Thu, Mar 1, 2012 at 5:36 PM, Paul <p...@ukcreativedesigns.com> wrote:**
> > **
> > Sorry to bring this one back up, I've only just had chance to get back
> > to this.
>
> > I'm not quite sure what I need to do here to get away from using
> > DateTime.Now in my index.
>
>
> > comment.CreatedAt.Year + "-" + comment.CreatedAt.DayOfYear / 7****
> > Where does this need to go, inside my MultiMap, or in my document? How
> > do pass to the index the current week to filter / aggregate on?
>
>
> > ****
>
> > ** **
Oren Eini (Ayende Rahien)
Paul Hinett
Hi,
Thank you for taking the time to have a look at this, however I don’t think this will work for me as I also need to filter on properties of my Audio document, such as the AudioType or several other properties.
With the index you have provided, I will only have access to the properties of the statistics and the audio Id until I call ToList(), this isn’t going to work when I need to introduce paging & filtering on other properties.
Any other ideas?
Oren Eini (Ayende Rahien)
Paul Hinett
I need to filter on the properties of my Audio document, however I need to order by on the aggregation properties which is the reason I was trying to get this all into a single index.
I have found a solution, not perfect but I think it will work.
In my AudioCounter document I have included a property called IsRecent, this is set to True when created.
I will then have a console application run once per hour to find all the AudioCounter documents older than 7 days where IsRecent = true, and update them to False, probably using a patch command.
This enables me to use my original index, but instead of comparing the Dates and using DateTime.Now.AddDays(-7) I can simply check for IsRecent = true.
As long as nothing stands out obvious why this is a bad idea, I’m happy to compromise with it.
Thank You,
Paul
Oren Eini (Ayende Rahien)
Paul Hinett
Is what I have is hundreds and thousands of media files on my site, users can then play them, download them, like them, add them to favourites etc.
Each time they do this it’s logged down as a statistic, I then need the user to be able to view media of type ‘Mix’ and tagged with ‘Techno’, but also give them the option to order by the most downloaded in the past 7 days, the most played overall (since it was first uploaded) etc, the list of media are then displayed in pages to the user along with many properties from the Audio document such as name etc, the statistics for the past 7 days, and also that statistics since it was first uploaded.
If I am using 2 queries to get my data, how can I perform paging correctly. Forgive me if I am missing something but from the code you provided I don’t think it was possible, surely I need to perform my paging after I have done my filtering and my OrderBy, I can’t get the relevant audios as a list, then query their stats because I can’t OrderBy my stats correctly then.
The reason I can’t use the date in my index is because apparently (I’m not entirely sure why) I can’t use DateTime.Now inside my static index to compare against to filter my statistics to get a subset for only the last 7 days.
I may be making this sound more complex than it actually is, but from doing lots of Googling and plenty of tests and alterations this is the only way I can get it working.
Oren Eini (Ayende Rahien)
Paul Hinett
I don’t want to filter by date, that’s pretty simple. I need to order by the stats that each Audio document has had in the past 7 days AND overall.
It doesn’t need to be PERFECTLY accurate, I have a page which lists the most popular audio, basically sorts Audio by how many downloads it has had in the last 7 days.
What I need in a simplified way (I have more stats than just downloads such as plays, comments, likes etc):
Audios
- Name
- AudioType
- Description
- Etc…
AudioDownloads
- AudioId
- DownloadDate
My index needs to include:
AudioIndex
- AudioId
- Name
- WeeksDownloads (a Sum of all the downloads from the AudioDownloads documents from the past 7 days only)
- TotalDownloads (a Sum of all the downloads from the AudioDownloads document)
Then I can perform filtering by Name, AudioType and also order by either WeeksDownloads or TotalDownloads very easily.
Sorry if I’m not expressing what I need very well.
Oren Eini (Ayende Rahien)
Paul Hinett
Sounds good, will give it a go.
When you say update hourly, I take it you mean with a console application in the background.
Will updating every document in my index hourly affect my index performance in any way?
Thank you for the help, really appreciated!
Oren Eini (Ayende Rahien)
Paul Hinett
Hi Oren,
I have implemented this for my weekly statistics and it’s working great thank you, should I follow the same principles for the Total statistics though?
Total statistics means it needs to include all AudioCounter documents for that AudioId rather than just from the previous 7 days, it means my Console app will need to query an index containing the totals every hour and will virtually include every Audio document (currently around 200,000), this is how I have set it up at the moment, I’m just hoping I don’t get any nasty performance issues later when in production?