掲示板

Override Liferay SQL

12年前 に lsli lsli によって更新されました。

Override Liferay SQL

Junior Member 投稿: 62 参加年月日: 08/11/11 最新の投稿
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:

<sql id="com.liferay.portlet.social.service.persistence.SocialActivityFinder.findByGroupUsers">
		
			SELECT
				{SocialActivity.*}
			FROM
				SocialActivity
			INNER JOIN
				Users_Groups ON
					(Users_Groups.userId = SocialActivity.userId)
			WHERE
				(SocialActivity.mirrorActivityId = 0) AND
				(Users_Groups.groupId = ?)
			ORDER BY
				SocialActivity.createDate DESC
		
	</sql>


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

SELECT SocialActivity.*
FROM SocialActivity
INNER JOIN Users_Groups
ON (Users_Groups.userId = SocialActivity.userId)
WHERE (SocialActivity.mirrorActivityId = 0)
AND (Users_Groups.groupId              = ?)
AND SocialActivity.userId NOT         IN
  (SELECT u.userid
  FROM User_ u,
    Users_Roles ur,
    Role_ r
  WHERE u.userId = ur.userId
  AND r.roleId   = ur.roleId
  AND r.name    IN ('Administrator', 'Community Administrator')
  )
ORDER 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
thumbnail
12年前 に David H Nebinger によって更新されました。

RE: Override Liferay SQL

Liferay Legend 投稿: 14914 参加年月日: 06/09/02 最新の投稿
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.
12年前 に lsli lsli によって更新されました。

RE: Override Liferay SQL

Junior Member 投稿: 62 参加年月日: 08/11/11 最新の投稿
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?
thumbnail
12年前 に David H Nebinger によって更新されました。

RE: Override Liferay SQL

Liferay Legend 投稿: 14914 参加年月日: 06/09/02 最新の投稿
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.
11年前 に lsli lsli によって更新されました。

RE: Override Liferay SQL

Junior Member 投稿: 62 参加年月日: 08/11/11 最新の投稿
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:

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


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

<!--?xml version="1.0" encoding="UTF-8"?-->
<custom-sql>
	<sql id="com.mycompany.portlet.social.service.persistence.SocialActivityFinderImpl.findByOrganizationUsers">
    
        SELECT SocialActivity.*
		FROM SocialActivity
		INNER JOIN Users_Groups
		ON (Users_Groups.userId = SocialActivity.userId)
		WHERE (SocialActivity.mirrorActivityId = 0)
		AND (Users_Groups.groupId              = ?)
		AND SocialActivity.userId NOT IN
		  (SELECT u.userid
		    FROM User_ u,
		    	 Users_Roles ur,
		         Role_ r
		 	WHERE u.userId = ur.userId
		    AND r.roleId   = ur.roleId
		    AND r.name    IN ('Administrator')
		  )
		ORDER BY SocialActivity.createDate DESC ;
    </sql>
</custom-sql>


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

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


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

package com.mycompany.portlet.social.service.persistence;

import java.util.List;

import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.portlet.social.model.SocialActivity;

public interface SocialActivityFinder extends
		com.liferay.portlet.social.service.persistence.SocialActivityFinder {
	List<socialactivity> findByOrganizationUsers(long organizationId,
			int start, int end) throws SystemException;
}</socialactivity>


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

package com.mycompany.portlet.social.service.persistence;

import java.util.List;

import com.liferay.portal.kernel.dao.orm.QueryPos;
import com.liferay.portal.kernel.dao.orm.QueryUtil;
import com.liferay.portal.kernel.dao.orm.SQLQuery;
import com.liferay.portal.kernel.dao.orm.Session;
import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.portlet.social.model.SocialActivity;
import com.liferay.portlet.social.model.impl.SocialActivityImpl;
import com.liferay.util.dao.orm.CustomSQLUtil;

public class SocialActivityFinderImpl extends
		com.liferay.portlet.social.service.persistence.SocialActivityFinderImpl
		implements SocialActivityFinder {
	public static String FIND_BY_ORGANIZATION_USERS = com.mycompany.portlet.social.service.persistence.SocialActivityFinderImpl.class
			.getName() + ".findByOrganizationUsers";

	@Override
	public List<socialactivity> findByOrganizationUsers(
			long organizationId, int start, int end) throws SystemException {

		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(FIND_BY_ORGANIZATION_USERS);

			SQLQuery q = session.createSQLQuery(sql);

			q.addEntity("SocialActivity", SocialActivityImpl.class);

			QueryPos qPos = QueryPos.getInstance(q);

			qPos.add(organizationId);

			return (List<socialactivity>) QueryUtil.list(q, getDialect(),
					start, end);
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}
}</socialactivity></socialactivity>
11年前 に lsli lsli によって更新されました。

RE: Override Liferay SQL

Junior Member 投稿: 62 参加年月日: 08/11/11 最新の投稿
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

<!--?xml version="1.0"?-->

<custom-sql>
	<sql file="custom-sql/custom-sql.xml" />
</custom-sql>


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

<!--?xml version="1.0" encoding="UTF-8"?-->
<custom-sql>
    <sql id="com.mycompany.portlet.social.service.persistence.SocialActivityFinderImpl.findByGroupUsers">
    
        SELECT SocialActivity.*
        FROM SocialActivity
        INNER JOIN Users_Groups
        ON (Users_Groups.userId = SocialActivity.userId)
        WHERE (SocialActivity.mirrorActivityId = 0)
        AND (Users_Groups.groupId              = ?)
        AND SocialActivity.userId NOT IN
          (SELECT u.userid
            FROM User_ u,
                 Users_Roles ur,
                 Role_ r
             WHERE u.userId = ur.userId
            AND r.roleId   = ur.roleId
            AND r.name    IN ('Administrator')
          )
        ORDER BY SocialActivity.createDate DESC ;
    </sql>
</custom-sql>


docroot/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.