Dynamic Query with Joins with liferay tables

[Vishal Panchal, modificado hace 11 años.]

Hi Experts,

We need to create a dynamic query using join on multiple liferay tables
which should return the users based upon the following conditions:

1. Users should belong to a given group and
2. Users should not belong to Administrator role and
3. Users should not belong to a given custom role (Regular role in our
case its name is Custom-Role).

We had created a sql for it and it is working fine if we run it in query
console but we need to create a dynamic query to fetch the results from
liferay. Below is the sql query which we created:

Select a.userId, a.screenName
from user_ a
join users_groups b on (b.userId=a.userId)
join group_ c on (c.groupId=b.groupId and c.name='SiteName')
join users_roles d on (d.userId=a.userId)
join role_ e on (e.roleId=d.roleId and e.name <> 'Custom-Role' and
e.name <> 'Administrator')

Any kind of help is really appreciated.

Thanks,
Vishal

[Joaquin Cabal, modificado hace 11 años.]

Hi Vishal,
I think you should use "Custom SQL" instead of "Dynamic Query", for doing a query with more than one liferay table.

Here you have something to do it.

http://www.liferay.com/community/wiki/-/wiki/Main/Custom+queries+in+Liferay

Regards

[Vishal Panchal, modificado hace 11 años.]

Hi Joaquin,

Thanks for your reply.

I want to join the liferay tables in HOOK and I don't have an Idea that how can I make custom query in Liferay-Hook.

Thanks,
Vishal

[Joaquin Cabal, modificado hace 11 años.]

y r welcome