sorting by one field with another field is a specified value

[Sean Rock]

Hi

I've got a situation where i need to sort on a sub property of a document based on the value of another field, for example take Product.Prices.Currency.  Product is a document, Prices is a list of Price objects and Currency is a property of Price.

{
  "Prices":[
        {
              Currency:"EUG",
              Price: 1.0
        },
       {
              Currency:"EHG",
              Price: 1.1
        }
    ]
}

Currency is a string and will be something like EUR, EUG, EUI, GBP or empty. So when i do a query i need to sort by the Price field where the Currency field is equal to a value I want to pass in the query.

I'm scratching my head still....Is this possible or is there a better way to solve this?

Thanks.
Sean

[Oren Eini (Ayende Rahien)]

You need to output the field like this:

from x in docs.Items

select new {

   _ =  x.Prices.Select(a => CreateField("Price_"+ a.Currency, a.Price))

}

Then you can sort by Price_GBP

Hibernating Rhinos Ltd  

Oren Eini l CEO l Mobile: + 972-52-548-6969

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

 

--
You received this message because you are subscribed to the Google Groups "RavenDB - 2nd generation document database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sean Rock]

Ok I realize this is a case of not seeing the wood for the trees :)

I can just sort by the price, the currency is irrelevant.

[Sean Rock]

Thanks Oren, I think I'll try to just sort on just the price as it is numerical. The currency is really for extracting an account specific price for a product (based on currency code). However i'll also keep in mind your solution in case sorting on just price isn't producing the correct results.

thanks!

[Sean Rock]

Hi Oren

This doesn't quite work. The Price needs to be ordered by its type (double) however in this solution you've suggested it's treated as a string and doesn't sort correctly. For example when sorting in descending order i get something like this

9, 8, 7, 16

On Thursday, 18 September 2014 18:15:37 UTC+1, Oren Eini wrote:

[Oren Eini (Ayende Rahien)]

Sort by the numeric number, Prices_Price_Range

[Sean Rock]

Hi Oren

Thanks for your time, but I'm not sure I understand what you mean...

here is what i'm trying now

_ = product.FromPrices.Select(x => CreateField("Price_" + x.Currency, x.Price))


the x.Price column is a double but is being treated as a string and I need to treat it as a double.

thanks.

[Oren Eini (Ayende Rahien)]

See the discussion here:

http://ravendb.net/docs/2.5/appendixes/lucene-indexes-usage

With numerics, we output two fields.

Price_USD and Price_USD_Range

[Chris Marisic]

Yes you sort by decimals not by currency. Currency is merely a string formatting of a decimal number.

Also I would advise against using doubles, there are really weird floating point things that pop up with doubles. Unless you're writing a very specific math program that is going to do calculations in radians for example from Math.Sin, you should always be using the decimal type.

If you use doubles one of these days a customer is going to get a bill after taxes have been multiplied in for $100.0199999999999999 instead of $100.02

[Sean Rock]

Hi Chris, Oren

In this instance the currency is simply an internal code used to identify a "from price"  to a customer - its not the numeric value or formatting info. That price is always rounded after account discounts, promotions, tax and multivolumn pricing is applied and before its passed to sage.

However this isn't working - i'm getting results that are not ordered correctly.

Take this example, three products, each has several currency records - this is so an account specific 'from price' can be displayed - so each product has many {"Currency" : Price} elements stored with it. And the [from] price can differ based on your account currency code (which indicates an agreed exchange rate) that will map to a 'real' currency later on in the app.

some real input data (currency code can be empty or a code such as EUG, EWI etc)

product | currency code | price
AV105  |                       | 1.25
AV106  |                       | 1.85
AV701  |                       | 1.45

Ok so this should produce the following assuming a descending (hi - lo) ordering

AV106 AV701 AV105    no chance.

The results just seem to be all over, mixed up.

here is the part of the index that sets up the field

_ = product.FromPrices.Select(x => CreateField("FromPrice_" + x.Currency, x.Price) ?? 0)

and query like this

var query = session.Advanced.LuceneQuery<ProductFilterModel, Indexes.ProductFilter>();
query.OrderBy("FromPrice__Range"); // assuming currency is an empty string

It just doesn't appear to want to sort the items correctly. Is there a way to see the actual indexed data? I want to compare this to the input data.

Thanks.

 



 

Is there a way to view the actual index data so i can see whats actually in there?

[Kijana Woodard]

In studio you can see the index data. Are you in 2.5?

So......what are you trying to do?

You should write a test.

http://ravendb.net/docs/2.5/samples/raven-tests/createraventests

The Asserts will let us help you unambiguously without having to try to build the models in our heads.

--

[Chris Marisic]

On Monday, September 22, 2014 12:46:42 PM UTC-4, Sean Rock wrote:

here is the part of the index that sets up the field

_ = product.FromPrices.Select(x => CreateField("FromPrice_" + x.Currency, x.Price) ?? 0)

and query like this

var query = session.Advanced.LuceneQuery<ProductFilterModel, Indexes.ProductFilter>();
query.OrderBy("FromPrice__Range"); // assuming currency is an empty string

Is there an overload of CreateField that allows you to define the field type? If not, you might need to explicitly cast price to decimal . I'm also not very sure what that null coalescing operator does in that situation.

[Sean Rock]

Chris

I read somewhere that setting a default value as the price could be null would allow the field to be sorted based on the correct type.

[Sean Rock]

Chris

I've checked and there is no overload for the CreateField method that would specify the type however i've changed the type in the poco and makes no difference.

[Oren Eini (Ayende Rahien)]

a) If you are outputting multiple values on the same field, the sort order is not defined.

b) Please create a test for this.

Hibernating Rhinos Ltd  

Oren Eini l CEO l Mobile: + 972-52-548-6969

Office: +972-4-622-7811 l Fax: +972-153-4-622-7811

 

--

[Sean Rock]

Oren 

Thanks for you assistance its all sorted now.

for anyone else that comes across this.

https://github.com/ayende/ravendb/blob/master/Raven.Client.Lightweight/IDocumentQueryBase.cs#L580

On Thursday, 18 September 2014 18:07:52 UTC+1, Sean Rock wrote:

[Sean Rock]

How can I create additional fields similar to this where i need to create a dynamic field i can sort on?

On Thursday, 18 September 2014 18:15:37 UTC+1, Oren Eini wrote:

[Oren Eini (Ayende Rahien)]

I don't understand the question

[Sean Rock]

basically i need to do this....

from x in docs.Items

select new {

   _ =  x.Prices.Select(a => CreateField("Price_"+ a.Currency, a.Price)),
   _ =  x.RanksByCategoryId.Select(a => CreateField("CategoryRank_"+ a.CategoryId, a.Rank))

}

this results in a error (duplicate anonymous type property name.

I need to either sort on Price_Currency or CategoryRank_111  (where 111 is a category id and the range will be a int value)

Thanks.

[Oren Eini (Ayende Rahien)]

from x in docs.Items

select new {

   _ =  x.Prices.Select(a => CreateField("Price_"+ a.Currency, a.Price)),

   __ =  x.RanksByCategoryId.Select(a => CreateField("CategoryRank_"+ a.CategoryId, a.Rank))

}

Works

[Sean Rock]

aaah why didn't i try that lol. thanks!