Developer forum

Forum » Development » Best way to fetch a custom set of products

Best way to fetch a custom set of products

E-mail notifications
Imar Spaanjaars Dynamicweb Employee
Imar Spaanjaars
Reply
Posted on 10/09/2024 20:48:57

With GetProductsBySql being obsolete, what is the recommended way to fetch products based on custom logic? Execute a custom SWL statement like this:

SELECT ProductAutoId FROM EcomProducts WHERE ProductLanguageId = 'en' AND  ProductNumber IN ('Bla', 'Something')

And then feed the returned auto IDs into ProductService.GetByAutoIDs?

Or is there a more preferred way?

Imar

 


Replies

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Posted on 11/09/2024 09:38:21
Reply

GetProductsBySql does not exist in DW10, and is full of potential issues. So yes, you should use GetByAutoIDs which will handle paging against the DB and take care of caching etc.

Using a statement like SELECT ProductAutoId FROM EcomProducts WHERE ProductLanguageId = 'en' AND  ProductNumber IN ('Bla', 'Something') is very prone to performance issues, so you should ensure that you do not have a lot of product records, or that you need to start joining the product table to other tables wihtout ensuring query performance. Out of experience you will also have to ensure that you IN statement is taking care of the 2048 max.

In most cases you can find what you need in the index and extending the index with additional information if missing is a better option to ensure performance.

BR Nicolai

 
Imar Spaanjaars Dynamicweb Employee
Imar Spaanjaars
Posted on 12/09/2024 09:44:58
Reply
This post has been marked as an answer

Thanks Nicolai.

In my case,  SELECT ProductAutoId FROM EcomProducts WHERE ProductLanguageId = 'en' AND  ProductNumber IN ('Bla', 'Something') is fine. It gives me exactly the two products I need and with an index on ProductNumber that's probably as fast as it gets.

My use case is a custom web API where I need to fetch prices from Live Integration. I have code like this:

var products = GetProducts(...);
PrepareProductInfoProvider.FetchProductInfos(products, user);
products.ForEach(product => PrepareProductInfoProvider.FillProductValues(product, user));

My GetProducts methods gets the products from the Ecom ProductService and then I feed them into LI. So I don't think the index will help me here, and I need to get the products directly somehow. GetByAutoIDs seems to do what I need in this scenario.

Imar

 

Votes for this answer: 1

 

You must be logged in to post in the forum