Query Liferay’s database to find documents marked searchable

This post shows you how to query Liferay’s database and see which Documents and Media files are marked ‘searchable’.  For these examples, I was using SQL Server and did not validate the queries work on other databases.  They are simple enough to port if you are running Oracle, DB2, or any other major relational database platform.

Figuring out how to query Liferay’s database for this information is not straightforward.  My original expectation was for the DLFileEntry table to have an ‘indexable’ column similar to the JournalArticle table.  “No big deal”, I thought to myself, “It must be on the DLFileVersion table”.  WRONG!!?!  When I did not see the ‘indexable’ field on the DLFileVersion table, and no references in ‘typeSettings’ data, I started feeling a bit uneasy.  How the heck is this info stored?

Finding any information on this topic prompted this blog post to help you query Liferay’s database to find Documents and Media marked as searchable.  I learned that Liferay uses Expando attributes to identify which documents are marked ‘searchable’.

Basic Documents and Media Query

Here is the basic starter to help you query Liferay’s database for the current documents and media entries.

SELECT FE.fileEntryId 
     ,FE.version 
     ,FE.title 
     ,EV.[data_] AS 'searchable' 
FROM [ExpandoValue] EV 
     INNER JOIN [ExpandoColumn] EC on EV.columnId = EC.columnId 
     INNER JOIN [DLFileVersion] FV on EV.classPk = FV.fileVersionId 
     INNER JOIN [DLFileEntry] FE on FE.fileEntryId = FV.fileEntryId 
WHERE EV.classNameId = 10010 
     AND EC.name = 'searchable' 
     AND FE.version = FV.version 
ORDER BY FV.fileEntryId, FV.version

This query should be enough to get a developer going or basic reporting.

Add Group_ and Organization_ tables to the query

For installations with multiple sites or organizations where content is stored, you will need to add joins on the

[Group_]

and

[Organization_]

tables. This will at least tell you where the documents are stored so someone can go into the Control Panel and find them.

SELECT G.name
     ,O.name
     ,FE.fileEntryId 
     ,FE.version 
     ,FE.title 
     ,EV.[data_] AS 'searchable' 
FROM [ExpandoValue] EV 
     INNER JOIN [ExpandoColumn] EC on EV.columnId = EC.columnId 
     INNER JOIN [DLFileVersion] FV on EV.classPk = FV.fileVersionId 
     INNER JOIN [DLFileEntry] FE on FE.fileEntryId = FV.fileEntryId 
     INNER JOIN [Group_] G on FE.groupId = G.groupId
     LEFT OUTER JOIN [Organization_] O on O.organizationId = G.classPK
WHERE EV.classNameId = 10010 
     AND EC.name = 'searchable' 
     AND FE.version = FV.version 
ORDER BY FV.fileEntryId, FV.version

Take note that when you query Liferay’s database with the queries above, you will not get all versions of the files. These queries only return the current file version.

Query for file version history

This next query will show you how to query Liferay’s database for the full file version history.

SELECT G.name
     ,O.name
     ,FE.fileEntryId
     ,FV.version
     ,FE.title   
     ,EV.[data_] AS 'searchable'
FROM [ExpandoValue] EV
     INNER JOIN [ExpandoColumn] EC on EV.columnId = EC.columnId
     INNER JOIN [DLFileVersion] FV on EV.classPk = FV.fileVersionId
     INNER JOIN [DLFileEntry] FE on FE.fileEntryId = FV.fileEntryId
     INNER JOIN [Group_] G on FE.groupId = G.groupId
     LEFT OUTER JOIN [Organization_] O on O.organizationId = G.classPK
WHERE EV.classNameId = 10010
     AND EC.name = 'searchable'
ORDER BY FV.fileEntryId, FV.version