SharePoint REST API Query is Not Valid

Question

I am trying to filter a list by multiple different columns, and then when I add the array filter towards the end, it tells me that the query is not valid. I have tried two different methods which are listed in the Verified Answer to the post below:

Reference: How to use array in rest API for getting items based on array values

I want to filter the response to only show specific users names.

Here is what I tried:

Method 1:

const filterStartDate = new Date('2022-01-01');
const formatFilterStartDate = moment(filterStartDate).format('YYYY-MM-DD');
const approvalStat = "Approved";
const teamEmps= ["Emp 1", "Emp 2", "Emp 3", "Emp 4"];

var filters = teamEmps.map(function(emp){
    return 'EmployeeName/Title eq ' + emp;
});

/items?$select=EmployeeName,ID,StartDate,EndDate,LeavePurpose,Title,Status&$expand=EmployeeName/Title&$filter=StartDate ge '" + formatFilterStartDate + "'and Status eq 'HR Approved' and '" + filters.join(' or ') + "'"

Method 2:

const filterStartDate = new Date('2022-01-01');
const formatFilterStartDate = moment(filterStartDate).format('YYYY-MM-DD');
const approvalStat = "Approved";
const teamEmps= ["Emp 1", "Emp 2", "Emp 3", "Emp 4"];

function getFilter(teamEmps) {
    var nameFilter = "EmployeeName/Title eq ";
    var filter = "";
    for (var i = 0; i < teamEmps.length; i++) {
        if (i == teamEmps.length - 1) {
            filter += nameFilter + teamEmps[i];
        } else {
            filter += nameFilter + teamEmps[i] + " or ";
        }
    }
    return filter;
}

var filterByName = getFilter(teamEmps);

/items?$select=EmployeeName/Title,ID,StartDate,EndDate,LeavePurpose,Title,Status&$expand=EmployeeName&$filter=StartDate ge '" + formatFilterStartDate + "'and Status eq 'HR Approved' and '" + filterByName + "'"

Answer 1

My guess is that it has to do with wrongly placed single quotes.

First, in that first example with the map(), after you build the filters array, you are going to end up with

["EmployeeName/Title eq Emp 1", "EmployeeName/Title eq Emp 2", etc]

where you do not have single quotes around the values you are checking for.

Then, when you build the full query, you enclose the entire output of the join() in single quotes:

// you have a single quote after that last "and", and then you use a double quote
// to jump out of the string back into javascript land where you can run the join(),
// then you use a double quote to jump back into the string where you add a final single quote
// and then use a double quote to end the string

theBeginningOfTheQuery + "' and Status eq 'HR Approved' and '" + filters.join(' or ') + "'"

// what that ends up outputtng is single quotes around the entire last section which is no good

theBeginningOfTheQuery + "' and Status eq 'HR Approved' and 'EmployeeName/Title eq Emp 1 or EmployeeName/Title eq Emp 2 or EmployeeName/Title eq Emp 3'"

I would change the map() to encode the single quotes around the filter values you are checking for like this:

var filters = teamEmps.map(function(emp){
    return "EmployeeName/Title eq '" + emp + "'";
});

and then change the section where you join() to not be enclosed in single quotes:

theBeginningOfTheQuery + "' and Status eq 'HR Approved' and " + filters.join(' or '); // just end the line here, no need to add any closing double quote