フォーラム

ホーム » Liferay Portal » English » 3. Development

構造的に表示 平面上に表示 ツリー上に表示
スレッド [ 前へ | 次へ ]
lsli lsli
Override Liferay SQL
2012/03/26 14:21
答え

lsli lsli

ランク: Junior Member

投稿: 57

参加年月日: 2008/11/10

最近の投稿

I've never overridden Liferay's SQL before, so perhaps someone can point me the right direction here.

The Social Activities portlet (that displays the activities of its members) currently displays the activities of all members. However, I only want it to display the activity of members that are NOT administrators - i.e. I don't want to show the activities of an administrator or community administrator. I traced down the SQL code to the social.xml custom SQL code file:

 1<sql id="com.liferay.portlet.social.service.persistence.SocialActivityFinder.findByGroupUsers">
 2        <![CDATA[
 3            SELECT
 4                {SocialActivity.*}
 5            FROM
 6                SocialActivity
 7            INNER JOIN
 8                Users_Groups ON
 9                    (Users_Groups.userId = SocialActivity.userId)
10            WHERE
11                (SocialActivity.mirrorActivityId = 0) AND
12                (Users_Groups.groupId = ?)
13            ORDER BY
14                SocialActivity.createDate DESC
15        ]]>
16    </sql>


The code is called from SocialActivityFindImpl.findByGroupUsers. I think I can easily update the SQL to the following:

 1SELECT SocialActivity.*
 2FROM SocialActivity
 3INNER JOIN Users_Groups
 4ON (Users_Groups.userId = SocialActivity.userId)
 5WHERE (SocialActivity.mirrorActivityId = 0)
 6AND (Users_Groups.groupId              = ?)
 7AND SocialActivity.userId NOT         IN
 8  (SELECT u.userid
 9  FROM User_ u,
10    Users_Roles ur,
11    Role_ r
12  WHERE u.userId = ur.userId
13  AND r.roleId   = ur.roleId
14  AND r.name    IN ('Administrator', 'Community Administrator')
15  )
16ORDER BY SocialActivity.createDate DESC ;


What's the way of updating the SQL code? Is there a way I can just override the definition in the social.xml file for this particular SQL ID key (com.liferay.portlet.social.service.persistence.SocialActivityFinder.findByGroupUsers)? Or do I have to do the full custom extension method, something similar to what's listed here where I have to define a finder, query method, etc.? I ask this because I'm only going to update the custom SQL - I don't have to change the Java code. Just wondering if there's a simple way to do this Thanks!:

http://www.liferay.com/community/wiki/-/wiki/Main/Custom+queries+in+Liferay+5.2
http://kamalkantrajput.blogspot.com/2009/07/how-to-use-custom-sql-in-liferay.html
David H Nebinger
RE: Override Liferay SQL
2012/03/26 16:19
答え

David H Nebinger

ランク: Liferay Legend

投稿: 6276

参加年月日: 2006/09/01

最近の投稿

You should not do what you are planning. You have no idea where all of the points are in the code base that may be leveraging this query, and should not tamper with the code at all.

Instead you should create your own hook to the JSP that invokes the call and have it call a method in your own class. It's fine if that method uses it's own SQL to return the same stuff, but that way your change is isolated from the core and will be easy to include in future upgrades.
lsli lsli
RE: Override Liferay SQL
2012/03/26 17:31
答え

lsli lsli

ランク: Junior Member

投稿: 57

参加年月日: 2008/11/10

最近の投稿

Thanks for the info, David!

I understand your advice. However, the social activities portlet is actually a Liferay plugin - the social networking portlet. Typically, aren't hooks done on the "core" Liferay portlets? For example, when I go to the Liferay IDE and try to create a hook to override the view_members_activity.jspf JSP fragment, this file is not listed. Thus, is this something I cannot do via a hook? Would I have to modify the jspf file directly for the social network portlet and then create extensions for my own custom query and new method for retrieving the results of the custom query?
David H Nebinger
RE: Override Liferay SQL
2012/03/26 17:40
答え

