Filter users by an ordered product id?
Forum rules
Always add your Laravel, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
Always add your Laravel, Aimeos and PHP version as well as your environment (Linux/Mac/Win)
Spam and unrelated posts will be removed immediately!
Filter users by an ordered product id?
Laravel framework version: 9.52.4
Aimeos Laravel version: ~2022.10
PHP Version: 8.2.4
Environment: Linux
Hello,
I want to be able to filter users by a product id in their orders in:
/admin/default/jqadm/search/customer
Intended functionality:
1) Open the "Search" modal
2) Select "Order product id" in the first dropdown
3) Select "== equals" in the second dropdown
4) Enter product id in the input field
5) Click "Search"
6) Users are filtered by users that have orders with the specified product id
How can I do this?
Aimeos Laravel version: ~2022.10
PHP Version: 8.2.4
Environment: Linux
Hello,
I want to be able to filter users by a product id in their orders in:
/admin/default/jqadm/search/customer
Intended functionality:
1) Open the "Search" modal
2) Select "Order product id" in the first dropdown
3) Select "== equals" in the second dropdown
4) Enter product id in the input field
5) Click "Search"
6) Users are filtered by users that have orders with the specified product id
How can I do this?
Last edited by kdim95 on 19 May 2023, 13:58, edited 1 time in total.
Re: Filter users by an ordered product id?
I got something working for the filter itself, but it breaks when attempting to use it in the customer list.
When the form is submitted, there are no customer results and I get an "Error retrieving data" in the bottom right corner.
Error log:
This is what I have so far:
Override the default customer manager:
namespace: Aimeos\MShop\Customer\Manager
class: LaravelCustom
searchConfig:
__constructor:
After the changes, looking up by an ordered product id works like this:
When the form is submitted, there are no customer results and I get an "Error retrieving data" in the bottom right corner.
Error log:
Code: Select all
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AND mordba."id" AND mordbapr."id" = '26' ) ) )
GROUP BY mcus."id"
...' at line 7:
SELECT COUNT(*) AS "count"
FROM (
SELECT mcus."id"
FROM "users" mcus
LEFT JOIN "mshop_order_base" AS mordba ON ( mcus."id" = mordba."customerid" )
LEFT JOIN "mshop_order_base_product" AS mordbapr ON ( mordba."id" = mordbapr."baseid" )
WHERE ( ( mcus."siteid" IN ('','1.') ) AND ( ( ( mordba."siteid" IN ('','1.') ) AND ( mordbapr."siteid" IN ('','1.') ) AND () AND mordba."id" AND mordbapr."id" = '26' ) ) )
GROUP BY mcus."id"
LIMIT 10000 OFFSET 0
) AS list
This is what I have so far:
Override the default customer manager:
Code: Select all
'mshop' => [
'customer' => [
'manager' => [
'name' => 'LaravelCustom',
],
],
]
class: LaravelCustom
searchConfig:
Code: Select all
'customer:order_product_id' => [
'code' => 'customer:order_product_id()',
'internalcode' => ':site_mordba AND :site_mordbapr AND :order_product_id AND mordba."id" AND mordbapr."id"',
'internaldeps' => [
'LEFT JOIN "mshop_order_base" AS mordba ON ( mcus."id" = mordba."customerid" )',
'LEFT JOIN "mshop_order_base_product" AS mordbapr ON ( mordba."id" = mordbapr."baseid" )'
],
'label' => 'Customer has order with product id, parameter(<order product id>)',
'type' => 'null',
'internaltype' => 'null',
'public' => true,
],
Code: Select all
$this->searchConfig['customer:order_product_id']['function'] = function( &$source, array $params ) use ( $level ) {
$sitestr_mordba_str = $this->siteString( 'mordba."siteid"', $level );
$sitestr_mordbapr_str = $this->siteString( 'mordbapr."siteid"', $level );
$order_product_id_str = $this->toExpression( 'mordbapr."prodid"', $params, ( $params[0] ?? null ) ? '==' : '=~' );
$source = str_replace( [':site_mordbapr', ':site_mordba', ':order_product_id'], [$sitestr_mordbapr_str, $sitestr_mordba_str, $order_product_id_str], $source );
return $params;
};
Code: Select all
$customer_manager = \Aimeos\MShop::create( $context, 'customer' );
$customer_filter = $customer_manager->filter();
$customer_filter->add( $customer_filter->make( 'customer:order_product_id', [26] ), '!=', null );
$customer_results = $customer_manager->search( $customer_filter );
Re: Filter users by an ordered product id?
The SQL error is due to an empty condition between two brackets "()":
This is because your ":order_product_id" replacement can also create an empty "()" string. Check in your search function that there are actually values for the order product ID before using it as condition.
Code: Select all
mordbapr."siteid" IN ('','1.') ) AND () AND mordba."id"
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, give us a star
If you like Aimeos, give us a star