Creating filter with value of key as column name

Help for integrating the Laravel package
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!
User avatar
dheeraj
Posts: 26
Joined: 02 Jan 2021, 08:47

Creating filter with value of key as column name

Post by dheeraj » 27 Jan 2021, 07:23

Laravel : 8.12
Aimeos : 2020.10
PHP Version : 7.4.13
Environment : Windows

I have added a new column to mshop_stock table with name 'threshold'.
Now i need to get all stock items whose stocklevel value is less than or equal to the value in its corresponding threshold value.
I cannot find how to create such filter. add() method of filter object only adds 'column key' , 'operator' and 'fixed value' for the column key. But in our case, 'fixed value' is determined by the value in the 'threshold' column of corresponding row in mshop_stock table.
I cannot find anything related to this in documentation.
I created an SQL query manually and executed it in the table mshop_stock and its working.

Code: Select all

SELECT * FROM mshop_stock WHERE stocklevel <= threshold;
How can i create such filter for stock items ??? :roll:

User avatar
aimeos
Administrator
Posts: 7836
Joined: 01 Jan 1970, 00:00

Re: Creating filter with value of key as column name

Post by aimeos » 29 Jan 2021, 08:45

Like you've already noticed, it's not possible to use a column name as argument for conditions, only fixed values. This would be only possible in SQL but e.g. not in other storages like ElasticSearch. To support all type of storages, you have to add a "search function" like this:
https://github.com/aimeos/aimeos-core/b ... #L175-L183

It can contain storage (SQL) specific code that can be part of a query. In your case, something like

Code: Select all

'stock:low' => array(
	'code' => 'stock:low()',
	'internalcode' => '(msto."stocklevel" < msto."threshold)"',
	'label' => 'Low stock levels',
	'type' => 'boolean',
	'internaltype' => 'boolean',
	'public' => false,
),
should work. In your code, you add this condition by using:

Code: Select all

$filter->add( $filter->make( 'stock:low', [] ), '==', true )
This will create a MySQL query like:

Code: Select all

... WHERE (msto."stocklevel" < msto."threshold) = 1
And in PostgreSQL it will be:

Code: Select all

... WHERE (msto."stocklevel" < msto."threshold) = true
Because the "internalcode" is always compared to some value, it must return something that is comparable. In this case it's a boolean true/false value but in other cases, you need to add a subquery in the "internalcode" to match that need and compare against a (not) NULL value.

More information for using search functions is described here:
https://aimeos.org/docs/latest/infrastr ... -functions
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

Post Reply