unable to join detail table

[Eelco Hillenius]

Hi,

I'm having trouble implementing what I assume is a pretty typical case.

Take these tables:

create table `product_type` (

    `id` int not null,

    `type_name` varchar(50),

    primary key (`id`)

);

insert into `product_type` (`id`, `type_name`) 

    values (1, 'shoes'), (2, 'pants');

create table `product` (

    `id` int,

    `name` varchar(50),

    `product_type_id` int,

    primary key (`id`),

    foreign key (`product_type_id`) references `product_type` (`id`)

);

insert into `product` (`id`, `name`, `product_type_id`)

    values (1, 'bootz', 1), (2, 'bags', 2), (3, 'drags', 2);

In plain SQL, I'd do 

select p.name, t.type_name from product p join product_type t on t.id = p.product_type_id

Now then, what should my definitions for korma look like in order to query like:

(select product (with product_type))

?

(defentity product)

(defentity product_type (belongs-to product))

yields: Exception No relationship defined for table: product_type  korma.core/with* (core.clj:709)

And has-one or has-many seems to want to do the inverse of what I want (thinks the foreign key is in product_type instead of product).

And then using an explicit join:

(sql-only (select product (join :inner product_type (= :product_type.id :product.product_type_id))))

this yields:

"SELECT `product`.* FROM `product` INNER JOIN `product_type` ON `product_type`.`id` = `product`.`product_type_id`"

but I also need the fields of the joined table instead of just from product.*

Any hints how I should go about it?

Thanks,

Eelco

[Michael Pheasant]

Try this:

(declare product product_type) ; actually only need product here, but...

(defentity product_type 

  (has-many product))

(defentity product

  (belongs-to product_type)))

Then your 

(select product (with product_type) should work assuming both have id column.

[Eelco Hillenius]

Hi Michael, sorry for the late reply. I tried similar things, but
didn't get that to work actually. Tbh, I moved on and now just use raw
queries for the most part.

Thanks for replying.

Eelco

> --
> You received this message because you are subscribed to a topic in the
> Google Groups "Korma" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlkorma/81I97F8YUBk/unsubscribe?hl=en.
> To unsubscribe from this group and all its topics, send an email to
> sqlkorma+u...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>