Multiple joins, with optional second join

SQL++
1

Hello,

I have three types of documents: Companies, AvailableCompanies and UserCompany. The AvailableCompanies document contains an array of objects with the following structure:

{
  "companies": [
    {
      "companyId": "425a9795-9606-47aa-a052-b06e38eba853",
      "userCompanyId": "0095bb52-ca34-4a6e-a225-39e52a2133bb"
    },
    {
      "companyId": "028484dd-8719-4456-9ded-962ccddc9589"
    }
  ]
}

Each object shows if the user is able to have a relationship with a company, and if he already has one, it specifies the id from that document (userCompanyId).

Knowing this, I’d like to have a query that fetches a list of companies, with some information of the userCompany if it’s available. I’ve come up with this so far:

SELECT companies.name, userCompanies.registerDate
FROM default availableCompanies USE KEYS "AvailableCompanies::b8871a0e-f1c4-4832-98fd-72bb68fbe283"
JOIN default companies ON KEYS ARRAY "Company::" || company.companyId FOR company IN availableCompanies.companies END
LEFT JOIN default userCompany ON KEYS ARRAY "UserCompany::" || company.userCompanyId FOR company IN availableCompanies.companies END

Unfortunately, when the userCompanyId is missing, there’s still information being shown, from the first element of the array (in this example). I’d like the result to be something like:

[
    {
        "company": "Company1",
        "registerDate": "x"   
    },
    {
        "company": "Company2"
    }
]

Is it possible to achieve this? Thanks!

2

Hi @manusyone,

Can you try this.

SELECT companies.name, userCompany.registerDate
FROM default availableCompanies USE KEYS "AvailableCompanies::b8871a0e-f1c4-4832-98fd-72bb68fbe283"
JOIN default companies ON KEYS ARRAY "Company::" || company.companyId FOR company IN availableCompanies.companies WHEN company.companyId IS NOT MISSING END
LEFT JOIN default userCompany ON KEYS ARRAY "UserCompany::" || company.userCompanyId FOR company IN availableCompanies.companies WHEN company.userCompanyId IS NOT MISSING END;
3

Hey @vsr1! Thank you for the quick reply. I’ve tried that, but the result is the same, there’s still a registerDate being outputted for the second object of the companies array.

EDIT: We’re currently using Couchbase Community Edition 4.5, let me know if you need the the EXPLAIN result

4

@manusyone, I thought you want that, that is why you are using LEFT JOIN. Remove LEFT keyword and try it.

If not post sample documents and expected results.

5

Is this what you are expecting.

 SELECT companies.name, userCompany.registerDate
    FROM default availableCompanies USE KEYS "AvailableCompanies::b8871a0e-f1c4-4832-98fd-72bb68fbe283"
    JOIN default companies ON KEYS ARRAY "Company::" || company.companyId FOR company IN availableCompanies.companies WHEN company.companyId IS NOT MISSING END
    LEFT JOIN default userCompany ON KEYS ARRAY "UserCompany::" || company.userCompanyId FOR company IN availableCompanies.companies WHEN (META(companies).id == "Company::" || company.companyId ) AND company.userCompanyId IS NOT MISSING END;
6

With that query it still removes the second company from the output. I am replying now with some sample documents

7

The result is the same with the JOIN keyword. Here are some sample documents:

AvailableCompanies

key: AvailableCompany::b8871a0e-f1c4-4832-98fd-72bb68fbe283
 {
  "companies": [
    {
      "companyId": "425a9795-9606-47aa-a052-b06e38eba853",
      "userCompanyId": "0095bb52-ca34-4a6e-a225-39e52a2133bb"
    },
    {
      "companyId": "028484dd-8719-4456-9ded-962ccddc9589"
    }
  ]
}

Company

key: Company::028484dd-8719-4456-9ded-962ccddc9589

{
    "docId": "028484dd-8719-4456-9ded-962ccddc9589",
    "name": "Company1"
}

key: Company::425a9795-9606-47aa-a052-b06e38eba853
{
    "docId": "425a9795-9606-47aa-a052-b06e38eba853",
    "name": "Company2"
}

UserCompany

key:  UserCompany::0095bb52-ca34-4a6e-a225-39e52a2133bb
{
    "companyId": "425a9795-9606-47aa-a052-b06e38eba853",
    "registerDate": "2017-03-22T23:12:21Z",
    "docId": "0095bb52-ca34-4a6e-a225-39e52a2133bb"
  }

Current output:

[
  {
    "name": "Company1",
    "registerDate": "2017-03-22T23:12:21Z"
  },
  {
    "name": "Company2",
    "registerDate": "2017-03-22T23:12:21Z"
  }
]

Expected output:

[
  {
    "name": "Company1",
    "registerDate": "2017-03-22T23:12:21Z"
  },
  {
    "name": "Company2"
  }
]

So the company always exist, but the userCompany info may not be present. Regardless of that, I always want to show the company information, hence the 'LEFT JOIN`

8

Did you try my latest query. it should have worked

1 Like
9

I had remove the LEFT part of the query, with the LEFT it does work like a charm.

Could you explain better this part of the query?

(META(companies).id == "Company::" || company.companyId )

It seems like it is enforcing the join only when the companyId is the same as the one being processed in the userCompany join , is that correct?

10

As userCompanyId taking from the availableCompanies while going through ARRAY construct it gets from other companies but you want matching company only so we are adding additional check.

The following gives right results.

INSERT INTO default VALUES("AvailableCompanies::b8871a0e-f1c4-4832-98fd-72bb68fbe283", { "companies": [ { "companyId": "425a9795-9606-47aa-a052-b06e38eba853", "userCompanyId": "0095bb52-ca34-4a6e-a225-39e52a2133bb" }, { "companyId": "028484dd-8719-4456-9ded-962ccddc9589" } ] });
INSERT INTO default VALUES("Company::028484dd-8719-4456-9ded-962ccddc9589", { "docId": "028484dd-8719-4456-9ded-962ccddc9589", "name": "Company1" });
INSERT INTO default VALUES("Company::425a9795-9606-47aa-a052-b06e38eba853", { "docId": "425a9795-9606-47aa-a052-b06e38eba853", "name": "Company2" });
INSERT INTO default VALUES("UserCompany::0095bb52-ca34-4a6e-a225-39e52a2133bb",{ "companyId": "425a9795-9606-47aa-a052-b06e38eba853", "registerDate": "2017-03-22T23:12:21Z", "docId": "0095bb52-ca34-4a6e-a225-39e52a2133bb" });
SELECT companies.name, userCompany.registerDate
     FROM default availableCompanies USE KEYS "AvailableCompanies::b8871a0e-f1c4-4832-98fd-72bb68fbe283"
     JOIN default companies ON KEYS ARRAY "Company::" || company.companyId FOR company IN availableCompanies.companies WHEN company.companyId IS NOT MISSING END
     LEFT JOIN default userCompany ON KEYS ARRAY "UserCompany::" || company.userCompanyId FOR company IN availableCompanies.companies WHEN (META(companies).id == "Company::" || company.companyId ) AND company.userCompanyId IS NOT MISSING END;
2 Likes
11

Perfect, now I understand it. Cheers!