3

I have 3 tables user_, userTracker, userTrackerPath

user_ has userId as Pk. which is Fk in userTracker userTracker has UserTrackerId as pk which is Fk in userTrackerPath and userTrackerPath has userTrackerPathId as Pk.

user_ tables has fields firstName, LastName, loginIp, lastLoginIp userTracker has fields remoteAddr, remoteHost userTrackerPath has fields path_, pathDate

All these are the fileds that i want.

I have written an sql query and it runs successfully for me, but i want the result using Dynamic query .

Here is my sql query.

select concat(U.firstName," ",U.lastName) as     FullName,U.loginIp,U.lastLoginIp,UT.remoteAddr,substring(UT.modifiedDate,1,10) as Date,UTP.path_ from demo.User_ U, demo.UserTracker UT, demo.UserTrackerPath UTP where ((U.userId=UT.userId) and (UT.userTrackerId=UTP.userTrackerId));

I wrote dynamic query with projections i am confused how will i be joining them.

//Dynamic Query For User Class

        DynamicQuery dynamicQuery_user = DynamicQueryFactoryUtil.forClass(User.class,PortalClassLoaderUtil.getClassLoader())
                .setProjection(ProjectionFactoryUtil.property("userId"))
                .setProjection(ProjectionFactoryUtil.property("firstName"))
                .setProjection(ProjectionFactoryUtil.property("lastName"))
                .setProjection(ProjectionFactoryUtil.property("loginIp"))
                .setProjection(ProjectionFactoryUtil.property("lastLoginIp"));

        //Dynamic Query For User and UserTracker Class

        DynamicQuery dynamicQuery_userTracker  = DynamicQueryFactoryUtil.forClass(UserTracker.class,PortalClassLoaderUtil.getClassLoader())
                .setProjection(ProjectionFactoryUtil.property("modifiedDate"))
                .setProjection(ProjectionFactoryUtil.property("remoteAddr"));


        //Dynamic Query for UserTracker and UserTrackerPath

        DynamicQuery dynamicQuery_userTrackerPath  = DynamicQueryFactoryUtil.forClass(UserTrackerPath.class,PortalClassLoaderUtil.getClassLoader())
                .setProjection(ProjectionFactoryUtil.property("path_"))
                .setProjection(ProjectionFactoryUtil.property("pathDate"));

Also i tried..

        dynamicQuery_userTracker.add(PropertyFactoryUtil.forName("userId").in(dynamicQuery_user));

        dynamicQuery_userTrackerPath.add(PropertyFactoryUtil.forName("userTrackerId").in(dynamicQuery_userTracker));

I knows my method is incorrect. Any Views or suggestions.

Thanks.

Jay Trivedi
  • 464
  • 4
  • 15
  • I am also waiting for the answer for this ..i tried with stored procedure . http://stackoverflow.com/questions/14608667/unable-to-get-multiple-table-entities-through-stored-procedure-using-hibernate – Suresh Atta Feb 12 '13 at 07:43
  • Hey You must go through.http://www.liferay.com/community/forums/-/message_boards/message/6729269?_19_redirect=http%3A%2F%2Fwww.liferay.com%2Fhome%3Fp_p_id%3D3%26p_p_lifecycle%3D0%26p_p_state%3Dmaximized%26p_p_mode%3Dview%26_3_keywords%3Ddynamic%2Bquery%2B%26_3_struts_action%3D%252Fsearch%252Fsearch Hope it helps, I am trying to get on exact solution to my problem using this concept. – Jay Trivedi Feb 12 '13 at 08:15
  • Have you run the code you posted? – Miguel Pereira Mar 18 '14 at 15:02

1 Answers1

1

Jay I think you cannot do joins with the DynamicQuery API. You can do subqueries with the in and notin methods.

Miguel Pereira
  • 1,781
  • 16
  • 14