Fórum

RE: Join Two tables in Liferay using custom query

Rhina Karr, modificado 12 Anos atrás.

Join Two tables in Liferay using custom query

Junior Member Postagens: 86 Data de Entrada: 02/11/10 Postagens Recentes
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.
thumbnail
Raja Nagendra Kumar, modificado 12 Anos atrás.

RE: Join Two tables in Liferay using custom query

Expert Postagens: 484 Data de Entrada: 02/03/06 Postagens Recentes
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
Rhina Karr, modificado 12 Anos atrás.

RE: Join Two tables in Liferay using custom query

Junior Member Postagens: 86 Data de Entrada: 02/11/10 Postagens Recentes
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:

SQLQuery q = session.createSQLQuery("Select Table1.name, Table1.Id, Table2.status from Table1, Table2 where Table1.Id = Table2.Id");
List 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, modificado 12 Anos atrás.

RE: Join Two tables in Liferay using custom query

New Member Postagens: 19 Data de Entrada: 22/11/11 Postagens Recentes
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:

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


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:

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


I get the following exception:

ava.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
thumbnail
Jan Geißler, modificado 11 Anos atrás.

RE: Join Two tables in Liferay using custom query

Liferay Master Postagens: 735 Data de Entrada: 05/07/11 Postagens Recentes
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:

public List<projectdayuserorganization> findByCompanyId_GroupId_UserId_OrganizationId_Released(long companyId, long groupId, long userId,
			long organizationId, long[] statusses, int begin, int end) throws SystemException {
		// / This stuff is basic set up
		Session session = null;
		try {
			session = openSession();
			// Here ends the basic set up;
			String sql = CustomSQLUtil.get("de.osc.projectadministration.service.persistence.ProjectDayUserOrganizationFinderImpl.findByCompanyId_GroupId_OrganizationId_Stausses_Released");
			// Now that we have built the query, we can do all the usual stuff.
			SQLQuery q = session.createSQLQuery(sql);
			q.addEntity("ProjectDayUserOrganisation", ProjectDayUserOrganizationImpl.class);
			QueryPos qPos = QueryPos.getInstance(q);
			qPos.add(companyId);
			qPos.add(groupId);
			qPos.add(organizationId);
			qPos.add(StringUtil.merge(statusses));			
			List<projectdayuserorganization> projectDayUOrganizations = (List<projectdayuserorganization>) QueryUtil.list(q, getDialect(), begin, end);
			return projectDayUOrganizations;
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			// must have this to close the hibernate session..
			// if you fail to do this.. you will have a lot of open sessions…
			closeSession(session);
		}
	}
</projectdayuserorganization></projectdayuserorganization></projectdayuserorganization>

This is the important part:

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

So long
Jan
thumbnail
Vijayakumar G, modificado 10 Anos atrás.

RE: Join Two tables in Liferay using custom query

New Member Postagens: 2 Data de Entrada: 11/05/13 Postagens Recentes
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:

SQLQuery q = session.createSQLQuery("Select Table1.name, Table1.Id, Table2.status from Table1, Table2 where Table1.Id = Table2.Id");
List 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