Parent Child relation within same table failing

[sveri]

I am trying to model a parent child relationship with one table. This is my code:

CREATE TABLE topic (
id INTEGER CONSTRAINT PK_TOPIC PRIMARY KEY AUTOINCREMENT NOT NULL,
parent_topic_id VARCHAR(43),
name VARCHAR(255) UNIQUE NOT NULL,
uuid VARCHAR(43) NOT NULL,
FOREIGN KEY (parent_topic_id) REFERENCES topic(uuid))

(defentity topic
           (belongs-to topic {:fk :uuid}))

(defn get-all-topics [] (select topic (with topic)))

throws this error:

Executing get-all-topics throws this exception:
SQLException [SQLITE_ERROR] SQL error or missing database (ambiguous column name:         main.topic.id)  org.sqlite.core.DB.newSQLException (DB.java:890)

Is this even supported by korma? If so, how to do it right?

[immoh]

It is not really supported. As you can see, the query generates sql that joins topic with topic without aliasing, hence the error message:

(sql-only (select topic (with topic)))
=> "SELECT \"topic\".*, \"topic\".* FROM \"topic\" LEFT JOIN \"topic\" ON \"topic\".\"id\" = \"topic\".\"uuid\""

Other problem is that using entities the foreign key is always joined to primary key which doesn't appear to be correct according to the table definition.

I's suggest constructing the query without using entities:

(sql-only (select [:topic :parent] (join [:topic :child] (= :child.parent_topic_id :parent.uuid))))
=> "SELECT * FROM \"topic\" AS \"parent\" LEFT JOIN \"topic\" AS \"child\" ON \"child\".\"parent_topic_id\" = \"parent\".\"uuid\""

[sveri]

Ok, thanks for the clarification. Is it ok to leave this open as a feature request?

[immoh]

Sure, let's leave this open.