Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


These support forums are now closed for new topics and comments.
Please head on over to http://eureka.ucommerce.net/ for support.

  • Andrei 64 posts 115 karma points
    Nov 19, 2010 @ 13:31
    Andrei
    0

    Search products and sort by custom property value

    Hey all, 

    I need some linq (nHibernate) help. I'm trying to produce a search query that yields sorted results by a custom property value. The scenario is like this: products have a custom field "SortDate" where editors can input a date to be used for sorting search results. The problem is that if I make reqular linq join I could be missing the products that don't have the property value saved, so I want to somehow simulate a left join. The starting query that produces an inner join - I guess looks like (simplified):

     var q = (from prod in Product.All()

       join pp in ProductProperty.All() on prod.ProductId equals pp.ProductId

       where prod.ParentProductId == null && prod.Name.Contains(searchString) && pp.ProductDefinitionField.Name == "SortDate"

        select new { Sku = prod.Sku, Name = prod.Name, SortDate = pp.Value == null ? pp.Value : "zzz"}).OrderBy(x=>x.SortDate);

     

    Do I have a chance to make the above as a sort of left join or I am heading the wrong way?

  • Søren Spelling Lund 1796 posts 2764 karma points
    Nov 22, 2010 @ 09:08
    Søren Spelling Lund
    0

    Hi Andrei,

    You might find this article useful: Querying Products by Custom Properties in uCommerce. Basically I'm using am embedded statement in my where clause. It might do the trick for you.

  • Andrei 64 posts 115 karma points
    Nov 22, 2010 @ 09:53
    Andrei
    0

    Hi Søren,

    I can find the products, by the property, but I was kinda' hopping to get them already sorted by sql in the result set and also have the property in the result set. Otherwise I have to do it in 3 steps:

    1. Find the products by that property

    2. Load the products and the property in some lists

    3. Programatically sort the products by that property

    So you see how this is a lot of overhead instead of getting the results already sorted by SQL in one fast query. This is easily doable with raw sql code, but looks like it's not that simple with linq.

  • Søren Spelling Lund 1796 posts 2764 karma points
    Nov 22, 2010 @ 12:55
    Søren Spelling Lund
    0

    You're right that some products would be missing if you don't do the outer join and there are ways of doing the outer join, but you got me thinking on a different approach. You're right that sorting products in memory is an option and it's straightforward to sort there as well so here's what I came up with:

    var q = from product in productRepository.Select()
    where product.ParentProductId == null
    select product;

    var products = q.ToList(); // LINQ brings back the products from the database at this point.

    var sorted = products.OrderBy(x => x.ProductProperties.Where(y => y.ProductDefinitionField.Name == "Sort").DefaultIfEmpty(new ProductProperty{Value = "0"}).SingleOrDefault().Value);

    The rahther long sort statement at the end takes into account missing values by using the DefaultItEmpty extension method which provides a default value whenever a null is encountered. I'm using 0 as my default, but you're free to add which ever value is appropriate for your scenario.

    It doesn't really matter whether you're sorting on the as part of the database call or in memory as you're dealing with the same resultset regardless.

    Let me know if it works for you.

  • Andrei 64 posts 115 karma points
    Nov 22, 2010 @ 13:13
    Andrei
    0

    Isn't that doing a lazy load of properties for each product in the list? Because now the products are in memory, but properties aren't. So when OrderBy is called it will iterate products in the list and for each it will hit sql with a query to load the property used for sorting, resulting in as many hits to sql as products are in the list.  If a search is yielding many results, that could be an issue for performance. 

  • Søren Spelling Lund 1796 posts 2764 karma points
    Nov 23, 2010 @ 10:26
    Søren Spelling Lund
    0

    Hi Andrei,

    The LINQ provider is smart enough to figure out to do it all in a single batch. So you're right in the sense that you will get extra roundtrips to the database, but you'll just get the one so you should be fine.

    Unfortunately the provider doesn't support doing it all in a single batch as the DefaultIfEmptyOperator is not implemented.

    If you want to cut down from two queries to one there's a trick to doing an outer join, but it does make your expression a little more complex to read.

Please Sign in or register to post replies

Write your reply to:

Draft