-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 26 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Optimising Query (how to use Join?)
PostPosted: Fri Nov 18, 2005 11:04 am 
Beginner
Beginner

Joined: Thu Aug 11, 2005 5:19 am
Posts: 29
Hi guys!

I have little question concerning using of the join operation.
I have two classes:

DeliveryTariff and CountryGroup

CountryGroup is very simple and looks like:

ID | name | country
____________________
0 DHL-EU DE
1 DHL-EU FR
2 DHL-EU UK
3 GLS-EU DE
4 GLS-EU BE
....


DeliveryTariff allways depenends on exactly one CountryGroup:


ID | name | country_grop |price
________________________________
0 DHLEU1 DHL-EU 20€
1 GLSDEBE GLS-EU 10€
....


I need to get all tariffs aviable for specific country.
What I do now is to make lot of querys:

1) get all groups for a country
2) for every group get a tariff

How can I do it better? I tryed to use join, but I don't know how to connect CountryGroup and DeliveryTariff by country_group attribute. And :(

I know, this is a lame question, but I'm not a DB/Hibernate guru [jet], so please help and save my weekend! :)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 11:35 am 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
show mapping file

you might be able to write hql like this

from tariff where tariff.country = :xxx

_________________
Anthony,
Get value thanks to your skills: http://www.redhat.com/certification


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 11:49 am 
Beginner
Beginner

Joined: Thu Aug 11, 2005 5:19 am
Posts: 29
Well, I can't say

from tariff where tariff.country = :xxx

I need something more like

from tariff where tariff.countryGroup.country=:xxx

hm... :) maybe I gonna get it...

Files:

<class name="DeliveryTariff" table="delivery_tariff">
<id name="id" column="ID" type="long">
<generator class="native"/>
</id>

<property name="name" column="name" type="string" />
<property name="company" column="company" type="string" />
<property name="userGroup" column="user_group" type="long" />
<property name="basePrice" column="base_price" type="float" />
<property name="kgPrice" column="kg_price" type="float" />
<property name="codOn" column="COD_on" type="byte" />
<property name="codPrice" column="COD_price" type="float" />
<property name="customsDuty" column="customs_duty" type="float" />
<property name="countryGroup" column="country_group" type="string" />
<property name="prio" column="prio" type="integer" />

</class>


<class name="CountryGroup" table="country_group">
<id name="id" column="ID" type="long">
<generator class="native"/>
</id>

<property name="name" column="name" type="string" />
<property name="country" column="country" type="string" />


</class>


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 11:55 am 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
you don't have any relationship?

_________________
Anthony,
Get value thanks to your skills: http://www.redhat.com/certification


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 11:55 am 
Beginner
Beginner

Joined: Thu Aug 11, 2005 5:19 am
Posts: 29
ok, the real question is how to connect

CountryGroup and DeliveryTariff,
if for every Tariff we need all the CountryGroups with

DeliveryTariff.name == DeliveryTariff.country_group


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 11:56 am 
Beginner
Beginner

Joined: Thu Aug 11, 2005 5:19 am
Posts: 29
No, I have no relationship.
But I sure do need some.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 12:02 pm 
Beginner
Beginner

Joined: Thu Aug 11, 2005 5:19 am
Posts: 29
DeliveryTariffok, the real question is how to connect

CountryGroup and DeliveryTariff,
if for every Tariff we need all the CountryGroups with

It had to be:

ok, the real question is how to connect

CountryGroup and DeliveryTariff,
if for every Tariff we need all the CountryGroups with

CountryGroup.name == DeliveryTariff.country_group


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 12:03 pm 
Hibernate Team
Hibernate Team

Joined: Thu Dec 18, 2003 9:55 am
Posts: 1977
Location: France
please understand that, in general, each time you have a foreign key, you'll have some one-to-many, many-to-one or both in your mapping file...

_________________
Anthony,
Get value thanks to your skills: http://www.redhat.com/certification


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 12:06 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
add many-to one instead property for countryGroup in DeliveryTariff and call

from DeliverTariff as t where t.countryGroup.country=:x


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 12:14 pm 
Beginner
Beginner

Joined: Thu Aug 11, 2005 5:19 am
Posts: 29
anthony

This is not really a foreign key I supose.

There are a lot of CountryGroups named DHL-EU, becouse it's my way to put particular countries in the group.

And on the other hand n Tariffs can depend on one country group...

Anyway, I'm unable to set foreign key constraint in mySql...


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 12:15 pm 
Beginner
Beginner

Joined: Thu Aug 11, 2005 5:19 am
Posts: 29
snpesnpe

Yeap, I wan't to do it, but don't know how :)


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 12:52 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
this is example
Code:
<many-to-one name="countryGroup" class="CountryGroup" not-null="true">
         <column name="country_group" />


add this and remove property countryGroup


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 12:55 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
Code:
<many-to-one name="countryGroup" class="CountryGroup" not-null="true">
         <column name="country_group" />
</many-to-one>


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 12:59 pm 
Beginner
Beginner

Joined: Thu Aug 11, 2005 5:19 am
Posts: 29
@snpesnpe

Yeap, but how Hibernate gonna know, it needs the "name" table of CountryGroup to compare with country_group prop of DeliveryTariff?

CountryGroup.name is not an index and not a key... :/


Top
 Profile  
 
 Post subject:
PostPosted: Fri Nov 18, 2005 1:27 pm 
Expert
Expert

Joined: Sat Jun 12, 2004 4:49 pm
Posts: 915
you define name in CountryGroup class (table, property,column etc) and hibernate is smart enough

many-to-one have column definition 'country_group' in DeliveryTariff and ID property in CountryGroup class is join expression and hibernate will join
delivery.country_group=countryGroup.id


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 26 posts ]  Go to page 1, 2  Next

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.