Category: Uncategorized

There seem to be no good way to show all Entry Assocations (e.g. entries that have listings for accesories or related products set),  in Commerce, so I just created some simple SQL scripts

Show all

SELECT CatalogAssociation.CatalogEntryId, CatalogEntry.Code, CatalogEntry.Name, CatalogAssociation.AssociationName
FROM    CatalogAssociation INNER JOIN
                 CatalogEntry ON CatalogAssociation.CatalogEntryId = CatalogEntry.CatalogEntryId

If you need the associated entries as well, dig into this table:
[dbo].[CatalogEntryAssociation]

Where association contains ‘Expired’

SELECT CatalogAssociation.CatalogEntryId, CatalogEntry.Code, CatalogEntry.Name, CatalogAssociation.AssociationName
FROM    CatalogAssociation INNER JOIN
                 CatalogEntry ON CatalogAssociation.CatalogEntryId = CatalogEntry.CatalogEntryId
				 where CatalogAssociation.AssociationName like '%Expired%'

Most common associations counted
A variant for showing the most common associations (counted) are:

SELECT distinct CatalogAssociation.AssociationName, Count(*) as Count 
FROM    CatalogAssociation INNER JOIN
                 CatalogEntry ON CatalogAssociation.CatalogEntryId = CatalogEntry.CatalogEntryId
				 GROUP BY AssociationName
				 order by Count Desc