Is it possible to Join on multiple types (more than 2 types)

SQL++
1

I am new to this couchbase we are planning to use this for our cms project to store meta-data.
I am trying to insert below Json Data and trying to Query using JOIN on 3 different types but i am facing issues. can some one help on this…

INSERT INTO default (key, value) VALUES (“hotel_1003”, {“hotelcode” : “1003”, “type”: “hotel”, “providercode” : “144”, “booking_date_range” : “18-11-2016 to 22-11-2016”, “travel_date_range” : “18-11-2016 to 22-11-2016”, “tenantid” : “Leela”,“companyid” : “” });

INSERT INTO default (key, value) VALUES (“hotel_facilities_09”, {“id”:“09”,“hotel_id”:“hotel_1003”, “type”: “facilities”, “facilities”:[{“contenttype”:“image”, “category”:“thumbnail”, “content_url”:“http://www.ibsplc.com/logo_8.png"},{“contenttype”:"image”, “category”:“thumbnail”, “content_url”:“http://www.ibsplc.com/logo_9.png"},{“contenttype”:"image”, “category”:“thumbnail”, “content_url”:“http://www.ibsplc.com/logo_10.png”}]});

INSERT INTO default (key, value) VALUES (“hotel_mealplan_01”, {“id”:“09”,“hotel_id”:“hotel_1003”, “type”: “mealplans”, “meal_plans”:[{“contenttype”:“image”, “category”:“thumbnail”, “content_url”:“http://www.ibsplc.com/logo_8.png"},{“contenttype”:"image”, “category”:“small”, “content_url”:“http://www.ibsplc.com/logo_9.png"},{“contenttype”:"image”, “category”:“small”, “content_url”:“http://www.ibsplc.com/logo_10.png”}]});

join is working fine with 2 diff types but when i try on 3 diff types i am facing issue

working Query:

select * from default mealplans
JOIN default hotel ON KEYS mealplans.hotel_id
WHERE mealplans.hotel_id=“hotel_1003”
AND mealplans.type=“mealplans”;

Facing issue below Query:

select * from default mealplans
JOIN default hotel ON KEYS mealplans.hotel_id
JOIN default facilities ON KEYS facilities.hotel_id
WHERE mealplans.hotel_id=“hotel_1003”
AND mealplans.type=“mealplans” AND faciliti.type=“facilities”;

Please some one help on issue query, is any syntax issue in above query or my Data modeling is wrong…

2

Given your data, you would need to use an index join for the second join.

http://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/from.html#concept_rnt_zfk_np__index-join

3

Hi @geraldss

I tried below statements using index join but it given error…

CREATE INDEX mealplans_hotel_id ON default(hotel_id) WHERE type=“mealplans”;

cbq.exe> select * from default hotel

JOIN default mealplans ON KEYS mealplans.hotel_id FOR hotel
WHERE mealplans.hotel_id=“hotel_1003”
AND mealplans.type=“mealplans”;
{
“requestID”: “80bffcd0-9e91-4372-ad9e-49c201bf6969”,
“errors”: [
{
“code”: 3000,
“msg”: “syntax error - at FOR”
}
],
“status”: “fatal”,
“metrics”: {
“elapsedTime”: “1ms”,
“executionTime”: “1ms”,
“resultCount”: 0,
“resultSize”: 0,
“errorCount”: 1
}
}

4

Try this N1QL

select * from default hotel
JOIN default mealplans ON KEY mealplans.hotel_id FOR hotel
WHERE mealplans.hotel_id="hotel_1003"
AND mealplans.type="mealplans";
1 Like
5

and BTW, for JOIN ON 3 types,try this N1QL:

select * from default facilities
JOIN default hotel ON KEYS facilities.hotel_id
JOIN default mealplans ON KEY mealplans.hotel_id FOR hotel
WHERE mealplans.hotel_id="hotel_1003"
AND mealplans.type="mealplans" AND facilities.type="facilities";
1 Like
6

Hi @atom_yang ,

Thanks for your reply i tried above two queries you given but i am getting below error. I am using couchbase 4.0.0-4051 Community Edition (build-4051).

cbq.exe> select * from default facilities

JOIN default hotel ON KEYS facilities.hotel_id
JOIN default mealplans ON KEY mealplans.hotel_id FOR hotel
WHERE mealplans.hotel_id=“hotel_1003”
AND mealplans.type=“mealplans” AND facilities.type=“facilities”;
{
“requestID”: “6337ff17-5328-4827-bef7-4c25f0fe7a3e”,
“errors”: [
{
“code”: 3000,
“msg”: “syntax error - at KEY”
}
],
“status”: “fatal”,
“metrics”: {
“elapsedTime”: “1ms”,
“executionTime”: “1ms”,
“resultCount”: 0,
“resultSize”: 0,
“errorCount”: 1
}
}

7

Couchbase Server 4.5.0-2601 Enterprise Edition (build-2601) works fine.

1 Like
8

Also, please see detailed explanation of this at: https://dzone.com/articles/join-faster-with-couchbase-index-joins