How to get data from associated domain table?

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

How to get data from associated domain table?

Post by dheeraj » 27 Jan 2021, 09:40

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


I created product manager object and using it i can get product items from product domain.
Each product is associated to stock domain through 'productcode'. My problem is that i want to get product item data along with its stocklevel from stock domain but only through single SELECT query which is possible through JOINing mshop_product and mshop_stock tables on productcode. But i don't know how to achieve this in aimeos!! :|
For now, i am first querying the product item using product manager and then fetching stock item for the product code using stock manager. :roll:

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

Re: How to get data from associated domain table?

Post by aimeos » 29 Jan 2021, 09:10

If you only want to fetch stock information, you can use:

Code: Select all

$items = $manager->search( $manager->filter(), ['stock'] )
This will return the associated stock items which you can access using:

Code: Select all

foreach( $items as $item ) {
	$stocklevel = $item->getStockItems()->getStockLevel()->first();
}
The interfaces define the methods available:
https://github.com/aimeos/aimeos-core/b ... hp#L41-L47
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

User avatar
dheeraj
Posts: 26
Joined: 02 Jan 2021, 08:47

Re: How to get data from associated domain table?

Post by dheeraj » 29 Jan 2021, 11:30

I want to fetch product + stock details for only those products whose stock level is below threshold value (say 5).
So, i think your solution will not work as it is first fetching products items using some filter and then accessing their stock details one by one.
The logical approach here i think is -
1. Fetch stock items first having stock level below threshold.
2. For each stock item, fetch the product item using the product code present in stock item.
But this is not good solution as for each stock item i am again querying the database for product item. So, number of extra queries = number of stock items.
I want to implement a good solution using JOINs and the following SQL solves my problem -

Code: Select all

SELECT mshop_product.*, mshop_stock.* FROM mshop_product INNER JOIN mshop_stock ON mshop_product.code = mshop_stock.productcode WHERE mshop_stock.stocklevel < my-threshold;
How can i achieve this in aimeos? :roll:

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

Re: How to get data from associated domain table?

Post by aimeos » 01 Feb 2021, 08:35

The best solution would be to add an "in-stock" attribute to the products and remove it again if the stock level reached 0. This is something that can be done in a decorator for the stock manager. There have been also some discussion about this here:
https://github.com/aimeos/aimeos-laravel/issues/358
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

User avatar
dheeraj
Posts: 26
Joined: 02 Jan 2021, 08:47

Re: How to get data from associated domain table?

Post by dheeraj » 01 Feb 2021, 12:25

Adding in-stock attribute would only allow me to know whether the product is in stock or not.
But my need is different.
I am implementing an admin panel where i am displaying the list of all products with their stock level and threshold (custom added attribute) which will contain all out of stock products as well as those product which will soon get out of stock (stock level < threshold).
So i don't think the in-stock attribute will do any good here.
I have already implemented this functionality but it is querying the database multiple times.
Now, i am improving the functionality by using a single join query but i don't know how aimeos handle JOINs for querying foreign table data through manager (Product or Stock Manager).

Is it possible to get data from mshop_product and mshop_stock tables for a given product code using a JOIN ? :roll:

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

Re: How to get data from associated domain table?

Post by aimeos » 03 Feb 2021, 09:04

There have been some discussions about that and a basic implementation which has been reverted due to multiple issues. You can find the discussion and the links here:
help-f15/how-to-hide-unique-products-in ... t1104.html

Main problem is that it's only working with MySQL and you need to add appropriate indexes to avoid performance problems.
Professional support and custom implementation are available at Aimeos.com
If you like Aimeos, Image give us a star

Post Reply