Join 2 tables using a DynamicQuery {Solved}

[14年前 に Stef Heyenrath によって更新されました。]

Hi all, I've searched the forums but I did not find an easy understandable example how to do a join from 2 tables using the DynamicQuery.

See this post for some details:

The sql query I want to execute is :

SELECT lvl.* FROM level lvl
join levelvalue val on lvl.levelValueId = val.levelValueId
order by val.value;

Where the level table has the following columns:
- levelId
- title
- levelValueId


and the levelvalue table has the following columns:
- levelValueId
- value

How to do this ?

[14年前 に Gnaniyar Zubair によって更新されました。]

I think you can use custom query for join two tables instead of dynamic query

Here is the Wiki article for this:

http://www.liferay.com/web/guest/community/wiki/-/wiki/Main/How+to+create+a+custom+query+in+ext+for+Liferay+models

HTH

- Gnaniyar Zubair

[14年前 に Stef Heyenrath によって更新されました。]

I believe this can only be used in an EXT environment and this wiki is also based on an older 4.3 version.

BTW : I tried the following code, but this does not work:

final DynamicQuery queryLevelValues = DynamicQueryFactoryUtil.forClass(LevelValue.class, "levelvalue");
queryLevelValues.setProjection(ProjectionFactoryUtil.property("levelValueId")).add(
    PropertyFactoryUtil.forName("levelvalue.levelValueId").eqProperty(
		"competencelevel.levelValueId")).addOrder(OrderFactoryUtil.desc("levelvalue.value"));

final DynamicQuery query = DynamicQueryFactoryUtil.forClass(CompetenceLevel.class, "competencelevel");
query.add(PropertyFactoryUtil.forName("competencelevel.competenceId").eq(competenceId));
query.add(PropertyFactoryUtil.forName("competencelevel.childId").eq(Long.valueOf(0)));
query.add(PropertyFactoryUtil.forName("competencelevel.levelValueId").in(queryLevelValues));

List res = dynamicQuery(query);

[14年前 に Stef Heyenrath によって更新されました。]

I solved my problem by building a custom SQL finder.

I did the following steps:

1. Create a new finder called CompetenceLevelFinderImpl in the <namespace>/generated/service/persistence directory.
2. Let this class extend BasePersistence
3. Now do a 'build-service' on the project.
4. The ServiceBuilder autogenerates the following two extra files : CompetenceLevelFinder.java and CompetenceLevelFinderUtil.java
5. Now open the CompetenceLevelFinderImpl.java file and let this class extend the CompetenceLevelPersistenceImpl class and implement CompetenceLevelFinder. (Assumed that the CompetenceLevel entity is defined in the service.xml and that the needed classes are also autogenerated by ServiceBuilder.)
6. Now add the needed functionality to the CompetenceLevelFinderImpl class and do a build-service.
7. You can now use the added functionality.


For completeness see attached source file.

[14年前 に Juan Fernández によって更新されました。]

More related info here

Regards
Juan Fernández

[13年前 に Shagul Khajamohideen によって更新されました。]

Nice article Juan. Looks like the above link is broken. The one below works.

http://www.liferay.com/community/wiki/-/wiki/Main/Service+Builder+Finders