I hit a funny issue today with GlideRecord join queries. The code I was writing was similar to this:

var gr = new GlideRecord('problem');
var grSQ = gr.addJoinQuery('incident');
gr.addQuery('active', 'false');
grSQ.addCondition('active', 'true');
grSQ.addCondition('assigned_to', gs.getUserID())
  .addOrCondition('assigned_to.manager', gs.getUserID());
gr.query();
 
while (gr.next()) {
    gs.info(gr.getValue('number'));
}

This won’t work. It returns a set of records completely unlike what you would expect. So I tried a different tack, and it worked great but felt like more work than should be necessary:

var user = gs.getUserID();
var downline = [];
var downline_gr = new GlideRecord('sys_user');
downline_gr.addQuery('sys_id', gs.getUserID())
     .addOrCondition('manager', gs.getUserID());
downline_gr.query();
while (downline_gr.next()) {
	downline.push(downline_gr.getValue('sys_id'));
}

var gr = new GlideRecord('problem');
var grSQ = gr.addJoinQuery('incident');
gr.addQuery('active', 'false');
grSQ.addCondition('active', 'true');
grSQ.addCondition('assigned_to', 'IN', downline.join(','));
gr.query();
 
while (gr.next()) {
    gs.info(gr.getValue('number'));
}

But then through a series of unlikely events I realized that the first code will work just fine if you change the order of your addCondition() calls against the join table:

var gr = new GlideRecord('problem');
var grSQ = gr.addJoinQuery('incident');
gr.addQuery('active', 'false');
grSQ.addCondition('assigned_to', gs.getUserID())
  .addOrCondition('assigned_to.manager', gs.getUserID());
grSQ.addCondition('active', 'true');
gr.query();
 
while (gr.next()) {
    gs.info(gr.getValue('number'));
}

I’m still scratching my head over this one, but at least my code works now.