Missing FROM-clause entry for table?

Question

When I select a specific field from a table in a has-many relationship I get an exception like this: org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table \"bar\"

Here are the relationships I have defined:

(declare foo bar)

(korma/defentity foo
                 (korma/pk :token)
                 (korma/database db)
                 (korma/table :foo)
                 (korma/has-many bar {:fk :token}))

(korma/defentity bar
                 (korma/pk :token)
                 (korma/database db)
                 (korma/table :bar)
                 (korma/belongs-to foo {:fk :token}))

Here is the query I am generating in sqlkorma:

(defn fetch []
  (->
    (korma/select* foo)
    (korma/with bar)
    (korma/fields :foo.token :bar.col)
    (korma/exec)))

If I select all the fields using with * then the query runs with no problem. However, I would like to specify which fields I want to select, what am I doing wrong?

Alan Thompson · Answer 1 · 2018-04-17T17:42:42.630

I gave up trying to do it with Korma. I think you may be better off with basic SQL interop:

(ns tst.demo.jdbc
  (:use demo.core tupelo.core tupelo.test)
  (:require
    [clojure.java.jdbc :as jdbc]
    [clojure.string :as str]
    [clojure.java.io :as io] ))

(def db {:classname   "org.h2.Driver"
         :subprotocol "h2:mem"
        ;:subname     "./korma.db"
         :subname     "demo;DB_CLOSE_DELAY=-1" ; -1 very important!!!
                          ; http://www.h2database.com/html/features.html#in_memory_databases
                          ; http://makble.com/using-h2-in-memory-database-in-clojure
         :user "sa"
         :password ""
         })

(dotest
  (spyx (jdbc/db-do-commands db ["drop table if exists langs"]))
  (spyx (jdbc/db-do-commands db ["drop table if exists releases"]))
  (spy :create
       (jdbc/db-do-commands
         db
         (jdbc/create-table-ddl :langs
                                [[:id :serial]
                                 [:lang "varchar not null"]])))
  (spy :create
       (jdbc/db-do-commands
         db
         (jdbc/create-table-ddl :releases
                                [[:id :serial]
                                 [:desc "varchar not null"]
                                 [:langId "numeric"]])))
  (spy :insert
       (jdbc/insert-multi! db :langs
                           [{:lang "Clojure"}
                            {:lang "Java"}]))
  ;; -> ({:lang "Clojure", :id 1} {:lang "Java", :id 2})
  (spyx-pretty (jdbc/query db ["select * from langs"]))

  (let [clj-id (grab :id (only (jdbc/query db ["select id from langs where lang='Clojure'"])))]
    (spyx clj-id)
    (spy :insert-rel
         (jdbc/insert-multi! db :releases
                             [{:desc "ancients" :langId clj-id}
                              {:desc "1.8" :langId clj-id}
                              {:desc "1.9" :langId clj-id}])) )
  (let [java-id (grab :id (only (jdbc/query db ["select id from langs where lang='Java'"])))]
    (spyx java-id)
    (spy :insert-rel
         (jdbc/insert-multi! db :releases
                             [{:desc "dusty" :langId java-id}
                              {:desc "8" :langId java-id}
                              {:desc "9" :langId java-id}
                              {:desc "10" :langId java-id}])) )
  (spyx-pretty
   (jdbc/query db [
   "select langs.lang, releases.desc
       from langs inner join releases
       on (langs.id = releases.langId)
       where lang = 'Clojure' "]) )
  )

with result:

(jdbc/db-do-commands db ["drop table if exists langs"]) => (0)
(jdbc/db-do-commands db ["drop table if exists releases"]) => (0)
:create => (0)
:create => (0)
:insert => ({:id 1} {:id 2})
(jdbc/query db ["select * from langs"]) =>
({:id 1, :lang "Clojure"} {:id 2, :lang "Java"})
clj-id => 1
:insert-rel => ({:id 1} {:id 2} {:id 3})
java-id => 2
:insert-rel => ({:id 4} {:id 5} {:id 6} {:id 7})
(jdbc/query db ["select langs.lang, releases.desc\n       from langs inner join releases\n       on (langs.id = releases.langId)\n       where lang = 'Clojure' "]) =>
({:lang "Clojure", :desc "ancients"}
 {:lang "Clojure", :desc "1.8"}
 {:lang "Clojure", :desc "1.9"})

This doesn't seem to fix my problem. – Freid001 Apr 17 '18 at 14:53 — Freid001, Apr 17 '18 at 14:53

score 0 · Accepted Answer · answered Apr 18 '18 at 14:33

0

Think, I was passing the bar.col field in the wrong place. I guess because this is a has-many relationship, sql korma has to go away and run n+1 queries here. This explains why I see a missing FROM-clause exception, because I was telling sql korma to fetch a field in the wrong query.

(defn fetch []
  (->
    (korma/select* foo)
    (korma/fields :token)
    (korma/with bar (korma/fields :col))
    (korma/exec)))

answered Apr 18 '18 at 14:33

Freid001

2,580
3
29
60

you shoud give Walkable a try. It's a new sql library for Clojure using a very expressive query language https://github.com/walkable-server/walkable – myguidingstar Apr 18 '18 at 20:06

Missing FROM-clause entry for table?

2 Answers2