Korma has-many through another table

Question

I have some tables like this:

CREATE TABLE place (
    id INTEGER NOT NULL, 
    name VARCHAR(75)
);

CREATE TABLE place_services (
    id INTEGER NOT NULL,
    place_id INTEGER
);

CREATE TABLE service (
    name VARCHAR(100),
    value BOOL,
    place_services_id INTEGER
);

I am using the Korma library in clojure and I wish to have a query which gets a place for an id, with a list of all the services. So far I have this:

(defentity service)

(defentity place-services
  (table :place_services)
  (has-many service)

(defentity place
  (has-one place-services))

(select place
  (join place-services (= :place_services.place_id :id))
  (join service (= :place_services.id :service.place_services_id))     

This appears to generate the correct joins but the service fields are not included. I tried adding

(fields :service.name)

And it returned one of the names of the services. I was wondering if it was possible to get a list of all of them in one query. Thanks

Answer 1

Korma will take care of doing the joins as you have already defined the relationships:

   (select place (with place-services (with service)))

Note that this seems to do n+1 queries so check that this is acceptable for your app.

If you have tovdo the join manually, any string that you specify in the fields will be pass to the query without touching it, so you can add all the fields with:

(select place
   (fields "service.*")
   (join place-services (= :place_services.place_id :id))
   (join service (= :place_services.id :service.place_services_id)))