Forums

Home » Liferay Portal » English » 3. Development

Combination View Flat View Tree View
Threads [ Previous | Next ]
toggle
Roger Thal
Custom queries
February 24, 2012 8:39 PM
Answer

Roger Thal

Rank: New Member

Posts: 16

Join Date: January 21, 2012

Recent Posts

All examples about building custom queries (by creating the Finder classes) always show SELECT * in the SQL statement.

I need to do SUM and AVG, and returning not the full row (the entity), but the computation result of my custom query. Has anyone done it ?

I used this thread http://www.liferay.com/community/wiki/-/wiki/Main/Custom+queries+in+Liferay#section-Custom+queries+in+Liferay-Using+the+query+in+the+portlet to build a prototype, but ran into an error:

04:33:03,019 ERROR [JDBCExceptionReporter:75] Column 'toyid' not found.
com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.excep
tion.SQLGrammarException: could not execute query

In the above 'toyid' is the primary key !!! so it has to exist.

The problem above is similar to this one, posted back in 2009:

http://www.liferay.com/community/forums/-/message_boards/message/2134102?_19_redirect=http%3A%2F%2Fwww.liferay.com%2Fcommunity%2Fforums%2F-%2Fmessage_boards%2Fsearch%3F_19_keywords%3DQueryUtil.list%26_19_searchCategoryId%3D239390%26_19_breadcrumbsCategoryId%3D239390%26_19_redirect%3Dhttp%253A%252F%252Fwww.liferay.com%252Fcommunity%252Fforums%252F-%252Fmessage_boards%252Fcategory%252F239390
Kavita Gupta
RE: Custom queries
February 24, 2012 9:55 PM
Answer

Kavita Gupta

Rank: Junior Member

Posts: 64

Join Date: March 12, 2008

Recent Posts

Hi,

You can give a try by changing your primary key field name.

That might solve ur problem.

Regards,
Kavita
Hitoshi Ozawa
RE: Custom queries
February 26, 2012 5:13 AM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7990

Join Date: March 23, 2010

Recent Posts

Have you tried using Dynamic Query instead?

http://www.liferay.com/community/wiki/-/wiki/Main/Queries+2%3A+DynamicQuery+API

There are several reasons/use cases for wanting to move beyond those existing 'finder' queries:
• the level of complexity allowed by the service generation is not sufficient for your purposes and/or
• you need queries which implement aggregate SQL operations such as max, min, avg, etc.[/quote]
Roger Thal
RE: Custom queries
February 26, 2012 9:32 PM
Answer

Roger Thal

Rank: New Member

Posts: 16

Join Date: January 21, 2012

Recent Posts

Well, after several days of headache trying to work with custom queries using the Service Builder unsuccessfully, I decided to drop down to the JDBC level and finished the job in 2 hours. I looked into Dynamic Queries also (thanks Hitoshi) but was discouraged due to the lack of documentation. My case could have been difficult because I was trying to build a result set that did not consist of full table rows but to contain some of the columns plus a sum and an average computation. On the other hand, JDBC coding is really no big deal - these fancy frameworks lots of time just get in the way, sorry to have to say.
Hitoshi Ozawa
RE: Custom queries
February 27, 2012 5:46 AM
Answer

Hitoshi Ozawa

Rank: Liferay Legend

Posts: 7990

Join Date: March 23, 2010

Recent Posts

Well, it's just a manner of getting used to it. Liferay's framework includes clustering supports and multi-database support to name a few.
However, if you're on a tight schedule and need to get something done quick and you can do it with what you already know, it may be
the best choice for the job. Better to get things finished. emoticon
Jignesh Vachhani
RE: Custom queries
March 19, 2012 4:28 AM
Answer

Jignesh Vachhani

Rank: Liferay Master

Posts: 775

Join Date: March 10, 2008

Recent Posts

You can have more details about custom query on http://www.liferaysolution.com/2012/03/custome-query.html as well
Avinash R
RE: Custom queries
November 10, 2013 4:35 AM
Answer

Avinash R

Rank: New Member

Posts: 13

Join Date: September 19, 2013

Recent Posts

Roger Thal:
Well, after several days of headache trying to work with custom queries using the Service Builder unsuccessfully, I decided to drop down to the JDBC level and finished the job in 2 hours.


Roger,
Can you explain how you did with the jdbc?
To clarify: did you create the connection yourself or did you get the connection using liferay utilities?, if latter, which utility was used?

I had the same kind of requirement. where I calculated the count after loading everything.