David H Nebinger

ランク: Liferay Legend

投稿: 6276

参加年月日: 2006/09/01

最近の投稿

In that case, I'd probably use the social networking portlet as a baseline and extend it in your own custom portlet.

In general I don't think it's necessarily a good idea to modify Liferay stuff directly, even though these are separate portlets. It's possible that they might get updated, too, and you still have the same upgrade issues as modifying the core directly.

Granted that will come across as a 'purist' position, but having been behind the 8 ball on many a Liferay upgrade process, experience says not to mess with their stuff directly.
lsli lsli
RE: Override Liferay SQL
2012/04/03 12:14
答え

lsli lsli

ランク: Junior Member

投稿: 57

参加年月日: 2008/11/10

最近の投稿

Hi David (or anyone else who can help me),

I've finally found the time to do this and I've decided that I just want to override Liferay's SQL (using the SQL above). I understand Dave's warnings above, but since my custom SQL change is quite simple and it's one of the few ext customizations I will make, I believe I can handle this when Liferay is updated. Also, I did a quick search and it looks like that SQL is only used by the member activities portlet. FYI, I currently use Liferay 6.0.6.

I tried updating the SQL (with some other code) to make this change, but I am unfortunately unable to get this to work. I would appreciate any help!

Here's what I've done so far. Again, I use Liferay 6.0.6 and Liferay IDE 1.2.3. I'm putting the code in the ext-plugin. I'll readily admit I'm not totally sure of what I'm doing. I tried looking up references on the web for what I'm doing but I couldn't find an exact match to my issue so I cobbled up suggestions from related issues. But I think I'm quite out of my league here and definitely would appreciate any help!

docroot/WEB-INF/ext-impl/src/portal-ext.properties:

1custom.sql.configs=sql/custom-sql.xml


docroot/WEB-INF/sql/custom-sql.xml:

 1<?xml version="1.0" encoding="UTF-8"?>
 2<custom-sql>
 3    <sql
 4        id="com.mycompany.portlet.social.service.persistence.SocialActivityFinderImpl.findByOrganizationUsers">
 5    <![CDATA[
 6        SELECT SocialActivity.*
 7        FROM SocialActivity
 8        INNER JOIN Users_Groups
 9        ON (Users_Groups.userId = SocialActivity.userId)
10        WHERE (SocialActivity.mirrorActivityId = 0)
11        AND (Users_Groups.groupId              = ?)
12        AND SocialActivity.userId NOT IN
13          (SELECT u.userid
14            FROM User_ u,
15                 Users_Roles ur,
16                 Role_ r
17             WHERE u.userId = ur.userId
18            AND r.roleId   = ur.roleId
19            AND r.name    IN ('Administrator')
20          )
21        ORDER BY SocialActivity.createDate DESC ;
22    ]]></sql>
23</custom-sql>


docroot/WEB-INF/ext-impl/src/ext-spring.xml:

1<bean
2    id="com.liferay.portlet.social.service.persistence.SocialActivityFinder"
3    class=" com.mycompany.portlet.social.service.persistence.SocialActivityFinderImpl"
4    parent="basePersistence" />


docroot/WEB-INF/ext-service/src/com/mycompany/portlet/social/service/persistence/SocialActivityFinder.java:

 1package com.mycompany.portlet.social.service.persistence;
 2
 3import java.util.List;
 4
 5import com.liferay.portal.kernel.exception.SystemException;
 6import com.liferay.portlet.social.model.SocialActivity;
 7
 8public interface SocialActivityFinder extends
 9        com.liferay.portlet.social.service.persistence.SocialActivityFinder {
10    List<SocialActivity> findByOrganizationUsers(long organizationId,
11            int start, int end) throws SystemException;
12}


