Forums

Home » Liferay Portal » English » 3. Development

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Adam Victor Nazareth Brandizzi
Finding users by birthday
January 19, 2011 5:09 AM
Answer

Adam Victor Nazareth Brandizzi

Rank: Junior Member

Posts: 67

Join Date: April 30, 2010

Recent Posts

Hello, all!

How could I find the users who have a birthday in a specific day in a portlet created via plugin SDK?

I tried to use custom queries as described here, but it does not work for native Liferay entities such as users and contacts. (Actually, I asked about this problem here.)

So, what other approach would you all suggest?

Thanks in advance!
Jelmer Kuperus
RE: Finding users by birthday
January 19, 2011 5:30 AM
Answer

Jelmer Kuperus

Rank: Liferay Legend

Posts: 1192

Join Date: March 10, 2010

Recent Posts

You can do that with the DynamicQuery api
Adam Victor Nazareth Brandizzi
RE: Finding users by birthday
January 19, 2011 6:39 AM
Answer

Adam Victor Nazareth Brandizzi

Rank: Junior Member

Posts: 67

Join Date: April 30, 2010

Recent Posts

We thought it too, but there are two problems. Firstly, the birthday is stored in the Contact entity, so we should find all contacts by birthday and then search users by the contact ids. It is surely possible but we don't find it very elegant...

However, the biggest problem is to find a user whose birthday is in any year. How could I create a dynamic query for finding a user whose birthday is at such day of such month, but ignores the year?

Thank you for your answer! I hope you all can help me again emoticon
Jelmer Kuperus
RE: Finding users by birthday
January 19, 2011 3:22 PM
Answer

Jelmer Kuperus

Rank: Liferay Legend

Posts: 1192

Join Date: March 10, 2010

Recent Posts

Hmmm... the first problem would be relatively easy to solve because you can use subselects with dynamic queries like i for instance show in this post :

http://www.liferay.com/community/forums/-/message_boards/message/6450100

The second problem... not that easy. I actually don't see how you could achieve this with the dynamic query api, since you would need to use sql functions. And i dont believe those are supported

You should be able to do something like this

 1SessionFactory sessionFactory = (SessionFactory) PortalBeanLocatorUtil.locate("liferaySessionFactory");
 2Session session = null;
 3try {
 4    session = sessionFactory.openSession();
 5
 6    SQLQuery query = session.createSQLQuery("select {User_.*} from User_ where userId = ? ");
 7    query.addEntity("User_", PortalClassLoaderUtil.getClassLoader().loadClass("com.liferay.portal.model.impl.UserImpl"));
 8
 9    QueryPos queryPos = QueryPos.getInstance(query);
10    queryPos.add(11548);
11
12    return (List<User>) QueryUtil.list(query, getDialect(), 0, QueryUtil.ALL_POS);
13
14} finally {
15    if (session != null) {
16        sessionFactory.closeSession(session);
17    }
18}


But you would have to call this code from a transactional servicebuilder method for it to work


Another option would be to directly go to the database, bypassing liferay by grabbing InfrastructureUtil.getDataSource()
Nicholas Tenczar
RE: Finding users by birthday
January 19, 2011 4:45 PM
Answer

Nicholas Tenczar

Rank: Junior Member

Posts: 53

Join Date: July 14, 2010

Recent Posts

Take a look at the CustomSQLParam class. This class takes an sql string and a value and allows you to create new WHERE/JOIN clauses in Liferay defined CustomSQL queries. For instance you could do something like this.
1
2INNER JOIN
3    Contact_ ON
4    User_.userId = Contact_.userId
5WHERE
6    Contact_.birthday = ?


The only problem that I foresee with this method is that the process() method of CustomSQLParam only recognizes Long, Long[], String, and String[]. My use case required an Object[] with String and Long values, so I subclassed CustomSQLParam and overrode the process() method.

Take a look at UserFinderImpl's getWhere(), getJoin(), and setJoin() methods to see how the CustomSQLParam is used to create larger queries. I could probably write a full blog post to describe how CustomSQLParams work, so if something is unclear let me know and I can clarify any issues.
Adam Victor Nazareth Brandizzi
RE: Finding users by birthday
January 20, 2011 2:05 PM
Answer

Adam Victor Nazareth Brandizzi

Rank: Junior Member

Posts: 67

Join Date: April 30, 2010

Recent Posts

jelmer kuperus:
Hmmm... the first problem would be relatively easy to solve because you can use subselects with dynamic queries like i for instance show in this post :

http://www.liferay.com/community/forums/-/message_boards/message/6450100


Actually, it will not help that much in this case given the second problem... On the other hand, we implemented a finder for retrieving only the user ids of the contacts and then used it for retrieve the users with the "in" operator.

jelmer kuperus:
The second problem... not that easy. I actually don't see how you could achieve this with the dynamic query api, since you would need to use sql functions. And i dont believe those are supported

You should be able to do something like this


Jelmer, YOU ARE A GENIUS! That is exactly what I was looking for. At first we did it:

1
2Session session = null;
3    try {
4        session = openSession();


but now I tried to do what you suggest:

1SessionFactory sessionFactory = (SessionFactory) PortalBeanLocatorUtil.locate("liferaySessionFactory");
2Session session = null;
3try {
4    session = sessionFactory.openSession();


and it worked flawlessly (in my example, not in the real application). Thank you! We will use it!


jelmer kuperus:
Another option would be to directly go to the database, bypassing liferay by grabbing InfrastructureUtil.getDataSource()


That seems pretty painful... and pretty unnecessary too, fortunately emoticon

Thank you again!
Adam Victor Nazareth Brandizzi
RE: Finding users by birthday
January 20, 2011 2:21 PM
Answer

Adam Victor Nazareth Brandizzi

Rank: Junior Member

Posts: 67

Join Date: April 30, 2010

Recent Posts

Hi, Nicholas.

I found the CustomSQLParam a bit complex for understanding... but actually I just take a brief look at it. Since the problem is (apparently) solved, probably I will not use it for now. However, I am very curious about it, so I will study it anyway. Also, if you write this blog post, let us know, ok? emoticon

Thanks!
dola dola
RE: Finding users by birthday
November 29, 2012 5:24 AM
Answer

dola dola

Rank: New Member

Posts: 7

Join Date: December 29, 2011

Recent Posts

Hi ,
can i get all birthdays of all users into new portlet ?...

thanks emoticon