CODEWITHSUNDEEP
coldfusioncfwheels
user1430046
user1430046

Reputation:

CFWheels Query Using In

I have a simple array of values (abbreviations) and I need to loop over them and check for a foreign key in another db table. In MySQL I'd use the IN clause but with CFWheels and MS SQL I am not sure how to go about it.

I first query the users table and get a list of groups that the user is a member of. This info (not my doing) is stored as a comma-separated list in the users.groups column. I then need to iterate over that list and get the title of each group from the groups.title column. I'm thinking it should be similar to the below code but it errors out, any ideas?

Also, how could this be done as one query, using "include" i.e. a join in CFWheels?

user = model("user").findAll(
    select="groups",
    where="userid = '#params.userid#'"
);

// remove trailing comma from list
groups = left(user.groups,len(user.groups)-1);

groups = listToArray(groups);

group_titles = model("groups").findAll(
    select="title",
    where="abbr IN (#groups#)"
);

Upvotes: 0

Views: 489

Answers (1)

Chris Peters
Chris Peters

Reputation: 18090

Instead of converting the list to an array, you need to encase each value within the list in single quotes.

user = model("user").findAll(
  select="groups",
  where="userid='#params.userid#'"
);

group_titles = model("groups").findAll(
  select="title",
  where="abbr IN (#ListQualify(groups, "'")#)"
);

In CFWheels ORM queries, you need to surround strings with single quotes or leave the quotes out if you're passing in a number or boolean.

Upvotes: 1

Related Questions