SQL Select "WHERE IN"

Questions
1

Silverstripe Version: 4*

We have addWhere, addWhereAny. Is there an option like “addWhereIn”?

        $sqlQuery = new SQLSelect();
        $sqlQuery->setFrom('Member');
        $sqlQuery->setSelect('FirstName', 'Surname', 'Email');
        $sqlQuery->addInnerJoin('Group_members','"Member"."ID" = "Group_members"."MemberID"');
// What would be the correct way to add the condition "WHERE IN"        
         $sqlQuery->addWhereAny(
            array('"Group_members"."GroupID" = ?' => 2),
            array('"Group_members"."GroupID" = ?' => 3),
            array('"Group_members"."GroupID" = ?' => 7)
        );

Does Silverstripe support this?

2

Is there a reason you’re not using the ORM? Assuming you have some relations set up on your objects, it should be a fairly simple query, something along the lines of:

Member::get()->filter(['Group_members.GroupID' => [2,3,7])

4

Hi,
Thank you for this idea. I have some relations set for a new form (newsletter) and I’m not well versed in Silverstripes ORM. I know SQL much better and it is simply a question of time spent when searching for the Silverstripe way :slight_smile: In most frameworks I worked, there was an option within the “DB SQL”. I see the next reply is the one I was searching for. :slight_smile:

3

To answer your specific question, you can pass an array of data to most of these queries, so you could use something like:

->addWhere('"Group_members"."GroupID"' => [2,3,7])

That’s all completely untested, but you should get the idea… have a look here for a lot of examples of what you can do: SilverStripe\ORM\Queries\SQLSelect | SilverStripe API

1 Like