Working with 1-to-M and M-to-M relationships

[csdrane]

This probably isn't a bug with Korma but I am a new user and am having trouble querying across tables.

Table schema:

(defn create-users-table []
  (db-do-commands db-spec
                  (create-table-ddl :users
                                    [:userid :integer "PRIMARY KEY" "AUTO_INCREMENT"]
                                    [:username "varchar(32)" "UNIQUE"]
                                    [:password "char(60)"] 
                                    [:email "varchar(255)" "UNIQUE"])))

(defn create-tracked-links-table []
  (db-do-commands db-spec (create-table-ddl :trackedlinks
                                            [:userid :integer "references users (userid)"]
                                            [:actionid :integer "PRIMARY KEY" "AUTO_INCREMENT"]
                                            [:productid :integer "references products (productid)"])))

(defn create-products-table []
  (db-do-commands db-spec (create-table-ddl :products
                                            [:productid :integer "PRIMARY KEY" "AUTO_INCREMENT"]
                                            [:url "varchar(2083)"])))

(defn create-prices-table []
  (db-do-commands db-spec (create-table-ddl :prices
                                            [:priceid :integer "PRIMARY KEY" "AUTO_INCREMENT"]
                                            [:productid :integer "references products (productid)"]
                                            [:date "date"]
                                            [:price "decimal"])))

Which I represented in Korma as:

(defentity users
  (pk :userid)
  (entity-fields :username :password :email)
  (has-many tracked-links))

(defentity tracked-links
  (table :trackedlinks)
  (pk :actionid)
  (belongs-to users {:fk :userid})
  (has-one products {:fk :productid}))

(defentity products
  (pk :productid)
  (entity-fields :url)
  (has-many tracked-links))

(defentity prices
  (pk :priceid)
  (belongs-to products)
  (entity-fields :date :price))

I'm currently focusing on trying to do a query between Users and Tracked-Links.

Sample query:

user> (select users (with tracked-links) (where {:username "asdf"}))
MySQLSyntaxErrorException Unknown column 'trackedlinks.users_id' in 'where clause'  sun.reflect.NativeConstructorAccessorImpl.newInstance0 (Nat\
iveConstructorAccessorImpl.java:-2)

I'm unclear on why I'm getting that error. I observe that if I add the {foreign key} constraint to the has-many statement under the definition of the Users entity that this error goes away. But then the tracked-links returns an empty list when there should be data returned.

I'm guessing that something is wrong with the way that I've either defined the table schema or Korma's entity relationships or possibly both. I am not sure though what this might be. Any help is appreciated.

[immoh]

Since your foreign key name is not users_id you need to add the foreign key name userid to has-many statement.

The reason why you're not getting any data is that the primary key of users is not included in entity-fields of users. Korma fetches one-to-many relations with a separate query for each main entity result. If the primary key is not included it doesn't return any child entities. There's already an issue about this: #222

[immoh]

Closing as duplicate.