Forums

Home » Liferay Portal » English » 3. Development

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Deepjyoti Nath
Join Two tables in Liferay using custom query
November 9, 2011 5:53 AM
Answer

Deepjyoti Nath

Rank: Junior Member

Posts: 81

Join Date: November 1, 2010

Recent Posts

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.
Raja Nagendra Kumar
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

Recent Posts

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
Deepjyoti Nath
RE: Join Two tables in Liferay using custom query
November 9, 2011 9:37 PM
Answer

Deepjyoti Nath

Rank: Junior Member

Posts: 81

Join Date: November 1, 2010

Recent Posts

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
Ian Harrigan
RE: Join Two tables in Liferay using custom query
January 9, 2012 3:52 PM
Answer

Ian Harrigan

Rank: New Member

Posts: 19

Join Date: November 22, 2011

Recent Posts

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
Jan Geißler
RE: Join Two tables in Liferay using custom query
April 17, 2012 6:11 AM
Answer

Jan Geißler

Community Moderator

Rank: Liferay Master

Posts: 735

Join Date: July 5, 2011

Recent Posts

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
Vijayakumar G
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

Recent Posts

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