Wrong logic in sorting query (aimeos kernel)

Sergunik · Post by **Sergunik** » 14 Sep 2018, 10:56

Hi all!

Situation:
I have a 14 products in some category. I Added different prices to each product depends of quantity (Minimum quantity). Added at least 4 different prices to each product.
After that opened category page with list of products.

Case 1:
Default sorting by relevance. All fine.

Case 2:
Lets change sorting to by price.
Now I see label in top of the list: 14 products.
But in list only 8 products (You could see another quantity. But less than 14, unfortunately).

Investigation:
Lets check queries:
mshop/index/manager/standard/count
mshop/index/manager/standard/search

Count of products works properly, instead of Search. Search is more complicated. Because need to implement sorting and grouping.

My query for retrieving products:

Code: Select all

SELECT mpro.`id`
FROM `mshop_product` AS mpro
LEFT JOIN `mshop_index_catalog` AS mindca USE INDEX (`idx_msindca_s_ca_lt_po`, `unq_msindca_p_s_cid_lt_po`) ON mindca.`prodid` = mpro.`id`
LEFT JOIN `mshop_index_price` AS mindpr USE INDEX (`idx_msindpr_s_lt_cu_ty_va`, `idx_msindpr_p_s_lt_cu_ty_va`) ON mindpr.`prodid` = mpro.`id`
WHERE ( mpro.`siteid` IN (1) AND ( mindca.`catid` IN (86) AND ( mindca.`catid` IS NOT NULL AND mindpr.`siteid` IN (1,1,1) AND mindpr.`listtype` = 'default' AND mindpr.`currencyid` = 'EUR' AND mindpr.`type` = 'default' AND mindpr.`value` >= '0.00' AND ( mpro.`status` = 1 AND ( mpro.`start` IS NULL OR mpro.`start` <= '2018-09-11 09:45:00' ) AND ( mpro.`end` IS NULL OR mpro.`end` >= '2018-09-11 09:45:00' ) ) ) ) )
GROUP BY mpro.`id`  , mindpr.`value`
ORDER BY mindpr.`value` ASC
LIMIT 48 OFFSET 0

I received 48 rows of product ids. Duplicated of course.
I added prices to sql-Select (SELECT mpro.id, mindpr.value). My result:

'3889','0.49'
'3889','0.52'
'3889','0.56'
'3889','0.85'
'1463','1.44'
'1463','1.45'
'1463','1.50'
'1463','1.57'
'1463','1.78'
'1463','2.27'
... and so on...
totally 48 records

After that Aimeos wants to get all product information by these Ids. Means, get all distinct information about these duplicated ids.
In result I see only 8 products.

Summary:
Unfortunately, functionality for multiply prices doesn't work properly.

---
aimeos/ai-laravel: 2018.07.4
laravel/framework: 5.5
PHP 7.0.30-0ubuntu0.16.04.1

Post by **aimeos** » 15 Sep 2018, 09:26

You are right, this makes problems since we did some changes for MySQL 5.7 because ONLY_FULL_GROUP_BY is then enabled by default. We are still looking for a solution and the best one might be something like this:

Code: Select all

SELECT mpro."id"  , MIN(mindpr."value") AS "index.price.value"
FROM "mshop_product" AS mpro
LEFT JOIN "mshop_index_price" AS mindpr ON mindpr."prodid" = mpro."id"
WHERE ( mpro."siteid" IN (1349) AND ( mindpr."siteid" IN (1349,1349,1349) AND mindpr."listtype" = 'default' AND mindpr."currencyid" = 'EUR' AND mindpr."type" = 'default' AND mindpr."value" >= '10.00' AND mindpr."quantity" 
= 1 ) )
GROUP BY mpro."id"
ORDER BY "index.price.value" ASC 
LIMIT 100 OFFSET 0

Post by **aimeos** » 16 Sep 2018, 11:15

We've released a new aimeos-core version (2018.07.21) which should fix this problem. Can you please check and verify?

Sergunik · Post by **Sergunik** » 17 Sep 2018, 08:17

aimeos wrote:We've released a new aimeos-core version (2018.07.21) which should fix this problem. Can you please check and verify?

Now it works properly.
Many thanks!

Help for Aimeos