soql multipicklist to multipicklist query using join

Question

Alright, maybe what I am trying to do is not possible, but before I rule it out, I thought I would ask (usually anything is possible if approached correctly!).

First, the goal. I am using a multipicklist as settings in one (settings) object for when I am running a class and searching against another (course) object. The picklists attached to each one are identical. I would like to receive back all of the records from the course object that are identified as the same value from it's picklist against the settings objects picklist. Pictures for clarification :)

Settings object (chosen for the semester field used in query):

Course object with exact same values(searching Semesters Offered using semester from settings object):

Now, I don't care how I get to my objective. if I have Fall selected, I would like to receive back all courses that have fall, (Fall, Fall A, Fall b) like a wildcard. However, it seems I can not use "LIKE" on a multipicklist, so that does not seem to be an option. INCLUDES is though, so I am using that for right now.

And, I do know that if I have an "INCLUDES" set up like this: INCLUDES('Fall','Fall B','Fall C') -- See full query:

for(Course__c y : [select ID, Name, Credits__c, Total_Price__c, 
Semesters_Offered__c from Course__c WHERE (Semesters_Offered__c 
INCLUDES('Fall','Fall B','Fall C')  AND Id = : pa.Course__c)]){
                     courseList.add(new cCourse(y));}

then I will get the results I want (find courses that have Fall, OR Fall A, OR Fall B).

However, I would really rather not hard code it, that is just a bad idea...

So I tried to work with a .join like this:

public string semester = [SELECT Semester__c FROM Registration_Settings__c WHERE Name = 'Registration Settings'].Semester__c;
 string[] values = semester.split(';',0);
 string semesters = string.join(values, ',');

However, this will not run in my query?:

for(Course__c y : [select ID, Name, Credits__c, Total_Price__c, 
Semesters_Offered__c from Course__c WHERE (Semesters_Offered__c 
INCLUDES(:semesters)  AND Id = : pa.Course__c)]){
                     courseList.add(new cCourse(y));}

I thought because maybe I am missing the apostrophes?

My debug log on this string looks like this:

So my join seems to doing exactly what I want (removing the AND ';' and inserting and OR ',') but why oh why is it not running, is it the apostrophes, is it something else?

So I tried to fix the apostrophe issue with something like this and it also fails, which I just can not figure out why:

    public string getSemesters(){
    if(semester=='Fall'){
            semesters='\'Fall\',\'Fall A\',\'Fall B\'';
            return semesters;}
    else
    return null;} 

it outputs:

which seems 100% correct to me, it has the right syntax for the query anyways ('Fall','Fall A','Fall B') or so I think. However, this too will also not work in the query, why?

Ultimately I would love to be able to use the .join after looking at my settings, and not have to touch the code again, any ideas?

Thank you so much in advance for looking at this, and I apologize for all of the questions within the questions. It seems like the more I learn, the less I know. Again, thank you though, I appreciate your time! Nic.

Answer 1

you can use dynamic query for that, all you need to do is generate string with query and then use Database.query() method.

Follow sample code below:

String semesters = '\'Fall\',\'Fall A\',\'Fall B\''; // You can modify this string
String query = 'SELECT ID, Name, Credits__c, Total_Price__c, Semesters_Offered__c 
FROM Course__c 
WHERE (Semesters_Offered__c INCLUDES(' + semesters + ') 
AND Id = \'' +  pa.Course__c + '\')'; 

for(Course__c y : Database.query(query)){ 
    courseList.add(new cCourse(y));
}

If you would like to use String.join() method, in your case, you would ned to do this in following way:

String semesters = '\'';
semesters += String.join(list, '\',\'');
semesters += '\'';

You can create method to reuse code:

public String returnSemesters(List<String> strings){
    String semesters = '\'';
    semesters += String.join(list, '\',\'');
    semesters += '\'';
    return semesters;
}