Doc issue with :joins

[jgallinari]

Hi,
In your example on joins :

(db/q conn '[:select todo/* person/*
:from todo
:joins person/todos])

We get the error

PSQLException ERROR: table name "todo" specified more than once org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2440)

Don't you think it should be simply

(db/q conn '[:select todo/* person/*
:joins person/todos])

?

In addition, it could be useful to have a way to get the real SQL query generated underneath. Would it be possible?

In the same vein, how do you use multiple joins?
E.g.

(def todos-users [{:db/col :person/name :db/type "text" :db/unique? true :db/nil? false}
{:db/col :item/name :db/type "text" :db/unique? true :db/nil? false}
{:db/rel :item/todos :db/type :many :db/ref :todo/item}
{:db/rel :todo/item :db/type :one :db/ref :item/id}
{:db/rel :person/todos :db/type :many :db/ref :todo/person}
{:db/rel :todo/person :db/type :one :db/ref :person/id}
{:db/col :todo/name :db/type "text"}
{:db/col :todo/done :db/type "boolean" :db/default false :db/nil? false}])

(db/migrate conn todos-users)

(db/q conn '[:select todo/* :joins person/todos item/todos])
PSQLException ERROR: table name "todo" specified more than once org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2440)

Thanks

[swlkr]

To the first question: yes it should be :joins without the :from

(db/q conn '[:select todo/* person/*
             :joins person/todos])

To the second question, yes! I pushed a version 1.2.0 that has a new lighthouse.core/sql fn that takes a connection and a vector and outputs a sql vector like so:

(require '[:lighthouse.core :as db])

(def conn (db/connect "jdbc:sqlite:test.db"))

(db/sql conn [:select todo/id]) ; => ["select todo.id as todo$id from todo"]

To the last question, unfortunately multiple joins to the same table (todo/item and todo/person) don't work currently :(

[jgallinari]

Great! Thanks for your response.