Foros de discusión

Custom queries

Roger Thal, modificado hace 12 años.

Custom queries

New Member Mensajes: 21 Fecha de incorporación: 21/01/12 Mensajes recientes
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, modificado hace 12 años.

RE: Custom queries

Junior Member Mensajes: 64 Fecha de incorporación: 12/03/08 Mensajes recientes
Hi,

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

That might solve ur problem.

Regards,
Kavita
thumbnail
Hitoshi Ozawa, modificado hace 12 años.

RE: Custom queries

Liferay Legend Mensajes: 7942 Fecha de incorporación: 24/03/10 Mensajes recientes
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, modificado hace 12 años.

RE: Custom queries

New Member Mensajes: 21 Fecha de incorporación: 21/01/12 Mensajes recientes
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.
thumbnail
Hitoshi Ozawa, modificado hace 12 años.

RE: Custom queries

Liferay Legend Mensajes: 7942 Fecha de incorporación: 24/03/10 Mensajes recientes
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
thumbnail
Avinash R, modificado hace 10 años.

RE: Custom queries

New Member Mensajes: 13 Fecha de incorporación: 19/09/13 Mensajes recientes
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.
Roger Thal, modificado hace 8 años.

Re: [Liferay Forums][3. Development] RE: Custom queries

New Member Mensajes: 21 Fecha de incorporación: 21/01/12 Mensajes recientes
Geez, it's been a long time and I don't have the code around any more. Just
get a JDBC connection one way or the other, and use it directly.


On Sun, Nov 10, 2013 at 6:35 AM, Avinash R from liferay.com <
forums@liferay.com> wrote:

> 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.
>
>
> To view the thread, follow the link below:
>
> https://www.liferay.com/community/forums/-/message_boards/view_message/31305013
> --
> Liferay.com <https://www.liferay.com>
Roger Thal, modificado hace 8 años.

Re: [Liferay Forums][3. Development] RE: Custom queries

New Member Mensajes: 21 Fecha de incorporación: 21/01/12 Mensajes recientes
Geez, it's been a long time and I don't have the code around any more. Just
get a JDBC connection one way or the other, and use it directly.


On Sun, Nov 10, 2013 at 6:35 AM, Avinash R from liferay.com <
forums@liferay.com> wrote:

> 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.
>
>
> To view the thread, follow the link below:
>
> https://www.liferay.com/community/forums/-/message_boards/view_message/31305013
> --
> Liferay.com <https://www.liferay.com>
Roger Thal, modificado hace 8 años.

Re: [Liferay Forums][3. Development] RE: Custom queries

New Member Mensajes: 21 Fecha de incorporación: 21/01/12 Mensajes recientes
Geez, it's been a long time and I don't have the code around any more. Just
get a JDBC connection one way or the other, and use it directly.


On Sun, Nov 10, 2013 at 6:35 AM, Avinash R from liferay.com <
forums@liferay.com> wrote:

> 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.
>
>
> To view the thread, follow the link below:
>
> https://www.liferay.com/community/forums/-/message_boards/view_message/31305013
> --
> Liferay.com <https://www.liferay.com>
Roger Thal, modificado hace 8 años.

Re: [Liferay Forums][3. Development] RE: Custom queries

New Member Mensajes: 21 Fecha de incorporación: 21/01/12 Mensajes recientes
Geez, it's been a long time and I don't have the code around any more. Just
get a JDBC connection one way or the other, and use it directly.


On Sun, Nov 10, 2013 at 6:35 AM, Avinash R from liferay.com <
forums@liferay.com> wrote:

> 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.
>
>
> To view the thread, follow the link below:
>
> https://www.liferay.com/community/forums/-/message_boards/view_message/31305013
> --
> Liferay.com <https://www.liferay.com>
Roger Thal, modificado hace 8 años.

Re: [Liferay Forums][3. Development] RE: Custom queries

New Member Mensajes: 21 Fecha de incorporación: 21/01/12 Mensajes recientes
Geez, it's been a long time and I don't have the code around any more. Just
get a JDBC connection one way or the other, and use it directly.


On Sun, Nov 10, 2013 at 6:35 AM, Avinash R from liferay.com <
forums@liferay.com> wrote:

> 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.
>
>
> To view the thread, follow the link below:
>
> https://www.liferay.com/community/forums/-/message_boards/view_message/31305013
> --
> Liferay.com <https://www.liferay.com>
thumbnail
Jignesh Vachhani, modificado hace 12 años.

RE: Custom queries

Liferay Master Mensajes: 803 Fecha de incorporación: 10/03/08 Mensajes recientes
You can have more details about custom query on http://www.liferaysolution.com/2012/03/custome-query.html as well