Sylius: [RFC] Sorting products by price

[lchrusciel]

On #7272 @Niiko tries to resolve problem with full_group_by exception on PostgreSQL and newer version of MySQL. But this solution will introduce a bug or feature(you decide).
The mystical problem is with sorting on database level. Currently, we are sorting based on the first variant. If we add channelPricing.price to the groupBy clause we will sort on the cheapest/most expensive variant(depending on ASC/DESC option). My question is, what should be the default behaviour for the following case:

    Background:
        Given the store operates on a single channel in "United States"
        And the store has a "Jack Daniel's" configurable product
        And the product "Jack Daniel's" has "Gentleman Jack" variant priced at "$50.00"
        And the product "Jack Daniel's" has "Jack Daniel's Old No.7" variant priced at "$30.00"
        And the store has a "Johnnie Walker" configurable product
        And the product "Johnnie Walker" has "Johnnie Walker Red Label" variant priced at "$20.00"
        And the product "Johnnie Walker" has "Johnnie Walker Blue Label" variant priced at "$125.00"

The current implementation will work as follows:

    Scenario: Sorting products by price of their first variant with ascending order
        When I sort products by the lowest price first
        Then the first product on the list should have name "Johnnie Walker" with price "$20.00"
        And the last product on the list should have name "Jack Daniel's" with price "$50.00"

    Scenario: Sorting products by price of their first variant with descending order
        When I sort products by the highest price first
        Then the first product on the list should have name "Jack Daniel's" with price "$50.00"
        And the last product on the list should have name "Johnnie Walker" with price "$20.00"

So it seems that by default variant resolver should take into account the sorting of returned items and fulfill following scenario:

    Scenario: Sorting products by price of their first variant with ascending order
        When I sort products by the lowest price first
        Then the first product on the list should have name "Johnnie Walker" with price "$20.00"
        And the last product on the list should have name "Jack Daniel's" with price "$50.00"

    Scenario: Sorting products by price of their first variant with descending order
        When I sort products by the highest price first
        Then the first product on the list should have name "Johnnie Walker" with price "$125.00"
        And the last product on the list should have name "Jack Daniel's" with price "$50.00"

[CoderMaggie]

tl;dr;

The problem is:
we have a set of two products, each having 2 variants:

| | Variant 1 Price | Variant 2 Price |
|---|---|---|
| Product 1 | 50$ | 30$ |
| Product 2 | 20$ | 125$ |

The product should be sorted:
A)

PRICE ASC: Product 2(20$); Product 1(50$); 
PRICE DESC: Product 1(50$); Product 2(20$);

B)

PRICE ASC: Product 2(20$); Product 1(50$); 
PRICE DESC: Product 2(125$); Product 1(50$);

[lchrusciel]

Same as @TheMadeleine with some example:

| Product | Variant | Price |
| ------------- | -------- | -----:|
| Jack Daniel's | Gentleman Jack | $50 |
| Jack Daniel's | Jack Daniel's Old No.7 | $30 |
| Johnnie Walker | Red Label | $20 |
| Johnnie Walker | Blue Label | $125 |

How should they be listed on product list when sorted?
Option A:
The cheapest first:

| Product | Price |
| ---------------- | -----:|
| Johnnie Walker | $20 |
| Jack Daniel's | $50 |

The most expensive first:

| Product | Price |
| ------------- | -----:|
| Jack Daniel's | $50 |
| Johnnie Walker | $20 |

Option B:
The cheapest first:

| Product | Price |
| ------------- | -----:|
| Johnnie Walker | $20 |
| Jack Daniel's | $50 |

The most expensive first:

| Product | Price |
| ------------- | -----:|
| Johnnie Walker | $125 |
| Jack Daniel's | $50 |

[pamil]

The first one makes no sense, we shall go with the second one.

[lchrusciel]

To do it we need a variant resolver that consider not only a product variant list, but also current sorting and channel.

[gabiudrescu]

@pamil I agree to disagree :)

on our instance, we plan to implement the first option mentioned by @lchrusciel, but the pricing display for the product Johnnie Walker would be: from 20$.

if you go for the 2nd one, a product listing becomes actually a product variant listing, which is something you don't necessarily want, especially if you have lots of variants of a product.

imagine you have 100 products, 10 per page, and each of them has around 5 variants (different colors and sizes combinations).

instead of having 10 pages of 10 products, you have 50 pages of 10 products. this could hurt:

• UX
• SEO

I perceive product listings as a collection of products, not as a collection of product variants.

To accommodate the behavior of a specific implementation, the way the variant used for sorting / filtering is picked when a specific product listing is being computed should be isolated in a class that can be easy rewritten by a user-land implementation.

So basically I should be able to:

• either select the first variant whenever I am sorting / filtering
• select the variant with the cheapest price
• select the variant based on a custom logic e.g. the one with the highest stock available

[Niiko]

I don't think that the behaviour choosen will induct in more products per page, in fact, it will always display 1 product (not matters on variants number), and the sorting will be on variants.

It's just a sorting issue :/

After reading your comment @gabiudrescu, i agree that the first option could be "reasonable" or "intuitive", but it's like when you push the elevator button "down" or "up".

Down means "i wan't to go down" or "elevator down has to reach me", it depends on the manufacturer...

To me, the cheapest is the product with the cheapest price, and vice versa.

Just because if you have a "variant" with a low price, you could interest customers, in most of all stores, you have "from XX€" to "seduce" customers.

Maybe we could add a registry/manager to add/remove theses behaviors and configure which we want in administration ? I haven't digg into the code, maybe this is already possible.

[gabiudrescu]

@Niiko imagine you have a perfume bottle on the website with 2 variants:

• one of 50ml - 50€
• one of 150ml - 100€

you sort by price ascending - cheapest first: you see the (parent) product with 50€ price
you sort by price descending - most expensive first: you see the (same parent) product with 100€ price

this doesn't make much sense for me, as a customer, to see the same product, with two different prices in the same product listing without any hint about multiple variants.

and yeah, "from XX€" might sound seducing at first, but after I've put some thought into it, I think it's the correct way of presenting the products.

[Niiko]

@gabiudrescu I think it's also a customer point of view, i disagree a little bit with your example. You can also have a filter by a volume attribute to be more consistent in such case.

For me, it describes more a cheapest/most expensive by volume case (as it can be for price by weight for example). I see often that on supermarket's e-commerces.

I checked on Amazon, and they act like i describe. Amazon my million
The product is : Lady Million Paco Rabanne EAU MON OR 50 ML de Paco Rabanne (and 80ML variant)

But it's not always the case, i checked on cerruti 1881 and it appears that there is only three products, with one with variants, it always take the 100ML variant in sorting (The three products are in 100ML bottle).
Maybe, there are "human" sorting rules, or it detect that they have all 100ML variant, but we are not at that level of sorting i think.

Anyway, i agree that your behaviour is correct too, it really depends on e-commerce products or owner vision and we should not be stuck in a way decided by "sylius".

We have to have the possibility to parametize that, like we do with shipping methods.