5 Replies Latest reply: Sep 17, 2012 5:58 AM by Michael Peel-Oracle RSS

    Sort Behavior within Aggregated Records

    DevReddy
      Hi ,

      In our application we have 1 to many relationship btw produt and SKU, so while presenting it to user we are rolling up (on Product ID) and showing the only one record that has the lease sku price(the representative record in AggrRec)

      Its working fine for normal Navigation querys we just rolled and ensured we are always showing the least price to user.

      We also have a facility for a user to sort the products based on price (light to low or low to high).

      Here comes the problem. When the sort is high to low(desc) the representative record we are getting is the one with the maximum sku price.

      How can I tweak here and see that it always brings the least priced sku as representative records and also as per the user experience it should sort the representative records alone and gives them in descending order? is it possible in a single Navingation query?

      If not possible with Endeca API , whats the approach to achive this in Java?

      Finally all i need is the price shown to user will remain the same in any sort scenario, also maintains the sort order of representative records for user to see that the products are sorted high to low price

      Thanks
      Dev
        • 1. Re: Sort Behavior within Aggregated Records
          TimK
          Would it be too onerous to return back all of the representative records (Np=2 or setNavERecsPerAggrERec(ALL_ERECS_PER_AGGR)?

          Then possibly you could just iterate through all of the representative records in java and show the minimum price that way?

          -Tim
          • 2. Re: Sort Behavior within Aggregated Records
            Pravin Chikhale
            Hi Dev,

            You should also consider the performance hit when you try to get all records for every Aggregated record for just displaying price.

            You can use DERIVED_PROP element's with functions like MIN, MAX on price attribute and use this attribute for display. It will return MIN price.
            Edit Derived_props.xml in notepad and add below snippet,
            e.g.
            *<DERIVED_PROP FCN="MIN" NAME="priceDerived"*
            DERIVE_FROM="P_PriceStr"/>

            Thanks,
            Pravin.

            Edited by: Pravin Chikhale on Sep 16, 2012 12:17 PM
            • 3. Re: Sort Behavior within Aggregated Records
              Michael Peel-Oracle
              If this is a regular retail app, is it so you can display a "from £xxxx" label? If so, then Pravin's suggestion is spot on - derived properties. One caveat is that derived properties will calculate the min value from the current data. This is important to remember, as if the customer contracts out some records through dimension value choices (e.g. size), the derived property will be derived from remaining records (e.g. all of the selected size). This is usually preferred behaviour, but I've had some customers want this label to be across all skus - the only way to do that is to pre-process your data to tag each sku with a "minimum price" attribute.

              And avoid returning all records with an aggregated request - the Endeca impact isn't too bad, but you will absolutely kill the network if you've got a lot of skus per product.
              • 4. Re: Sort Behavior within Aggregated Records
                DevReddy
                It looks like I need to deal with lot other things if i go for Derived Props , bcz i also have Facet on price ranges where i cannot map the derived price to it...

                So it looks like i need to tag my source data records to identify which is the least priced sku row for product and make sure only that row is returned when user queries. This seems to be solving the problem 100% , but there is little more effort involved in tagging my source records.

                I will be jumping onto that section probably.

                Thanks for your thoughts on this query.

                Regards,
                DEV
                • 5. Re: Sort Behavior within Aggregated Records
                  Michael Peel-Oracle
                  Hi Dev

                  Dimensions etc. all work correctly with aggregated records, derived properties are only for returning additional data with a representative record. You do not want to programmatically exclude all but the lowest price sku for a product, as this will mean customers can't dynamically navigate at a sku level. If you consider how skus are typically represented, i.e. colours and sizes for a product, with potentially different prices for each permutation of colour and size, then using aggregated records allows you to support granular search and navigation and display accurate price information based on the actual skus returned for the search (as opposed to just the overall product).

                  Michael