CODEWITHSUNDEEP
c#joincouchbase-lite
Chase Florell
Chase Florell

Reputation: 47367

How to create complex Join Query for Couchbase Lite

I'm trying to come up with an appropriate join query to join the following documents together (contrived but useful), but I don't even know where to start.

Looking at the official documentation is coming up short because it appears to be obsolete (IFunction doesn't even exist).

The couchbase mobile documentation is a little more current, but I'm still struggling at making heads or tails of it. It doesn't translate 1 to 1 with the api.

The documents I'm trying to join are akin to this

    /*school.json*/
    {
        "Name": "Harvard",
        "Teachers": [
            {
                "HireDate": "1-1-2020",
                "SchoolId": "qwerty",
                "TeacherDocumentId": "Teacher::1234"
            },
            {
                "HireDate": "1-1-2019",
                "SchoolId": "zxcvb",
                "TeacherDocumentId": "Teacher::1235"
            }
        ]
    }

    /*teacher.json*/
    {
        "Id":"Teacher::1234",
        "Name": "Peggy Sue",
        "TeacherDetailDocumentId":"TeacherDetail::9876"
    }

    /*teacher.json*/
    {
        "Id":"Teacher::1235",
        "Name": "William Jackson",
        "TeacherDetailDocumentId":"TeacherDetail::9875"
    }

    /*teacherdetail.json*/
    {
        "Id":"TeacherDetail::9876",
        "CourseLoad":1
    }

    /*teacherdetail.json*/
    {
        "Id":"TeacherDetail::9875",
        "CourseLoad":3
    }

And the goal is to project a complex C# object

public class School
{
    public string Name {get;set;}
    public IEnumerable<SchoolTeacher> Teachers {get;set;}
}

public class SchoolTeacher
{
    public DateTime HireDate { get; set;}
    public string SchoolId { get; set;}
    public string TeacherDocumentId { get; set; }
    public Teacher Teacher { get; set; }
}

public class Teacher
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string TeacherDetailDocumentId { get; set; }
    public TeacherDetail TeacherDetail { get; set; }
}

public class TeacherDetail
{ 
    public string Id { get; set; }
    public int CourseLoad { get; set; }

}

Which would be akin to the following complex json

{
    "Name": "Harvard",
    "Teachers": [
        {
            "HireDate": "1-1-2020",
            "SchoolId": "qwerty",
            "TeacherDocumentId": "Teacher::1234",
            "Teacher": {
                "Id": "Teacher::1234",
                "Name": "Peggy Sue",
                "TeacherDetailDocumentId": "TeacherDetail::9876",
                "TeacherDetail": {
                    "Id": "TeacherDetail::9876",
                    "CourseLoad": 1
                }
            }
        },
        {
            "HireDate": "1-1-2019",
            "SchoolId": "zxcvb",
            "TeacherDocumentId": "Teacher::1235",
            "Teacher": {
                "Id": "Teacher::1235",
                "Name": "William Jackson",
                "TeacherDetailDocumentId": "TeacherDetail::9875",
                "TeacherDetail": {
                    "Id": "TeacherDetail::9875",
                    "CourseLoad": 3
                }
            }
        }
    ]
}

Upvotes: 1

Views: 174

Answers (1)

Pasin
Pasin

Reputation: 86

As with the current Couchbase Lite Query API, you will need to have more than one queries to get the information of each teacher in the school's teacher array. If you don't need to build the entire JSON at once, which could lead to performance and memory issue depending on the number of teachers, you could break the query down into levels and query them as needed while specific data is being accessed.

For example, you can first query a list of SchoolTeacher without the detail when the School.Teachers property is accessed. When the SchoolTeacher.teacher is accessed, you can query again to get the teacher's detail.

Upvotes: 4

Related Questions