Find number of sites in Liferay, using SQL

Question

I want to know the number of sites in a Liferay instance.

If I had access to the web UI I would go to Configuration then Sites and see their number, but I don't have access to the web UI, I only have access to the MySQL database.

When I run SELECT COUNT(*) FROM Group_; I get 16584, which is obviously too much as I expect the instance to have about a hundred sites. When I look at the detail, I see that the 16584 sites include "sites" like /name-surname for each user.

Question: Using only MySQL requests, how can I find the number of "actual" Liferay sites? That number should match (or nearly match) the number that would be seen by an administrator viewing the list at Configuration>Sites.

Answer 1

Using the Group_ table, select based on entries with a 'site' value of 1. That should give you results which correspond to Configuration > Sites.

Answer 2

You probably expect me to resist giving this answer. I've decided to give it a try, but only after an important disclaimer. You (Nicolas) probably know what to expect, but as this is also going to be found by others, I feel that this is critical information:

In general, you shouldn't try to understand Liferay's database structure - especially not because this might lead to the temptation to write to it (because you assume to understand it). That being said, occasional read access might be easier than the (rather appropriate) API access. In short: Go with the API whenever you can.

Look at the database structure: You'll find that the table has a "Site" flag, which seems to more or less correlate with non-personal sites and non-templates. It includes the "global" site as well, and also there are no other relationships that you won't pay attention to when selecting data from GROUP_ (like permissions, etc.) - Plus, you don't mention if you're only going for "independent" (top level) sites, or "organizational sites" as well. But with this information you should have the basic information to figure out what you need.

Answer 3

Based on Gavin's idea, here is the request that gives me roughly[1] the same sites as can be seen in Configuration>Sites:

SELECT groupId, friendlyURL FROM Group_ WHERE type_=1 OR type_=2;

There are also many sites with types 0, I guess these are for users.

[1]: Sites with type 3 are a real headache. Most of them are LFR_ORGANIZATION and do not appear in Configuration>Sites, but some DO appear in that list, maybe the ones whose name does not end with LFR_ORGANIZATION.