What's the official way to generate a product image URL using SQL?

I am writing a feed query in SQL.  I would like to include image links in the feed, but I can't find any technical information on how to construct them in SQL. There are lots of parameters involved in generating an image URL that makes it quite a challenge. Images can be served from a database or host filesystem. They can have SEO names or NopCommerce generated names. They also can be served as thumbnails, standard sizes or large sizes. There are probably even more parameters. What makes it even more complex is that they can change depending on the store's configuration.

I found some code which may or not be applicable to the version of NopCommerce I'm using (version 4.1).  

SELECT 'http://www.website.com/Content/Images/Thumbs/' +
RIGHT('0000000' + CONVERT(NVARCHAR(32),npi.ID),7) + '_' + SeoFilename + '.jpeg' as 'Image Path'
FROM Product npr
INNER JOIN Product_Picture_Mapping npp ON (npr.Id = npp.ProductId)
INNER JOIN Picture npi ON (npp.PictureID = npi.ID)
WHERE npp.DisplayOrder = 1

Aside from the base URL, there's a lot of string processing involved with hard coded values such as the image folder.  It appears too simplistic to cover all the aforementioned cases.  Is there a recommended or official query or stored procedure demonstrating how to generate an image URL?

RE: Is there a recommended or official query or stored procedure demonstrating how to generate an image URL?

No, there is no official query regarding this.

To generate image url, it does not require too many db queries. So, even if you could manage all the conditions to generate that, performance will not increase too much.

mhsjaber wrote:

Do you know where I can find SQL code to do this? I'm running version 4.1.

What will be consuming this feed?  That is, what is the restriction for pure SQL?

Filesystem vs DB can be handled with a CASE WHEN on the "media.images.storeindb" field in the Setting table.  If the image is BinaryData in the DB then you're probably looking at creating a stream from the DB server to serve it up.  I can't say whether that's recommended, but a quick google may get you started in that direction:  [url] https://www.mssqltips.com/sqlservertip/4963/simple-image-import-and-export-using-tsql-for-sql-server/[/url]

If it's file system then you have to check on the "MultipleThumbDirectories" setting and then know where to look based on filename.  Predictable, but again I can't say if this is ideal in pure SQL.  May as well grab all the other "mediasettings" settings while you're at it, but as you suggest things are getting exponentially complex.  

However, if you're not locked into pure SQL and the end result is an image URL then something like a WebAPI endpoint may be preferred, or even easier, a custom controller that taps into the GetPictureURL method in Nop.Services/Media/PictureService.cs since all the hard work is already figured out there.

Have you looked at a Product Export (xlsx file)?  Does that image URL meet your needs?  If so, then see how the export is doing it, and then you may be able to 'reverse engineer' how it works into SQL.

Thanks for all the help. My feed is essentially exporting products and their images to another SQL database.  My export only needs a fully constructed string URL to the product image that's accessible. It doesn't require the binary picture stream.

It appears NopCommerce's SQL and data models are symmetric, but it's SQL and functional models are asymmetric.  While a product's data is can be easily constructed from code or SQL, the operations applied to the data such as GetProductUrl doesn't have an SQL equivalent.  This means the business logic such as price calculations are not exposed through SQL, making it harder to integrate with outside systems and build reports.