How to set maximum return score for many-to-one
I have a product class that has a one-to-many relationship to the price class. Thus, a product can have multiple prices.
I need to query db to get 10 products that have Price.amount <$ 2. In this case, it should populate the UI with 10 elements per page. so I write the following code:
ICriteria criteria = session.CreateCriteria(typeof(Product));
criteria.SetFirstResult(pageNumber);
criteria.SetMaxResults(numberOfItemInPage);
criteria = criteria.CreateCriteria("PriceCollection");
criteria.Add(Restrictions.Le("Amount", new Decimal(2)));
criteria.SetResultTransformer(CriteriaSpecification.DistinctRootEntity);
Instead of getting 10 products on the list, I get less (i.e. 5). The reason why SetMaxResults (10) is returning 10 products to me, but with duplicates. Then the duplicates are removed by SetResultTransformer (DistinctRootEntity).
Can anyone tell me so that I can get 10 unique products without increasing SetMaxResults ()? I need to use pagenumber as some sort of indexing.
Your problem with duplicates seems to stem from the fact that you are joining two tables and therefore you can get the same product as many times as you have the price of it.
How to add 2 additional columns to the product table:
MinimumPrice (numeric(18,2)
MaximumPrice (numeric(18,2)
Whenever your system changes the price for a product, you update these two fields on the product. Now you can write a SQL query like:
SELECT TOP 10 * FROM Product
WHERE MinimumPrice > 2.0
And you won't have duplicate products.
source to share
Will the order of the statements matter? It looks like it sets the maximum score before, and in the end the duplicates that are applied in that order may be less than what you have limited it according to what you described.
I would think that you would need to efficiently get all the results, then apply a constraint (and maybe sort?) And filter out duplicates, and then finally apply a paging or counter listing to those to get the first 10, the next 10, and etc. Therefore, reordering the statements to reflect this logical order may help correct your mistake.