Use jugglingdb to select results where an id exists in another table

Question

I'm attempting to use jugglingdb (and the mysql adapter) to grab a list of Patients. Our schema is setup so that a Patient must be a User, but a User does not have to be a Patient. This means a Patient has a userId, and the User table holds the name of the patient.

Patient
    id
    userId
User
    id
    givenName
    familyName
    middleInitial

When the initial pages were created, we simply used Patient.all to grab the results, and patient.user(... to grab the user object/username. This is fine for a small amount of records, but not for thousands of records.

How do I grab a list of Users, only if their id is referenced in the Patient table? Example mysql query:

SELECT * FROM User WHERE id IN (SELECT id FROM Patient) AND familyName LIKE '%doe%';

I've been browsing the jugglingdb-mysql adapter for a bit now and haven't found an ideal solution, otherwise, this question is the only other solution I've found so far.

Another alternative/query I could use is creating an inner join between Patient and User and then filter the results:

SELECT Patient.id
      ,User.familyName
FROM Patient 
    INNER JOIN User ON Patient.userId = User.id 
WHERE User.familyName LIKE '%doe%';

But again, I haven't found a solution to joins either using jugglingdb.

Answer 1

You can always use the include functionality as long as the relationship exists. For example:

Patient.belongsTo(User, { as: 'user', foreignKey: 'userId' });

// Empty callback for async getter.
var c = function () { };

Patient.all({ 
    where: { blah: blash }, 
    include: [ 'user' ] 
}, function (err, patients) {
        var user = patients[0].user(c);
    }
);

You can even include relationships to the relationship. For example:

Role.belongsTo(User, { as: 'role', foreignKey: 'roleId' });

Patient.all({ 
    where: { blah: blash }, 
    include: [ { user: 'role' } ]
}, function (err, patients) {
        var user     = patients[0].user(c);
        var userRole = user.role(c);
    }
);

It is important to note that sometimes the returned relational object(for example the "role" object) may be read-only. I have not dug too deeply into the reasoning behind this, so I cant give you a confident answer as to the why, how, or where this occurs, but I believe it may be within JugglingDB itself.