Fóruns

Início » Liferay Portal » English » 2. Using Liferay » General

Visualização combinada Visão plana Exibição em árvore
Tópicos [ Anterior | Próximo ]
toggle
Jay Trivedi
Getting Records from 3 tables using Dynamic Query
12 de Fevereiro de 2013 00:58
Resposta

Jay Trivedi

Ranking: Regular Member

Mensagens: 106

Data de entrada: 23 de Novembro de 2012

Mensagens recentes

I have 3 tables user_, userTracker, userTrackerPath

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

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

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.

1
2select 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.

 1
 2//Dynamic Query For User Class
 3
 4        DynamicQuery dynamicQuery_user = DynamicQueryFactoryUtil.forClass(User.class,PortalClassLoaderUtil.getClassLoader())
 5                .setProjection(ProjectionFactoryUtil.property("userId"))
 6                .setProjection(ProjectionFactoryUtil.property("firstName"))
 7                .setProjection(ProjectionFactoryUtil.property("lastName"))
 8                .setProjection(ProjectionFactoryUtil.property("loginIp"))
 9                .setProjection(ProjectionFactoryUtil.property("lastLoginIp"));
10
11        //Dynamic Query For User and UserTracker Class
12
13        DynamicQuery dynamicQuery_userTracker  = DynamicQueryFactoryUtil.forClass(UserTracker.class,PortalClassLoaderUtil.getClassLoader())
14                .setProjection(ProjectionFactoryUtil.property("modifiedDate"))
15                .setProjection(ProjectionFactoryUtil.property("remoteAddr"));
16
17
18        //Dynamic Query for UserTracker and UserTrackerPath
19
20        DynamicQuery dynamicQuery_userTrackerPath  = DynamicQueryFactoryUtil.forClass(UserTrackerPath.class,PortalClassLoaderUtil.getClassLoader())
21                .setProjection(ProjectionFactoryUtil.property("path_"))
22                .setProjection(ProjectionFactoryUtil.property("pathDate"));


but i am confused how will i be getting all this fields into single list.

I was successfully able to get User_ records by using

 1
 2 DynamicQuery q = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader())
 3                 .add(
 4                         PropertyFactoryUtil.forName("userId")
 5                         .in(
 6                                 DynamicQueryFactoryUtil.forClass(UserTracker.class, PortalClassLoaderUtil.getClassLoader())
 7                                 .setProjection(ProjectionFactoryUtil.property("userId"))
 8                                 .add(
 9                                         PropertyFactoryUtil.forName("userTrackerId").in
10                                         (
11                                                 DynamicQueryFactoryUtil.forClass(UserTrackerPath.class, PortalClassLoaderUtil.getClassLoader())
12                                                 .setProjection(ProjectionFactoryUtil.property("userTrackerId"))
13                                        )
14                                     )
15                            )
16                     );


but instead i need records from all the 3 tables...
Any Help ?? emoticon
Juhi Kumari
RE: Getting Records from 3 tables using Dynamic Query
12 de Fevereiro de 2013 01:51
Resposta

Juhi Kumari

Ranking: Expert

Mensagens: 347

Data de entrada: 12 de Dezembro de 2011

Mensagens recentes

Hi,

For this requirement you can go for Custom query. I think using Dynamic Query we can use only one table.

Regards
Juhi
David H Nebinger
RE: Getting Records from 3 tables using Dynamic Query
12 de Fevereiro de 2013 05:33
Resposta

David H Nebinger

Ranking: Liferay Legend

Mensagens: 7917

Data de entrada: 1 de Setembro de 2006

Mensagens recentes

Untrue. DQ can do joins with other tables.
Kowbathullah Gnaniyar
RE: Getting Records from 3 tables using Dynamic Query
12 de Fevereiro de 2013 06:04
Resposta

Kowbathullah Gnaniyar

Ranking: Liferay Master

Mensagens: 603

Data de entrada: 19 de Dezembro de 2007

Mensagens recentes

Somewhere I have seen this code given by Jonas : But I didn't check. Please try this :

/* DynamicQuery dq0 = DynamicQueryFactoryUtil.forClass(JournalArticle.class, "journalarticle")
.setProjection(ProjectionFactoryUtil.property("resourcePrimKey"))
.add(PropertyFactoryUtil.forName("journalarticle.companyId").eqProperty("tagsasset.companyId"))
.add(PropertyFactoryUtil.forName("journalarticle.groupId").eqProperty("tagsasset.groupId"))
.add(PropertyFactoryUtil.forName("journalarticle.type").eq("article-content"));
DynamicQuery query = DynamicQueryFactoryUtil.forClass(TagsAsset.class, "tagsasset")
.add(PropertyFactoryUtil.forName("tagsasset.classPK").in(dq0))
.addOrder(OrderFactoryUtil.desc("tagsasset.viewCount"));*/
Jay Trivedi
RE: Getting Records from 3 tables using Dynamic Query
12 de Fevereiro de 2013 06:32
Resposta

Jay Trivedi

Ranking: Regular Member

Mensagens: 106

Data de entrada: 23 de Novembro de 2012

