<img alt="Liferay" height="36" src="http://cdn.www.liferay.com/osb-community-theme/images/custom/heading.png" />

Hi,
I am trying to retrieve data from two tables (Table1 and Table2) using Join. I created a service buider defining the two entities. Now based on the query (join) some columns of Table1 and some columns of Table 2 should be retrieved. How can it be done using service builder in Liferay? Can somebody please explain the steps.

RE: Join Two tables in Liferay using custom query

November 9, 2011 6:02 AM

Answer

Raja Nagendra Kumar

Rank: Expert

Posts: 484

Join Date: March 1, 2006

See this..

http://www.liferay.com/community/forums/-/message_boards/message/1061266

Once you define a entity, you can use the query as if it is fired on table and its colums.. however physically the data may come from two tables..due the schema relationships.

Regards,
Raja Nagendra Kumar,
C.T.O
www.tejasoft.com

Mark as an Answer

RE: Join Two tables in Liferay using custom query

November 9, 2011 9:37 PM

Answer

Rhina Karr

Rank: Junior Member

Posts: 74

Join Date: November 1, 2010

Thanks for your answer emoticon

. I tried to use custom query. Below is the steps I followed:
1. Created a service builder defining the two entities.
2. Created a Finder class and wrote custom sql query:

1
2SQLQuery q = session.createSQLQuery("Select Table1.name, Table1.Id, Table2.status from Table1, Table2 where Table1.Id = Table2.Id");
3List resultList = QueryUtil.list(q, getDialect,(), begin, end);

In the code above, the resultList returns a list of Object. But how can I get the data from the objects? Those objects are not instance of model beans ot the two entities (Table1ModelImpl and Table2ModelImpl).\

Thanks

Mark as an Answer

RE: Join Two tables in Liferay using custom query

January 9, 2012 3:52 PM

Answer

Ian Harrigan

Rank: New Member

Posts: 17

Join Date: November 22, 2011

Hello everyone,

Has anyone got any further information on this? I too am just getting back a list of Objects rather than the service model. In my finder i have:

1results = (List<AuditItem>) QueryUtil.list(q, getDialect(), begin, end);

This seems to work in the sense that the size of the results list matches the record count in the database, however, in view.jsp im trying something like:

1List<AuditItem> results= AuditItemLocalServiceUtil.findAuditItems(0, 100);
2System.out.println("record count = " + results.size());
3System.out.println("Action ID = " + results.get(0).getActionID());

I get the following exception:

1ava.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to urn.inps.vpp.data.model.AuditItem

Im not using the ext environment (but im fairly sure thats not the issue). Has anyone got this to work properly? It seems to be making the query correctly, but how can i get the actual objects out of the query?

Thanks in advance,
Ian

Mark as an Answer

RE: Join Two tables in Liferay using custom query

April 17, 2012 6:11 AM

Answer

Jan Geißler

Rank: Expert

Posts: 250

Join Date: July 5, 2011

I know its kind of an old thread, but I faced the same problem as Ian Harrigan.
Here is the Solution for my Reference and everybody who might face the same problem:

1public List<ProjectDayUserOrganization> findByCompanyId_GroupId_UserId_OrganizationId_Released(long companyId, long groupId, long userId,
2          long organizationId, long[] statusses, int begin, int end) throws SystemException {
3       // / This stuff is basic set up
4       Session session = null;
5       try {
6          session = openSession();
7          // Here ends the basic set up;
8          String sql = CustomSQLUtil.get("de.osc.projectadministration.service.persistence.ProjectDayUserOrganizationFinderImpl.findByCompanyId_GroupId_OrganizationId_Stausses_Released");
9          // Now that we have built the query, we can do all the usual stuff.
10          SQLQuery q = session.createSQLQuery(sql);
11          q.addEntity("ProjectDayUserOrganisation", ProjectDayUserOrganizationImpl.class);
12          QueryPos qPos = QueryPos.getInstance(q);
13          qPos.add(companyId);
14          qPos.add(groupId);
15          qPos.add(organizationId);
16          qPos.add(StringUtil.merge(statusses));
17          List<ProjectDayUserOrganization> projectDayUOrganizations = (List<ProjectDayUserOrganization>) QueryUtil.list(q, getDialect(), begin, end);
18          return projectDayUOrganizations;
19       } catch (Exception e) {
20          throw new SystemException(e);
21       } finally {
22          // must have this to close the hibernate session..
23          // if you fail to do this.. you will have a lot of open sessions…
24          closeSession(session);
25       }
26 }

This is the important part:

q.addEntity("ProjectDayUserOrganisation", ProjectDayUserOrganizationImpl.class);

So long
Jan

Mark as an Answer

RE: Join Two tables in Liferay using custom query

May 21, 2013 1:20 AM

Answer

Vijayakumar G

Rank: New Member

Posts: 2

Join Date: May 10, 2013

Hello

it only returns the List of objects,you need to manually assign this to bean object or you can pass the list directly to JSP.

For more details please refer

http://www.liferaysavvy.com/2013/02/getting-data-from-multiple-tables-in.html

-Vj

Rhina Karr:

Thanks for your answer emoticon

. I tried to use custom query. Below is the steps I followed:
1. Created a service builder defining the two entities.
2. Created a Finder class and wrote custom sql query:

1
2SQLQuery q = session.createSQLQuery("Select Table1.name, Table1.Id, Table2.status from Table1, Table2 where Table1.Id = Table2.Id");
3List resultList = QueryUtil.list(q, getDialect,(), begin, end);

In the code above, the resultList returns a list of Object. But how can I get the data from the objects? Those objects are not instance of model beans ot the two entities (Table1ModelImpl and Table2ModelImpl).\

Thanks