Please help me translate NHibernate query to RavenDB

[Johan Nordberg]

I have a query in nhibernate/sql that get the ranking of a set of items based on how many votes they've got. Basically it just counts the number items that have more votes than "me". 

The code in nhibernate looks like this:

string sql = @"select c.Id, (select COUNT(distinct positivevotes) + 1 from Contributions where IsOnline = 1 And Country = c.Country And PositiveVotes > c.PositiveVotes) as Ranking
                    from Contributions c
                    where c.Id In (:id)";
 
                    ISQLQuery query = (ISQLQuery)context.CurrentSession.CreateSQLQuery(sql)
                                .AddScalar("Id", NHibernateUtil.Int32)
                                .AddScalar("Ranking", NHibernateUtil.Int32)
                                .SetParameterList("id", contributionIds);

                    IList<object[]> result = query.List<object[]>();
 
                    foreach (object[] item in result)
                    {
                        Contribution contribution = contributions.FirstOrDefault(x => x.Id == (int)item[0]);
                        list.Add(contribution, (int)item[1]);
                    }

A "Contribution" has properties for Id, PositiveVotes, IsOnline and Country. constributionIds is an array of ids.

What would be the best way to do this with RavenDB?

[Ayende Rahien]

I am not sure that I understood you, you can probably do this with a map/reduce index, but what does your entities looks like, and what are you querying for?

[Johan Nordberg]

The entity looks like this:

class Contribution

{

Id;

PositiveVotes;

Country;

IsOnline;

// And some other properties

}

Visitors can vote on each contribution. Each vote increment PositiveVotes.

I would like to get a list of contribution based on the ranking, where the item with most votes gets ranking 1, the one with second most votes gets ranking 2 etc. 

If there are more than one item with the same number of votes, they get the same ranking, like:

Votes / Ranking

100   /   1

100   /   1

90     /   2

75    /    3

What I like the query to return is a list of id/ranking pairs. Like

public Dictionary<string /* id */, int /* rankning */> GetRankingForEntities(IEnumerable<Contribution> contributions) 

{

}

Or maybe even better:

public Dictionary<Contribution, int /* rankning */> GetRankingForEntities(IEnumerable<Contribution> contributions) 

{

}

Is that a better explanation? :)

[Ayende Rahien]

Um,

And something like?

session.Query<Contribution>()

   .OrderByDescending(x=>x.PositiveVotes)

   .ToArray()

won't do that for you?

[Johan Nordberg]

No, because of two reasons:

1) Two items with the same number of votes should get the same ranking

2) I need to be able to pass in a list of contribution and get the ranking for them. Like "what the ranking on my image" or "what's the ranking of my friends images". 

As I see it, if I would just sort by PositiveVotes, I would need to get every single contribution in the document store just to find out the ranking for contribution X.

I was also thinking map/reduce, but I've never actually used that (this is my first documentdb-project) so I thought it was best to ask the pros first. :)

// Johan

(thanks for your fast replies! I'm impressed!)

[Ayende Rahien]

Hm...

Is there a different ranking for each country?

[Johan Nordberg]

Yes.

[Ryan Heath]

Ayende & Matt,

Could 'ranking' be solved via the facets mechanisme?

Facets could return
Votes / count / dense_rank / rank
100 / 2 / 1 / 1
90 / 1 / 2 / 3
75 / 1 / 3 / 4

What do you think?

// Ryan

[Ayende Rahien]

// map

from contrib in docs.Contribs

where contrib.IsOnline

select new { contrib.Country, VoteRanking = new [] { contrib.PositiveVotes} );

// reduce

from result in results

group result by result.Country into g

select new { Country = g.Key, VoteRanking = g.SelectMany(x=>x.PositiveVotes).OrderByDescending(x=>x).Distinct() }

The result of which would be something like:

{ Country: "US", "VoteRanking": [100, 90, 75] }

That means that is is _really_ cheap to calculate the ranking.

On Wed, Sep 28, 2011 at 6:36 PM, Johan Nordberg <tfsj...@gmail.com> wrote:

Yes.

[Ayende Rahien]

How would you do that?

[Johan Nordberg]

How would my query look to use the index?

I've never used indexes in my app yet, so even that part is new to me.

I appreciate all your help so very much!

// Johan 

[Ayende Rahien]

var ranking = session.Query<RankingByCountry.ReduceResult, RankingByCountry>()

   .Where(x=>x.Country == "US")

   .FirstOrDefault();

var contributions = session.Query<Contribution>().OrderByDescening(x=>x.PositiveVotes)

   .ToList();

return contributions.Select( contrib => new { Contribution = contrib, Ranking = ranking.VoteRanking.IndexOf(contrib.PositiveVotes) });

[Ryan Heath]

Looking at http://ravendb.net/documentation/faceted-search

Facets would now return

Votes/ count
100 / 2
90 / 1
70 / 1

It would be easy to calc the rank from thereof.
Perhaps something like

new Facet {
Name = "Votes"
RankMode = RankOnValues ( or RankOnCounts | RankOnCountDesc |
RankOnValues | RankOnValuesDesc)
}

Which would return

Votes / count / dense_rank / rank
100   /   2 / 1 / 1
90     /   1 / 2 / 3
75     /   1 / 3 / 4

// usage
s.Query<Contribution>("VoteRank")
.Where(x => x.Country == "US")
.ToFacets("facets/VoteFacets");

I hope I am clear enough :)

