Liferay 6: SQL query to get non-instantiated portlets

This SQL query lets you know the list of custom portlets that are not-instantiated (that has no entry in portlet preferences table).

(I wrote this query for a MS SQL DB, if you want to use it for another DB there are a few changes to make)

It only checks for portlet ids that are not numbers (to exclude Liferay portlets).

This query is based on the fact that if a portlet is used, it has an entry in table PortletPreferences.

select distinct all_portlets.portletId 
    (select portletId from Portlet where IsNumeric(portletId)<>1) all_portlets
left join 
        select distinct p.portletId
            (select portletId, len(portletId) as len from Portlet where IsNumeric(portletId)<>1) p,
            PortletPreferences pp
        where left(pp.portletId, p.len) = p.portletId
    ) used_portlets
on all_portlets.portletId = used_portlets.portletId
where used_portlets.portletId is null;

You can have the list of portlets that are instantiated by changing the last line of the query from where used_portlets.portletId is null to where used_portlets.portletId is not null

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.