From Service Builder to Liferay Objects: Where Your Custom SQL Goes
The entity moved in a day. The team modelled Vendor, PurchaseRequest and RequestLine as objects, wired up the relationships, loaded a copy of the data with the Batch Engine, and had working REST endpoints before the afternoon stand-up. Then someone opened the old module's custom-sql/default.xml. Fourteen queries: joins, a couple of GROUP BYs, a NOT EXISTS, and one query the reporting team had been running every night for years.
<custom-sql>
<sql id="com.example.purchasing.service.persistence.PurchaseRequestFinder.findByOnHoldVendor">
SELECT PurchaseRequest.*
FROM Purchasing_PurchaseRequest PurchaseRequest
INNER JOIN Purchasing_Vendor Vendor
ON Vendor.vendorId = PurchaseRequest.vendorId
WHERE Vendor.onHold = [$TRUE$]
AND PurchaseRequest.stage = ?
ORDER BY PurchaseRequest.createDate DESC
</sql>
...
</custom-sql>That file is where most Service Builder to Objects migrations slow down. With Service Builder, you owned the tables. When a finder wasn't enough, you wrote SQL or used the DSL query framework, and anything the database could do, you could do. Objects take that away on purpose. What they give back isn't one replacement for SQL but a stack of layers, each good at a particular kind of query. The work is sorting each old query into the right layer, and recognising the few that don't belong in Objects at all.
If you're still deciding whether an entity should be an object in the first place, Objects vs. Service Builder covers that choice. This post assumes the decision is made and deals with what comes next.
You don't own the schema anymore
Objects do live in ordinary database tables. A published object gets a table named along the lines of O_<companyId>_<ObjectName>, fields added after publishing go into an extension table next to it, a one-to-many relationship becomes an r_<relationshipName>_c_<parentObject>Id column on the child, and a many-to-many relationship gets its own join table. It's all visible in a database client, and it's very tempting to point the old SQL at it.
Don't. Those names are an implementation detail Liferay can change between releases, and a query against them skips everything the platform does on the way in and out:
- Permissions. The API only returns entries the caller is allowed to see. Raw SQL returns everything. How Liferay's permission framework works explains what you'd be skipping.
- Workflow status. Drafts, pending and expired entries sit in the same table as approved ones.
- Scope. A site-scoped object keeps every site's entries in one table.
- The search index. Writes made with SQL never reach it, so search results and the database drift apart.
Reading those tables from a reporting tool is a separate question, covered near the end. For application code, the rule is simple: go through the platform.
Triage the custom SQL file first
Before writing any code, go through the old queries one at a time and ask what each one is actually for. In a typical module they fall into a handful of shapes, and each shape has a natural home:
| What the old query did | Example | Where it goes in Objects |
|---|---|---|
| Filter, sort and page one table | Approved requests over 1,000, newest first | OData parameters on the headless API |
| Load a parent with its children | A request with all its lines | nestedFields |
| Filter by a column on a joined table | Requests from vendors that are on hold | Relationship filtering |
| Roll child rows up onto the parent | Total amount per request | Aggregation field |
| Full-text search, facets, big listing screens | Search requests by description, faceted by stage | The search index |
| Logic across unrelated data, or outer joins | Vendors with no requests in 90 days | A custom endpoint |
| GROUP BY reporting across several tables | Monthly spend per vendor | A reporting store, not the API |
Most files are dominated by the first four rows. The sections below take the layers in order, from the ones you get for free to the ones you have to build.
Layer 1: OData on the headless API
Every published object gets REST endpoints under /o/c/<plural-name>/, and the collection endpoint accepts OData-style parameters:
- filter: comparisons (eq, ne, gt, ge, lt, le), and/or/not, functions such as contains and startswith, and in for a list of values.
- sort: one or more fields, each with :asc or :desc.
- search: keyword search across the entry's text.
- page and pageSize: pagination, with totalCount and lastPage in every response.
- fields: return only the fields you list.
- nestedFields: include related entries (next section).
curl -G "https://portal.example.com/o/c/purchaserequests/" \
-H "Authorization: Bearer $TOKEN" \
--data-urlencode "filter=stage eq 'approved' and amount gt 1000" \
--data-urlencode "sort=dateCreated:desc" \
--data-urlencode "fields=id,title,amount,stage,dateCreated" \
--data-urlencode "page=1" \
--data-urlencode "pageSize=20"That one call replaces the commonest kind of custom finder: a WHERE clause, an ORDER BY and a LIMIT. It also applies permissions, respects workflow status, and returns the total count for the pager, which used to be a second query you wrote by hand. The headless APIs post covers authentication and the rest of the API surface.
One habit from SQL needs adjusting. You can't add your own database indexes to object tables, so you have fewer levers when a filter is slow on a large table. If a screen filters a big object on several fields, test that filter on realistic data early, and be ready to move it to the search index (Layer 6).
Layer 2: Relationships instead of joins
In Service Builder, a join was something you wrote into a query. In Objects, it's something you declare once, as a relationship, and then use from any API call. Most joins in a custom SQL file turn out to be one of two things: loading related rows, or filtering by them.
Loading related entries
nestedFields names the relationships to include in the response, so a request comes back with its lines in one call:
curl -G "https://portal.example.com/o/c/purchaserequests/42" \
-H "Authorization: Bearer $TOKEN" \
--data-urlencode "nestedFields=purchaseRequestToRequestLines"nestedFieldsDepth lets the response follow relationships more than one level, from a vendor to its requests to their lines. The depth is capped (five levels at the time of writing), and each level multiplies the payload, so ask for the depth the screen needs and no more. For a parent with a large number of children, skip nesting and page through the child endpoint, filtered by the parent, instead.
GraphQL at /o/graphql covers the same ground, if a client would rather pick its fields that way, and REST vs GraphQL in Liferay compares the two. GraphQL can also fetch two unrelated object lists in one round trip, but it doesn't join them. Combining them is still your code.
Filtering by related entries
The child side of a one-to-many relationship carries the parent's ID as a field, so "all requests for this vendor" is an ordinary filter:
filter=r_vendorToPurchaseRequests_c_vendorId eq 12345The custom SQL at the top of this post went one step further: it filtered requests by a field on the vendor. Recent 7.4 updates support that too, by using the relationship name as a path to the related field:
filter=vendorToPurchaseRequests/onHold eq true and stage eq 'approved'That's the whole join from the opening example, and the platform writes the SQL. When a filter needs to cross three or more objects with mixed and/or logic, it usually means the model is missing something, often a relationship or a copied field directly on the object being listed. The hard cases section below comes back to this.
Layer 3: Aggregation and formula fields
A surprising share of custom SQL exists to compute something: a total, a count, the latest date. Objects can hold many of those as fields.
- Aggregation fields compute a count, sum, average, minimum or maximum over a relationship. PurchaseRequest gets a totalAmount field that sums the amount of its RequestLine entries, and the aggregation can carry its own filters, such as summing only lines that aren't cancelled.
- Formula fields compute a value from other fields on the same entry, such as an amount including tax.
Both appear in API responses and the admin UI like any other field, which removes a whole class of "parent plus rolled-up child data" queries.
Check one thing before you design a screen around them. These values are computed when the entry is read, not stored with it, and depending on your release that can limit whether you can filter or sort on them through the API. If you need "requests over 5,000 in total, largest first" and your release won't filter on the aggregation, keep a stored total instead: a plain decimal field that an object action recalculates whenever a line is added, changed or deleted. It's more moving parts, but it's an ordinary field, so every filter, sort and index works on it.
Layer 4: A custom endpoint for what the API can't express
Some queries don't fit any built-in layer: matching on a business key with no relationship behind it, flattening columns from several objects into one row, or anything with outer-join semantics, such as "vendors with no requests in the last 90 days". For these, put an endpoint in front of the objects that does the work and returns exactly the shape the front end needs. This is the new home for the custom finder.
There are two ways to build it:
- A microservice client extension. It works on every deployment model, including SaaS. It calls the objects' headless APIs with an OAuth2 token, combines the results, and serves its own endpoint.
- A REST Builder or JAX-RS module, on PaaS and self-hosted. It runs inside the portal and can use the Java options in the next section.
Here's the vendors-without-recent-requests query as microservice code. It reads two collections through the API and does the anti-join in memory:
async function vendorsWithoutRecentRequests(since) {
const vendors = await fetchAll("/o/c/vendors/", { fields: "id,name" });
const recentRequests = await fetchAll("/o/c/purchaserequests/", {
fields: "r_vendorToPurchaseRequests_c_vendorId",
filter: `dateCreated ge ${since.toISOString()}`,
});
const activeVendorIds = new Set(
recentRequests.map((request) => request.r_vendorToPurchaseRequests_c_vendorId)
);
return vendors.filter((vendor) => !activeVendorIds.has(vendor.id));
}
async function fetchAll(path, params) {
const items = [];
for (let page = 1; ; page++) {
const query = new URLSearchParams({ ...params, page, pageSize: 500 });
const response = await fetch(`${PORTAL_URL}${path}?${query}`, {
headers: { Authorization: `Bearer ${await getToken()}` },
});
if (!response.ok) {
throw new Error(`${path} returned ${response.status}`);
}
const { items: pageItems, lastPage } = await response.json();
items.push(...pageItems);
if (page >= lastPage) {
return items;
}
}
}Pulling data over the wire to join it in code feels wasteful to anyone used to SQL, and at high volume it is. At the volumes most application queries run on, it's simple, every call goes through the same permission checks as any other API call, and nothing in it depends on table names that can change in the next release. If the result feeds a paged screen, page the final result rather than the inputs; why pagination is harder than it looks explains why.
Layer 5: Java inside the portal (PaaS and self-hosted)
If your custom endpoint runs inside the portal, you have two Java options, and they aren't equally safe.
The first is ObjectEntryManager, the layer the REST API itself calls. You get it from ObjectEntryManagerRegistry for the object's storage type and call it with a filter string, sorts and pagination. It takes the same OData filter syntax, applies the same permission checks, and is the closest thing to a stable Java contract for querying objects. Its method signatures have still shifted between 7.4 updates, so check the API for your release.
The second is the DSL query framework, the one Service Builder developers used directly. Objects are built on it internally. An object's table is available as a DynamicObjectDefinitionTable, ObjectEntryLocalService has getValuesList overloads that take a DSL Predicate, and a filter factory in the object REST module can turn an OData filter string into one:
ObjectDefinition objectDefinition =
_objectDefinitionLocalService.fetchObjectDefinition(
companyId, "C_PurchaseRequest");
Predicate predicate = _filterPredicateFactory.create(
"stage eq 'approved' and amount gt 1000",
objectDefinition.getObjectDefinitionId());
List<Map<String, Serializable>> rows =
_objectEntryLocalService.getValuesList(
groupId, companyId, userId,
objectDefinition.getObjectDefinitionId(), predicate, null,
QueryUtil.ALL_POS, QueryUtil.ALL_POS, null);Writing the join yourself with DynamicObjectDefinitionTable
getValuesList still queries one object at a time. To write a real join, build the tables yourself. A DynamicObjectDefinitionTable is a DSL Table assembled at runtime from an object definition and its fields, so it plays the part the generated *Table classes played in Service Builder. Two small helpers do the setup:
private DynamicObjectDefinitionTable _getTable(
long companyId, String objectDefinitionName) {
ObjectDefinition objectDefinition =
_objectDefinitionLocalService.fetchObjectDefinition(
companyId, objectDefinitionName);
return new DynamicObjectDefinitionTable(
objectDefinition,
_objectFieldLocalService.getObjectFields(
objectDefinition.getObjectDefinitionId(),
objectDefinition.getDBTableName()),
objectDefinition.getDBTableName());
}
@SuppressWarnings("unchecked")
private <T> Column<DynamicObjectDefinitionTable, T> _getColumn(
DynamicObjectDefinitionTable dynamicObjectDefinitionTable,
String fieldName) {
ObjectDefinition objectDefinition =
dynamicObjectDefinitionTable.getObjectDefinition();
ObjectField objectField = _objectFieldLocalService.fetchObjectField(
objectDefinition.getObjectDefinitionId(), fieldName);
// Look up the column by the field's DB column name, never a hardcoded one
return (Column<DynamicObjectDefinitionTable, T>)
dynamicObjectDefinitionTable.getColumn(
objectField.getDBColumnName());
}With those in place, the custom SQL from the top of this post becomes a DSL query. It joins the two object tables through the relationship column, joins ObjectEntryTable for the workflow status and create date (both live there, not in the object's own table), and returns flat rows with columns from both objects. That flat projection is exactly what the REST API can't give you:
public List<Object[]> getApprovedRequestsFromOnHoldVendors(
long companyId) {
DynamicObjectDefinitionTable requestTable = _getTable(
companyId, "C_PurchaseRequest");
DynamicObjectDefinitionTable vendorTable = _getTable(
companyId, "C_Vendor");
Column<DynamicObjectDefinitionTable, Long> requestIdColumn =
requestTable.getPrimaryKeyColumn();
Column<DynamicObjectDefinitionTable, Long> vendorIdColumn =
vendorTable.getPrimaryKeyColumn();
DSLQuery dslQuery = DSLQueryFactoryUtil.select(
requestIdColumn, _getColumn(requestTable, "title"),
_getColumn(requestTable, "amount"),
_getColumn(vendorTable, "name")
).from(
requestTable
).innerJoinON(
vendorTable,
vendorIdColumn.eq(
_getColumn(
requestTable, "r_vendorToPurchaseRequests_c_vendorId"))
).innerJoinON(
ObjectEntryTable.INSTANCE,
ObjectEntryTable.INSTANCE.objectEntryId.eq(requestIdColumn)
).where(
ObjectEntryTable.INSTANCE.status.eq(
WorkflowConstants.STATUS_APPROVED
).and(
_getColumn(vendorTable, "onHold").eq(Boolean.TRUE)
)
).orderBy(
ObjectEntryTable.INSTANCE.createDate.descending()
);
return _objectEntryLocalService.dslQuery(dslQuery);
}Every select, join, where and orderBy here is the same DSL you used with Service Builder, and dslQuery is the standard method every local service has. The difference is that the tables come from the object definition at runtime instead of from generated code, so a renamed field or a changed relationship turns into a runtime failure, not a compile error. Cover these queries with integration tests that run against real object definitions.
This is the only route that gets close to the old freedom, including joins across object tables and many-to-many join tables. It also leans on classes Liferay treats as internal, and the query above applies no permission checks at all: it returns every matching row, whoever is asking. Check permissions on the results yourself, or only use it for data the caller is already allowed to see in full. Keep it behind one service of your own, so an upgrade breaks one class rather than every caller.
Neither option exists on SaaS, where you can't deploy custom Java. There, the microservice from the previous section is the answer.
Layer 6: The search index
Object entries are indexed in Elasticsearch or OpenSearch, and for some query shapes that's the better engine: full-text search, faceted navigation, counts by category, and big listing screens where users filter and sort interactively. Those are the queries search engines are built for, and moving them there takes load off the database. You reach the index through the search widgets and, on DXP, Search Blueprints, or through the search APIs from a module. Understanding search architecture in Liferay explains how the pieces fit.
The index can also answer some cross-object questions, as long as you put the data there. Search can't join, so denormalize instead: copy the vendor's name and on-hold flag onto each PurchaseRequest as ordinary fields, kept current by an object action on Vendor. The copies are indexed with the request, and "requests from on-hold vendors, faceted by stage" becomes a single search. The cost is a fan-out write every time a vendor changes, so copy only the handful of fields that screens actually filter on.
Layer 7: Reporting belongs somewhere else
The query the reporting team ran every night (monthly spend per vendor, grouped and summed across three tables) isn't an application query, and no layer of the objects API is designed for it. The API returns entries, not result sets.
Give reporting its own store. Export from Objects into a database or warehouse the reporting team controls, and let them write all the GROUP BYs they like there:
- Scheduled Batch Engine exports for nightly or hourly loads.
- Object actions that push changes to the reporting store as they happen, for near-real-time reports.
The reporting schema is yours, so you can index it, flatten it and join it however the reports need, and nothing in it breaks when Liferay changes an object table. It also removes the real risk in the old setup: a reporting tool wired directly to the portal's internal tables. The Expando to Object migration ran into the same lesson: moving to Objects improves the write model, but you still have to design the read model.
At the far end of the spectrum, Objects support custom storage types. You implement your own ObjectEntryManager, and the object keeps its admin UI and REST API while the data lives in tables you design. Your SQL works again. It's a significant piece of engineering, only possible where you can deploy Java, and only justified when a domain is genuinely relational and you still want the Objects experience on top. More often, a domain like that should simply stay in Service Builder.
The hard cases, side by side
| Query shape | Why it's hard | Best option |
|---|---|---|
| Join on a business key with no relationship | There's no relationship to traverse | Add a relationship if the link is real; otherwise a custom endpoint |
| Flat rows combining columns from several objects | The API returns entries, not projections | A custom endpoint, or copied fields on the main object |
| GROUP BY across objects with conditions on each | There's no grouping in the API | An aggregation field if it's per parent; otherwise a reporting store |
| Filters across three or more objects with mixed logic | Relationship filters only go so far | Copy the filtered fields onto the listed object, then use OData or search |
| Outer joins, such as vendors with no requests | There's no anti-join in OData | A count aggregation, if your release filters on it; otherwise a custom endpoint |
| Anything the reporting team runs on a schedule | It isn't an application query | A reporting store |
What happens to the file
Run this triage on a real custom-sql file and the result is usually lopsided. Most queries turn into OData parameters and relationships, and get deleted rather than migrated. A few become aggregation fields. One or two become a custom endpoint. The reporting queries move to a reporting store, where they arguably always belonged. Now and then, one shows that a corner of the domain is too relational for Objects and should stay in Service Builder. That's a fine outcome, not a failure: a hybrid with Objects for the configurable entities and Service Builder for a query-heavy core is a perfectly healthy architecture on PaaS and self-hosted.
Objects trade raw SQL for automatic APIs, permissions, workflow and an upgrade path you don't have to carry. For most queries, OData, relationships and aggregation fields are enough. For the rest, you have custom endpoints, Java inside the portal, the search index and a reporting store. The change is to stop asking where the SQL goes and start asking which layer should answer the query. Get that right, and nearly everything the custom SQL file used to do has a home that survives the next upgrade.