Compound foreign keys are not joined correctly

[sparkofreason]

I have a table defined like this:

CREATE TABLE IF NOT EXISTS merged_pull_request_user (
  scm_repo_id INT NOT NULL,
...
  FOREIGN KEY (scm_repo_id, pull_request_number)
    REFERENCES pull_request(scm_repo_id, pull_request_number)
...

However, honeyeql is only taking one column of the compound key. The attribute joining merged_pull_request_user to pull_request is :merged-pull-request-user/pull-request-by-pull-request-number, and the generated query only joins on pull_request_number.

[tamizhvendan]

Thanks @sparkofreason, for reporting this issue.

I can reproduce it with the below schema

CREATE TABLE account (
   acc_num INTEGER,
   acc_type INTEGER,
   acc_descr CHAR(20),
   PRIMARY KEY (acc_num, acc_type));

CREATE TABLE sub_account (
   sub_acc INTEGER PRIMARY KEY,
   ref_num INTEGER NOT NULL,
   ref_type INTEGER NOT NULL,
   sub_descr CHAR(20),
   FOREIGN KEY (ref_num, ref_type) REFERENCES account);

Currently, it generates four relationship attributes

:account/ref-num-sub-accounts
:account/ref-type-sub-accounts

:sub-account/account-by-ref-num
:sub-account/account-by-ref-type

Post this fix (which I will address in a week's time), It will have two extra relationship attributes

:account/sub-accounts

:sub-account/account

Which will join based on the composite foreign key.

[tamizhvendan]

@sparkofreason I did some initial analysis today. It is a bit more complicated than I initially thought. If I am not wrong, it will take at least half a day of work. So, it won't be possible for me to work on it this week. Let me try my best to find some time to wrap this up!

[tamizhvendan]

Hi @sparkofreason,

It took way more than I expected, and thankfully, I used the holidays effectively to bring it to closure.

Here we go: https://cljdoc.org/d/com.github.tamizhvendan/honeyeql/1.0.2/doc/attributes#composite-primary-and-foreign-keys

Sample Queries: https://github.com/tamizhvendan/honeyeql/blob/1.0.2/test/suite.edn#L698-L723

Let me know your feedback with the latest version :)

[sparkofreason]

Awesome! Thank you, I will take a look.