Find number of sites in Liferay, using SQL

custom sql in liferay 7
liferay 7.2 portal properties

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.

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.

Custom SQL – Liferay Help Center, If you want to find a guestbook entry based on its name, message, and guestbook name, you must access the name of the entry's guestbook. Of course, with SQL  Liferay - is user Site Owner? Ask Question Asked 7 years, 1 month ago. Active 6 years, 9 months ago. Viewed 2k times Find number of sites in Liferay, using SQL. 0.

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

Developing Custom SQL Queries – Liferay Help Center, Suppose you want to find an event based on its name, description, and location name. In the Event Listing portlet, the event entity refers to its location by the  Liferay Service Builder finder methods are use to fetch the data based on columns. Liferay Service Builder will generate finder methods simply by configure the finder tag in service.xml file. We have finder tag so that we can configure the columns on which base we need to fetch the data from the table.

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.

Finding and Invoking Liferay Services – Liferay Help Center, Every Liferay service provides a local interface to clients running in the same JVM as Liferay Portal. Many local services ( *LocalServiceUtil classes) are called by  Custom SQL is the way to write native SQL queries in liferay portlet development. Generally we need requirement that might not be fulfill by service builder generated services such scenarios we will use Custom SQL.

Portal Properties - Index of, See liferay-portlet-app_7_0_0.dtd and the virtual-path element for more information. Defaults: Input a list of comma delimited custom SQL configurations. Defaults: Set the max number of columns in an ORDER BY clause. Defaults: When the server starts, the portal checks to ensure all system site roles exist. 1. We will use the same liferay project that we created while demonstrating the logger example. i.e. Using Logger in Liferay 7. 2. To use Dynamic Query, the first task is to determine what kind table we need to query on. By what kind of table, we mean “Liferay Generated Table” or “Custom Table”. i.

Liferay Administrator's Guide, Regardless of which method is used to install Liferay, you will need to to set up the database an application server to have that many permissions over the database. Structure Liferay provides an SQL script archive download on the web site. You will find that it contains a folder structure that is broken down by the type  Figure 4.9: Customizing string input fields to use spacious text areas and customizing date fields to filter-out past years are just a couple examples of the many things you can do with Liferay model hints. Well, you’ve learned the art of persuasion through Liferay’s model hints.

Pruning the Database – Liferay Help Center, Never use SQL directly on your database to remove records. Here are some common places to check for unused objects. Large tables with many records might contain lots of unused objects. Finding and removing unused objects associated with such tables reduces  Service Builder’s use in Liferay Portal demonstrates it to be a robust and reliable tool. Service Builder is easy to use and can save developers lots of development time. Although the number of files Service Builder generates can seem intimidating at first, developers only need to work with a few files in order to make customizations to their

Comments
  • The "roughly" is precisely why the actual solution is to not go with SQL, but rather through the API. Just sayin' ;)