Subclass Mapping and Cartesian product on postgres

tradem · **Joined:** Mon Oct 06, 2003 1:59 am **Posts:** 52

Untfortunally the HQL of Hibernate 2.1.4 translates a cross join in HQL into a inner join. This is influenced by a hibernate property or some other configuration? How can I express a cartesian product in HQL which is not transformed into a inner join.

Code:

SELECT count(contact.id) FROM innowake.apogee.domain.model.master.Contact AS contact, innowake.apogee.domain.model.master.Drugstore AS customer WHERE (contact.customer = customer  AND customer.name LIKE 'P%'AND contact.active = true ) AND contact.name.firstName like 'H%' AND contact.name.lastName like 'S%'

is translated into:

Code:

select count(contact0_.id) as x0_0_ from CONTACT contact0_, DRUGSTORE drugstore1_ inner join CUSTOMER drugstore1__1_ on drugstore1_.ID=drugstore1__1_.id where ((contact0_.CUSTOMER_ID=drugstore1_.ID )AND(drugstore1__1_.NAME LIKE 'P%' )AND(contact0_.ACTIVE=true ))AND(contact0_.FIRST_NAME like 'H%' )AND(contact0_.LAST_NAME like 'S%' )

Unfortunaly I cannot use the joined customer as a criteria in WHERE clause.

So what it the reason for this? Drugstore is a Customer and mapped by a joined-subclass. Is inheritance the problem? If yes, so the inner join stuff is not the real problem... If so who should the HQL look's a like?

Thx
Toby

gavin · **Posted:** Tue Jul 27, 2004 11:00 am

I don't understand the problem. Looks perfect to me.

tradem · **Joined:** Mon Oct 06, 2003 1:59 am **Posts:** 52

gavin wrote:

I don't understand the problem. Looks perfect to me.

Hey Gavin! Sorry for the delay. Yesterday I was in Hamburg so I haven't the chance to test again and reply.

The problem IHMO has to do with inheritance and joined subclass mapping here. Drugstore is a subclass of Customer and the attributes used in the query refer to the customer table not to the drugstore table...

So this is how the drugstore table looks a like in postgres:

Code:

                Table "public.drugstore"
       Column       |         Type          | Modifiers
--------------------+-----------------------+-----------
 id                 | bigint                | not null
 drugstore_group_id | bigint                |
 location           | character varying(15) |
Indexes:
    "drugstore_pkey" primary key, btree (id)
Foreign-key constraints:
    "fkb6a09d2191b" FOREIGN KEY (id) REFERENCES customer(id)
    "fkb6a09d219771a859" FOREIGN KEY (drugstore_group_id) REFERENCES drugstore_group(id)

As you can see: the table has no name column. What I really does not understand: postgres does not take care about the missing column! I copied the generated sql query and executed it in the database (connected via psql command line tool) to verify that its not hibernate's fault ;).

Any explaination/guessing/thougths of/for this weird phenomenon?

Anyway I got a workaround for this problem:

Code:

SELECT count(contact.id) FROM foo.bar.Contact AS contact  inner join contact.customer  AS customer WHERE (contact.customer = customer  AND customer.name LIKE '%'AND contact.active = true ) AND contact.name.firstName like 'H%' AND contact.name.lastName like 'S%'

This works like a charme!

(Please note that the passed LIKE comparators used are just parameters and hat actually nothing to do with the problem)

Thank you

Toby