These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 
  Previous topic | Next topic 
Author Message
RegisMelo
 Post subject: HQL to retrieve the Top 10 most sold products
PostPosted: Tue Sep 07, 2004 9:41 pm 
Newbie

Joined: Tue Jun 08, 2004 11:41 am
Posts: 10
Location: Fortaleza/Brazil
I've two table - Sales and Products. I want to retrieve the Top 10 most sold products.

I try to do this with this HQL:

String qry = "select l, sum( Vendas.quantidade ) " +
"from Livro l " +
"group by l.codigo " +
"order by sum( Vendas.quantidade )";

The sales table has a relationship mapped to product:

----- Sales.hbm.xml ----------

<many-to-one
name="product"
class="bean.product"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="cd_product"
/>

How can I do that? The query above produce:

select
product0_.cd_product as cd_product,
product0_.nm_product as nm_product,
from product product0_
group by product0_.cd_product
order by sum(Vendas.quantidade)

That is, of course, a wrong query!

How can I do that?

Thanks!
Top
 Profile  
 
RegisMelo
 Post subject: One more information...
PostPosted: Tue Sep 07, 2004 10:08 pm 
Newbie

Joined: Tue Jun 08, 2004 11:41 am
Posts: 10
Location: Fortaleza/Brazil
Code:
String qry = "select sale.produto.id, " +
       "       sale.product.name,   " +
       "       sum( sale.qty ) " +
       "from Sale sale " +
       "group by sale.product.id, sale.product.name " +
       "order by sum( sale.qty ) desc"


I try this variation of first query and Hibernate generates this SQL:

Code:

select  product1_.id as x0_0_,
   product1_.product_name as x1_0_,
   sum(sale0_.qty) as x2_0_
from    sales sale0_,
   product product1_
where sale0_.productId=product1_.id
group by  sale0_.productId , product1_.product_name
order by  sum(sale0_.qty)desc



Why Hibernate use "group by sale0_.productId? The correct is to use product1_.id!!!!

Thanks in Advance!

Regis
Top
 Profile  
 
Myk
 Post subject:
PostPosted: Wed Sep 08, 2004 3:12 am 
Senior
Senior

Joined: Sun Jan 04, 2004 2:46 pm
Posts: 147
Because of this join

Code:
where sale0_.productId=product1_.id


in this case the two are equivalent.
Top
 Profile  
 
RegisMelo
 Post subject: It's equivalent!
PostPosted: Wed Sep 08, 2004 5:37 am 
Newbie

Joined: Tue Jun 08, 2004 11:41 am
Posts: 10
Location: Fortaleza/Brazil
Myk wrote:
Because of this join

Code:
where sale0_.productId=product1_.id


in this case the two are equivalent.


Hello Myk,

The join is equivalent! My table sale has a relations ship with product. The primary key of product is as id and a foreign key of sales is productId. It's equivalent!

The structure of my tables is:


Code:
Product
----------------------------------------------
id  int no null
name varchar(40) not null
----------------------------------------------

Sales
----------------------------------------------
idSequence int not null
productId int not null    (FK)
qty int not null
----------------------------------------------



Thanks in advance!
Top
 Profile  
 
RegisMelo
 Post subject:
PostPosted: Fri Sep 10, 2004 9:31 am 
Newbie

Joined: Tue Jun 08, 2004 11:41 am
Posts: 10
Location: Fortaleza/Brazil
Please helppppppp..................
Top
 Profile  
 
Teg
 Post subject:
PostPosted: Fri Sep 10, 2004 10:43 am 
Regular
Regular

Joined: Wed Aug 18, 2004 5:16 am
Posts: 69
Location: Modena, Italy
I have created a simple example and this work:
Code:
select prod.id, prod.name, sum( sale.qty ) from Product prod join prod.sales sale group by prod.id, prod.name order by sum( sale.qty ) desc


If you need I can post the mapping files
Top
 Profile  
 
RegisMelo
 Post subject:
PostPosted: Fri Sep 10, 2004 12:28 pm 
Newbie

