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! :)

show mapping file

you might be able to write hql like this

from tariff where tariff.country = :xxx

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>

you don't have any relationship?

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

No, I have no relationship.
But I sure do need some.

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

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

add many-to one instead property for countryGroup in DeliveryTariff and call

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

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

snpesnpe

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

this is example


add this and remove property countryGroup

@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... :/

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