Mensagens recentes

Thanks a Lot , I will update success trails.
emoticon
Jay Trivedi
RE: Getting Records from 3 tables using Dynamic Query
12 de Fevereiro de 2013 08:03
Resposta

Jay Trivedi

Ranking: Regular Member

Mensagens: 106

Data de entrada: 23 de Novembro de 2012

Mensagens recentes

I tired Out an example to join 2 tables User_ and UserTracker as userId is common between them.

 1
 2DynamicQuery dq0 = DynamicQueryFactoryUtil.forClass(User.class, "user",PortalClassLoaderUtil.getClassLoader())
 3            .setProjection(ProjectionFactoryUtil.property("user.userId"))
 4            .add(PropertyFactoryUtil.forName("user.userId").eqProperty("usertracker.userId"));
 5            DynamicQuery query = DynamicQueryFactoryUtil.forClass(UserTracker.class, "usertracker",PortalClassLoaderUtil.getClassLoader())
 6            .add(PropertyFactoryUtil.forName("usertracker.userId").in(dq0));
 7            
 8            List temp = new ArrayList();
 9            temp.addAll(UserTrackerLocalServiceUtil.dynamicQuery(query));


it returns all fields of UserTracker but we also want fields of User Table, What if we want all the fields from both table to get displayed.
ex. Select * from User_ u , UserTracker ut where u.userId = ut.userId.

Any suggestions. emoticon
Jay Trivedi
RE: Getting Records from 3 tables using Dynamic Query
12 de Fevereiro de 2013 08:06
Resposta

Jay Trivedi

Ranking: Regular Member

Mensagens: 106

Data de entrada: 23 de Novembro de 2012

Mensagens recentes

Thanks David. It makes one side of coin clear keeping other side confused. is How?
How can we do that?

can you please project any views on dynamic query written above for two tables. to get all data of both tables.

Thanks Jay.
Jelmer Kuperus
RE: Getting Records from 3 tables using Dynamic Query
12 de Fevereiro de 2013 12:43
Resposta

Jelmer Kuperus

Ranking: Liferay Legend

Mensagens: 1192

Data de entrada: 10 de Março de 2010

Mensagens recentes

You cannot do joins using Liferay I wrote more on why this is in this thread

I don't believe it's possible to write this particular query using a dynamic query but you can probably do it via custom sql. Try something like this :

 1        SessionFactory sessionFactory = (SessionFactory) PortalBeanLocatorUtil.locate("liferaySessionFactory");
 2
 3        Session session = null;
 4        try {
 5            session = sessionFactory.openSession();
 6
 7            SQLQuery query = session.createSQLQuery(
 8                "select {U.*}, {UT .*}, {UTP.*} " +
 9                "from User_ U, UserTracker UT, UserTrackerPath UTP " +
10                "where U.userId=UT.userId and UT.userTrackerId=UTP.userTrackerId;");
11
12            try {
13                query.addEntity("U", PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl"));
14                query.addEntity("UT", PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserTrackerImpl"));
15                query.addEntity("UTP", PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserTrackerPathImpl"));
16            } catch (ClassNotFoundException e) {
17                throw new IllegalStateException(e); // should never happen
18            }
19
20           // probably a list of object arrays containing a user, usertracker and usertrackerpath
21           List results = QueryUtil.list(query, sessionFactory.getDialect(),  QueryUtil.ALL_POS, QueryUtil.ALL_POS);
22
23        } finally {
24            if (session != null) {
25                sessionFactory.closeSession(session);
26            }
27        }
Jay Trivedi
RE: Getting Records from 3 tables using Dynamic Query
12 de Fevereiro de 2013 23:11
Resposta

Jay Trivedi

Ranking: Regular Member

Mensagens: 106

Data de entrada: 23 de Novembro de 2012

Mensagens recentes

Thanks Jelmer, but i am wondering since here there are three class i am going to use then what should i be writing in
SessionFactory sessionFactory = (SessionFactory) PortalBeanLocatorUtil.locate("liferaySessionFactory");
Jelmer Kuperus
RE: Getting Records from 3 tables using Dynamic Query
13 de Fevereiro de 2013 04:48
Resposta

Jelmer Kuperus

Ranking: Liferay Legend

Mensagens: 1192

Data de entrada: 10 de Março de 2010

Mensagens recentes

but i am wondering since here there are three class i am going to use then what should i be writing in
SessionFactory sessionFactory = (SessionFactory) PortalBeanLocatorUtil.locate("liferaySessionFactory");


Exactly that, liferaySessionFactory is just the name of the spring bean, its the same regardless of what entity you retrieve
Meera Prince
RE: Getting Records from 3 tables using Dynamic Query
13 de Fevereiro de 2013 05:00
Resposta

Meera Prince

Ranking: Liferay Master

Mensagens: 841

Data de entrada: 8 de Fevereiro de 2011

Mensagens recentes

HI Jay Trivedi
Go through the following link
http://www.liferaysavvy.com/2013/02/getting-data-from-multiple-tables-in.html


Regards,
Meera Prince
http://www.liferaysavvy.com/