Filter users by an ordered product id?

[kdim95]

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?

[kdim95]

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:

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',
		],
	],
]

namespace: Aimeos\MShop\Customer\Manager
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,
],

__constructor:

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;
};

After the changes, looking up by an ordered product id works like this:

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 );

[aimeos]

The SQL error is due to an empty condition between two brackets "()":

Code: Select all

mordbapr."siteid" IN ('','1.') ) AND () AND mordba."id"

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.