docroot/WEB-INF/ext-service/src/com/mycompany/portlet/social/service/persistence/SocialActivityFinderImpl.java:

 1package com.mycompany.portlet.social.service.persistence;
 2
 3import java.util.List;
 4
 5import com.liferay.portal.kernel.dao.orm.QueryPos;
 6import com.liferay.portal.kernel.dao.orm.QueryUtil;
 7import com.liferay.portal.kernel.dao.orm.SQLQuery;
 8import com.liferay.portal.kernel.dao.orm.Session;
 9import com.liferay.portal.kernel.exception.SystemException;
10import com.liferay.portlet.social.model.SocialActivity;
11import com.liferay.portlet.social.model.impl.SocialActivityImpl;
12import com.liferay.util.dao.orm.CustomSQLUtil;
13
14public class SocialActivityFinderImpl extends
15        com.liferay.portlet.social.service.persistence.SocialActivityFinderImpl
16        implements SocialActivityFinder {
17    public static String FIND_BY_ORGANIZATION_USERS = com.mycompany.portlet.social.service.persistence.SocialActivityFinderImpl.class
18            .getName() + ".findByOrganizationUsers";
19
20    @Override
21    public List<SocialActivity> findByOrganizationUsers(
22            long organizationId, int start, int end) throws SystemException {
23
24        Session session = null;
25
26        try {
27            session = openSession();
28
29            String sql = CustomSQLUtil.get(FIND_BY_ORGANIZATION_USERS);
30
31            SQLQuery q = session.createSQLQuery(sql);
32
33            q.addEntity("SocialActivity", SocialActivityImpl.class);
34
35            QueryPos qPos = QueryPos.getInstance(q);
36
37            qPos.add(organizationId);
38
39            return (List<SocialActivity>) QueryUtil.list(q, getDialect(),
40                    start, end);
41        } catch (Exception e) {
42            throw new SystemException(e);
43        } finally {
44            closeSession(session);
45        }
46    }
47}
lsli lsli
RE: Override Liferay SQL
2012/04/07 10:06
答え

lsli lsli

ランク: Junior Member

投稿: 57

参加年月日: 2008/11/10

最近の投稿

Hi everyone,

I'm still running into this issue so I'm hoping someone can help! Unfortunately, it looks like there are some issues with Liferay not handling custom SQL queries on JBoss correctly:

http://issues.liferay.com/browse/LPS-13877

So this could be why my custom SQL file is not being read. But in my latest attempt, I just tried to override the SQL like this in my Liferay IDE install:

docroot/WEB-INF/ext-impl/src/custom-sql/default.xml

1<?xml version="1.0"?>
2
3<custom-sql>
4    <sql file="custom-sql/custom-sql.xml" />
5</custom-sql>


docroot/WEB-INF/ext-impl/src/custom-sql/custom-sql.sql:

 1<?xml version="1.0" encoding="UTF-8"?>
 2<custom-sql>
 3    <sql
 4        id="com.mycompany.portlet.social.service.persistence.SocialActivityFinderImpl.findByGroupUsers">
 5    <![CDATA[
 6        SELECT SocialActivity.*
 7        FROM SocialActivity
 8        INNER JOIN Users_Groups
 9        ON (Users_Groups.userId = SocialActivity.userId)
10        WHERE (SocialActivity.mirrorActivityId = 0)
11        AND (Users_Groups.groupId              = ?)
12        AND SocialActivity.userId NOT IN
13          (SELECT u.userid
14            FROM User_ u,
15                 Users_Roles ur,
16                 Role_ r
17             WHERE u.userId = ur.userId
18            AND r.roleId   = ur.roleId
19            AND r.name    IN ('Administrator')
20          )
21        ORDER BY SocialActivity.createDate DESC ;
22    ]]></sql>
23</custom-sql>
24
25
26docroot/WEB-I


Unfortunately, when I debug through the Liferay code, it looks like my custom SQL files are not even being read in because of the above classloader issues (as described in the Liferay bug link above).

One last alternative is just editing the Liferay source code directly. That's certainly something I do not want to do, but I may have to do it in order to get my custom SQL to work.