filter product id by pattern matching =~

[IvanIgniter]

I am using laravel 6.x, PHP 7.4, Docker desktop/Ubuntu and the Aimeos 2021 (aimeos-laravel: 2021.10.4 / aimeos-core: 2021.10.13)

I would like to make a filter product.id using =~ like search for '1' should return like '1', '111', '123' etc.
I tried $search = $manager->filter( false, true )->add( 'order.id', '=~', '1' ) but return only 1 row '1'.
I tried examples from docs https://aimeos.org/docs/latest/models/search-filter/ but no luck.
Please help what is the best way to filter id not string column?

[aimeos]

Enable debug logging (madmin/log/manager/loglevel = 7) and check the generated SQL statement to see what happens.

[IvanIgniter]

ss1.jpg (131.93 KiB) Viewed 1582 times

I am not sure where to set the loglevel = 7. Is that correct?

I would like to emphasize that there were no error while searching using =~ in order.id but why it will only return one exact match? But if I tried it with string filter =~ to a string column would return the correct expect values.

[aimeos]

The config is in your ./config/shop.php file:
https://github.com/aimeos/aimeos/blob/m ... p.php#L196

Even if there's no error, you need to see what SQL statement is generated to find out why the "=~" operator isn't behaving as expected.

[IvanIgniter]

Ok this is the log output after searching "2" after searching 27 orders

Time: 2.9537677764893ms
Class: Aimeos\MShop\Order\Manager\Standard
SELECT COUNT( DISTINCT mord."id" ) AS "count"
FROM "mshop_order" AS mord
LEFT JOIN "mshop_order_base" AS mordba ON ( mord."baseid" = mordba."id" )
LEFT JOIN "mshop_order_base_product" AS mordbapr ON ( mordba."id" = mordbapr."baseid" )
WHERE ( ( mord."siteid" = '' OR mord."siteid" = '1.' ) AND ( mordba."siteid" = '' OR mordba."siteid" = '1.' ) AND ( mordbapr."siteid" = '' OR mordbapr."siteid" = '1.' ) AND ( ( mord."id" LIKE 2 ESCAPE '#' ) AND ( ( mordbapr."siteid" = '' OR mordbapr."siteid" = '1.' ) AND 1 = 1 ) ) )

Any idea what is causing the sql statement to be like AND ( ( mord."id" LIKE 2 ESCAPE '#' )?

[aimeos]

Guess, the order ID is casted to an integer value because the mshop_order.id column is defined as INT and the manager specifies it as "integer" too.

It also doesn't make much sense to apply the LIKE operator to an integer value, doesn't it?

[IvanIgniter]

Yap seems like but this is our requirements once a user search for id "1" it should be able to display id starting with 1.
From my sample screenshot sql statement, I wonder how to do this in Aimeos?

aa2.jpg (223.31 KiB) Viewed 1531 times

[aimeos]

You can try to overwrite the order manager class and replace PARAM_INT by PARAM_STR here:
https://github.com/aimeos/aimeos-core/b ... d.php#L142

[IvanIgniter]

Are you suggesting modifying the the aimeos core???
Is there any other way without modifying it?

[aimeos]

No, do that in your own project-specific extension, please!