Published on January 19, 2021.

I've recently been doing some work for a new client that is using NetSuite's SuiteCommerce Advanced (SCA) ecommerce offering. As part of their project, I had to create a few SuiteQL queries involving items, and specifically item images. This was the first time that I needed to access that type of data, and I thought I'd share what I learned.

Items With Images

The first query that I developed is used to return images for a specified item, based on its internal ID.

SELECT
	ItemImage.Item,
	ItemImage.Name,
	ItemImage.AltTagCaption,
	ItemImage.SiteList,
	File.CreatedDate, 
	File.LastModifiedDate, 
	File.FileType, 
	File.FileSize, 
	File.URL,
	MediaItemFolder.AppFolder
FROM
	ItemImage
	INNER JOIN File ON
		( File.Name = ItemImage.Name)
	INNER JOIN MediaItemFolder ON 
		(MediaItemFolder.id = File.Folder) 
WHERE
	( ItemImage.Item = 1402 )

The primary table for this query is ItemImage. As you might expect, it's used to map an item to its images. It only contains a few columns, including Item (the internal ID of the item that the image is associated with), the name of the image file, as well as text that serves as the alt tag when the image is published on the SCA store.

Using the file name, I joined from the ItemImage table to the File table. The File table stores information about the files stored in the File Cabinet, which includes item images. In this case, I'm using the File table to get the image's file type, size, and URL. (I've discussed the File table in the past, and specifically in "SuiteQL and the File Cabinet.")

And finally, I'm joining from the File table to the MediaItemFolder table. This table provides information about the File Cabinet's folders. In this query, I'm using the table to determine what folder each image is stored in.

About that join from the ItemImage table to the File table: Again, I'm making the join using the file name. That seems to me to be a little odd, because it's entirely possible for there to be multiple files in the File Cabinet with the same name. From what I can tell, there is another column on the underlying ItemImage table - named "nkey" - that is the ID of the specific File record that the ItemImage is related to. However, that file is not currently available via SuiteQL.

Items Without Images

The second query that I developed for this project returns all items that do not have images. In theory, it should be as easy as this.

SELECT 
	ID,
	ItemID
FROM 
	Item 
WHERE 
	ID NOT IN
		( SELECT Item FROM ItemImage )	

However, for whatever reason, the performance of nearly all queries that I've made against the ItemImage table has been horrible. I'm seeing frequent timeouts and errors, for what to me seems to be a simple query that should be able to use an index.

The closest that I've been able to get to a query for handling this is this.

	
SELECT
	*
FROM
	(
		SELECT 
			ROWNUM AS RN, 
			* 
		FROM
			(
				SELECT 
					Item.ID,
					Item.ItemID 
				FROM 
					Item 
				WHERE 
					Item.ID NOT IN
						( SELECT ItemImage.Item FROM ItemImage )
				ORDER BY
					Item.ID,
					Item.ItemID 						
			)

	)
WHERE
	RN BETWEEN 1 AND 1000

The primary query is nested as a subquery, two levels deep. And while the performance of this query is still terrible, it does (usually) result in a response.

This is a SuiteQL "mystery" as I like to call them. There's no reason why the simple version of the query shouldn't work, and why wrapping it in a series of subqueries is necessary.

Wrapping Up

I think the big takeaway from this post is that, if you're using SuiteCommerce Advanced and want to use SuiteQL to query images, then the ItemImage table is what you'll need. However, depending on how you're querying that table, you'll likely get odd performance results.

As always, I hope these queries prove to be helpful. If you have any questions, please feel free to reach out to me.