Forums

Home » Liferay Portal » English » 3. Development

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Tim Cardwell
Custom Query using built in Liferay tables (No entities)
February 19, 2013 3:27 PM
Answer

Tim Cardwell

Rank: Junior Member

Posts: 30

Join Date: July 19, 2012

Recent Posts

My service.xml:
 1
 2<?xml version="1.0" encoding="UTF-8"?>
 3<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.1.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_1_0.dtd">
 4<service-builder package-path="com.cardwell.test">
 5    <author>tcardw01</author>
 6    <namespace>getfolders</namespace>
 7
 8    <entity name="GetFolders" local-service="true" remote-service="true"/>
 9</service-builder>


After running build-service in Ant, only 3 packages are created for me
  • com.cardwell.test.service.base
  • com.cardwell.test.service.http
  • com.cardwell.test.service.impl


I follow the directions of other custom query posts, and add the package com.cardwell.test.service.persistence, and then add the following class to the package:
 1
 2package com.cardwell.test.service.persistence;
 3
 4import java.util.List;
 5
 6import com.liferay.portal.kernel.dao.orm.SQLQuery;
 7import com.liferay.portal.kernel.dao.orm.Session;
 8import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
 9import com.liferay.portlet.documentlibrary.model.DLFolder;
10import com.liferay.util.dao.orm.CustomSQLUtil;
11
12public class DLFolderCustomFinderImpl extends BasePersistenceImpl{
13    public List<DLFolder> getAllFolders()
14    {
15        List<DLFolder> allFolders = null;
16        Session session;
17        String sqlId = "DLFolderFindAll";
18        session = openSession();
19        String sql = CustomSQLUtil.get(sqlId);
20        SQLQuery query = session.createSQLQuery(sql);
21        allFolders = (List<DLFolder>)query.list();
22       
23        return allFolders;
24    }
25}


And finally my custom sql looks like:
 1
 2<?xml version="1.0" encoding="UTF-8"?>
 3
 4<custom-sql>
 5    <sql id="DLFolderFindAll">
 6        <![CDATA[
 7            SELECT DISTINCT {dlFolder.*} FROM DLFolder dlFolder
 8            ORDER BY dlFolder.parentFolderId ASC
 9        ]]>
10    </sql>
11</custom-sql>


Before I continue, I know there is a liferay API call that will get me all of the folders on a liferay site, but I will be modifying the SQL once I get everything else working.

I do another Build-Service in Ant. This is where things go wrong. From what I've read, I should be able to access a DLFolderCustomFinderUtil class to run my query, yet it never shows up. Anyone able to tell me what my problem is? Do I need an entity object?

Thanks,
-Tim
David H Nebinger
RE: Custom Query using built in Liferay tables (No entities)
February 19, 2013 6:49 PM
Answer

David H Nebinger

Community Moderator

Rank: Liferay Legend

Posts: 11770

Join Date: September 1, 2006

Recent Posts

Tim Cardwell:
I do another Build-Service in Ant. This is where things go wrong. From what I've read, I should be able to access a DLFolderCustomFinderUtil class to run my query, yet it never shows up. Anyone able to tell me what my problem is? Do I need an entity object?


You cannot manufacture your own 'persistence' class.

You have two options:

1. create a full blown entity so you get all of the generated classes (probably not what you want).

2. Use DynamicQuery.

DQ is actually built to allow you to create dynamic queries against entities, including Liferay entities. The path that you're currently on is just plain wrong. Change over to DQ and you'll have no problems at all.

Basically you shouldn't even think about writing your own SQL queries, especially since a) you're not even supposed to be looking at the Liferay tables and b) the next release could change the underlying table layout thus breaking your SQL.
Tim Cardwell
RE: Custom Query using built in Liferay tables (No entities)
February 21, 2013 9:12 AM
Answer

Tim Cardwell

Rank: Junior Member

Posts: 30

Join Date: July 19, 2012

Recent Posts

What if the query I'm trying to execute is too difficult for a dynamic query?

I initially tried to use dynamic queries but wasn't able to create the full query using the Dynamic Query API. It is basically the same query Liferay executes when calling a DLFolderUtil.filterFindByGroupId(long groupId), except I'm trying to do the same without a group id. (This is not the same as find all folders, because the user permissions are taken into account).

The query I'm trying to execute is:
 1
 2SELECT DISTINCT {dlFolder.*} FROM DLFolder dlFolder   
 3INNER JOIN (
 4    SELECT scope, primKey, roleId, ownerId
 5    FROM ResourcePermission
 6    WHERE (ResourcePermission.companyId = 10154)
 7    AND (ResourcePermission.name = 'com.liferay.portlet.documentlibrary.model.DLFolder')
 8    AND (MOD(ResourcePermission.actionIds, 2) = 1) ) InlineSQLResourcePermission
 9    ON ((InlineSQLResourcePermission.scope = 4
10    AND InlineSQLResourcePermission.primKey = CAST_TEXT(dlFolder.folderId)
11    --AND ((dlFolder.groupId = 0)  -- Take out group ID so we get all folders a user can view
12    AND (InlineSQLResourcePermission.roleId = 10162  -- Depends on the current user
13    OR InlineSQLResourcePermission.roleId = 10164  -- Depends on the current user
14    OR InlineSQLResourcePermission.roleId = 10165  -- Depends on the current user
15    OR (dlFolder.userId = 11126)))))    -- Depends on the current user
16ORDER BY dlFolder.parentFolderId ASC, dlFolder.name ASC


I successfully coded everything into a dynamic query except for the modulus operator... It seems the Dynamic Query API doesn't support it, which is why I was changing to custom queries.

You're right, I don't want to generate a full blown entity, but is there a way around it at this point?

Thanks,
-Tim