Liferay's Dynamic Query Magic

The common approach to fetch data filtered by criterias is to add finder definitions. When finder possibilities are not fit your requirements you can write custom finder that will use custom s(d)ql query. This seems ok, but writing queries as bindable strings is weak when you what to add or remove sugnificant number of criterias depending e.g. on user input. This dives you into concatenate strings or write placeholders for different query parts. Finally you will find yourself on inventing your own query builder tool or find that liferay developers alredy did this before;) Liferays query builder tool may seem incomplete after the first look but it contains full set of features to query data under the hood.
Every Service Builder's *LocalServiceUtil has number of dynamicQuery methods that allows you to pass your own query construction and expect result. To define what you what to get you should use setProjection method with model class passed:
DynamicQuery query = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader())
try {
    List<User> users = UserLocalServiceUtil.dynamicQuery(query);
} catch (Exception e) {
you can add some conditions to filter records
query.add("userName", "%John%"));
query.add(RestrictionsFactoryUtil.eq("userId", userId));
query.add("name", String[] {"alpha", "beta" }));
If you want receive e.g. only ids:
the way to operate on results will be a bit changed, you will receive list of objects that you can try to cast to something else
try {
    List<Object> ids = UserLocalServiceUtil.dynamicQuery(query);
    for (Object obj: ids) {
        long id = Long.parseLong(obj.toString());
} catch (Exception e) {
you can set multiple projections to fetch a number of fields and e.g. buid hashset or you gether agregate values:
to group by field use:
dynamic query can be used as parameter for eq or in restriction criterions
the problem complained by many developers is table join unavailability but you can use subqueries with aliases instead

DynamicQuery childQuery = DynamicQueryFactoryUtil.forClass(DLFileEntry.class, "childQuery", PortalClassLoaderUtil.getClassLoader())
DynamicQuery parentQuery = DynamicQueryFactoryUtil.forClass(DLFileVersion.class, "parentQuery", PortalClassLoaderUtil.getClassLoader())
childQuery and parentQuery are named only as examples, better widely used practice is to shorten table names to few letters, e.g. UserPermission tablee is better to be aliased as up and so on