Joined: Tue Jun 08, 2004 11:41 am
Posts: 10
Location: Fortaleza/Brazil
Teg wrote:
I have created a simple example and this work:
Code:
select prod.id, prod.name, sum( sale.qty ) from Product prod join prod.sales sale group by prod.id, prod.name order by sum( sale.qty ) desc


If you need I can post the mapping files


Please, post the mapping files...

Thanks!
Top
 Profile  
 
RegisMelo
 Post subject:
PostPosted: Sat Sep 11, 2004 2:11 pm 
Newbie

Joined: Tue Jun 08, 2004 11:41 am
Posts: 10
Location: Fortaleza/Brazil
Hello,


When I try to execute the query below (to return the top most sold books)


String qry = "select l, sum(venda.quantidade) " +
"from Livro l " +
"group by l.codigo " +
"order by sum(venda.quantidade) ";



Hibernate generates this:

Hibernate: select livro0_.cd_livro as cd_livro, livro0_.nm_livro as nm_livro, livro0_.ds_notas as ds_notas, livro0_.vr_preco as vr_preco, livro0_.dt_publicacao as dt_publi5_, livro0_.cd_tipo as cd_tipo from livro livro0_ group by livro0_.cd_livro order by sum(venda.quantidade)


That is a wrong query!

How can I do that?? :-(



Quote:
<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class
name="bean.Livro"
table="livro"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="codigo"
column="cd_livro"
type="int"
>
<generator class="hilo">
<param name="table">nrAutor</param>
<param name="column">next_value</param>
</generator>
</id>

<property
name="nome"
type="java.lang.String"
update="true"
insert="true"
column="nm_livro"
/>

<property
name="notas"
type="java.lang.String"
update="true"
insert="true"
column="ds_notas"
/>

<property
name="preco"
type="java.math.BigDecimal"
update="true"
insert="true"
column="vr_preco"
/>

<property
name="publicacao"
type="java.util.Date"
update="true"
insert="true"
column="dt_publicacao"
/>

<many-to-one
name="tipoLivro"
class="bean.TipoLivro"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="cd_tipo"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Livro.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>




Quote:
<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class
name="bean.Livro"
table="livro"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="codigo"
column="cd_livro"
type="int"
>
<generator class="hilo">
<param name="table">nrAutor</param>
<param name="column">next_value</param>
</generator>
</id>

<property
name="nome"
type="java.lang.String"
update="true"
insert="true"
column="nm_livro"
/>

<property
name="notas"
type="java.lang.String"
update="true"
insert="true"
column="ds_notas"
/>

<property
name="preco"
type="java.math.BigDecimal"
update="true"
insert="true"
column="vr_preco"
/>

<property
name="publicacao"
type="java.util.Date"
update="true"
insert="true"
column="dt_publicacao"
/>

<many-to-one
name="tipoLivro"
class="bean.TipoLivro"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="cd_tipo"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Livro.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>
Top
 Profile  
 
Teg
 Post subject:
PostPosted: Mon Sep 13, 2004 2:29 am 
Regular
Regular

Joined: Wed Aug 18, 2004 5:16 am
Posts: 69
Location: Modena, Italy
This is the mapping I have used:
Code:
    <class name="Product" table="PRODUCTS">
        <id name="id" column="ID" type="long" unsaved-value="0">
            <generator class="increment"/>
        </id>
       
        <property name="name" column="NAME" type="string" length="256"/>
       
        <set name="sales" inverse="true" cascade="all-delete-orphan">
            <key column="PRODUCT_ID"/>
            <one-to-many class="Sale"/>
        </set>
    </class>
   
    <class name="Sale" table="SALES">
        <id name="id" column="ID" type="long" unsaved-value="0">
            <generator class="increment"/>
        </id>
       
        <property name="qty" column="QTY" type="integer"/>
        <many-to-one name="product" class="Product" column="PRODUCT_ID" cascade="all" not-null="true"/>
    </class>

Try to use in select the elements you use in group by clause (l.codigco instead of l)
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 9 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.