// Ryan

[Matt Warren]

I was thinking along the same lines ;-)

However the facet implementation won't let you what Johan wants.

For instance:

Votes/ count
100 / 2
90 / 1
70 / 1

Is grouped by Votes, not Votes and Country which the Map/Reduce
solution is. You can of course add a "Country" field to the facet doc
and you will also get a result like this:

Country/ count
UK / 5
USA / 8
..

The main limitation of the faceted search is that it is only ever do a
simple document count, it can't sum/avg any values from within the
individual docs like Map/Reduce can.

On Sep 28, 5:23 pm, Ryan Heath <ryan.q.he...@gmail.com> wrote:
> Looking athttp://ravendb.net/documentation/faceted-search

>
> Facets would now return
>
> Votes/ count
> 100 / 2
> 90 / 1
> 70 / 1
>
> It would be easy to calc the rank from thereof.
> Perhaps something like
>
> new Facet {
> Name = "Votes"
> RankMode = RankOnValues ( or RankOnCounts | RankOnCountDesc |
> RankOnValues | RankOnValuesDesc)
>
> }
>
> Which would return
> Votes / count / dense_rank / rank
> 100   /   2 / 1 / 1
> 90     /   1 / 2 / 3
> 75     /   1 / 3 / 4
>
> // usage
> s.Query<Contribution>("VoteRank")
>  .Where(x => x.Country == "US")
>  .ToFacets("facets/VoteFacets");
>
> I hope I am clear enough :)
>
> // Ryan
>
>
> On Wed, Sep 28, 2011 at 5:38 PM, Ayende Rahien <aye...@ayende.com> wrote:
> > How would you do that?
>

> > On Wed, Sep 28, 2011 at 6:37 PM, Ryan Heath <ryan.q.he...@gmail.com> wrote:
>
> >> Ayende & Matt,
>
> >> Could 'ranking' be solved via the facets mechanisme?
>
> >> Facets could return
> >> Votes / count / dense_rank / rank
> >> 100   /   2 / 1 / 1
> >> 90     /   1 / 2 / 3
> >> 75     /   1 / 3 / 4
>
> >> What do you think?
>
> >> // Ryan
>
> >> On Wed, Sep 28, 2011 at 5:28 PM, Ayende Rahien <aye...@ayende.com> wrote:
> >> > Hm...
> >> > Is there a different ranking for each country?
>

> >> > On Wed, Sep 28, 2011 at 6:25 PM, Johan Nordberg <tfsjo...@gmail.com>

[Ryan Heath]

Hmm,

I have the impression with facet search when filtering the returned values would adhere this filtering too, no?

So I could add country and votes to the facet doc, filter on country:us would give me different values of counts for votes than filter on country:uk?

// Ryan

Sent from my iPhone

[Ayende Rahien]

Yes, you could do that, sure.

[Johan Nordberg]

Thanks a lot! However, I cant get this to work and since I'm new to this I don't really know what's wrong. I've created a class like this:

public class RankingByCountry : AbstractIndexCreationTask<Contribution>
    {
        public RankingByCountry()
        {
            // map
            Map = docs =>
                from contrib in docs
                where contrib.IsOnline
                select new { contrib.Country, VoteRanking = new[] { contrib.PositiveVotes } };
 
            // Reduce
            Reduce = results => from result in results
                group result by result.Country into g
                select new { Country = g.Key, VoteRanking = g.SelectMany(x=>x.PositiveVotes).OrderByDescending(x=>x).Distinct() };
        }
    }

However, Visual Studio highlights g.SelectMany(x => x.PositiveVotes) and says that:

The type arguments for method 'System.Linq.Enumerable.SelectMany<TSource,TResult>(System.Collections.Generic.IEnumerable<TSource>, System.Func<TSource,System.Collections.Generic.IEnumerable<TResult>>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Any ideas?

[Ayende Rahien]

What is the type of PositiveVotes?

[Johan Nordberg]

int

[Johan Nordberg]

Any ideas? PositiveVotes is of type "int".

[Ayende Rahien]

You need to create a type that matches the values that you output in the map
And that type should be the second argto the index creation task

[Johan Nordberg]

Thanks! Got it working! Thanks for all your help!