Possible to use SQLSelect result with ORM filterAny

Questions
1

Silverstripe Version: 4.5

Question:

I have an odd query that needs to keep duplicates from joins. ->distinct(false) leaves the duplicates but I can’t seem to set the select fields for the joins. ->setQueriedColumns either isn’t working or doesn’t do what I thought.

So, I’m using SQLSelect to build the initial query.

I need to run some further queries on the result that I’d like to use filter and filterAny on.

How can I combine an SQLSelect query with DataList filters.

I tried this:


$stage = Versioned::get_stage();        
$baseTable = EventPage::getSchema()->tableName(EventPage::class);
$tableName = $stage === Versioned::LIVE ? $baseTable.'_Live' : $baseTable;
        
$sqlQuery = new SQLSelect();
$sqlQuery->setFrom($tableName);
$sqlQuery->setSelect($tableName.'.*, EventDates.StartDate, EventDates.EndDate, EventRegion.Title AS RegionTitle');
$sqlQuery->addLeftJoin('EventDates', '"EventDates"."EventID" = "'.$tableName.'"."ID"');
$sqlQuery->addLeftJoin('EventLocation', '"EventLocation"."DateID" = "EventDates"."ID"');
$sqlQuery->addLeftJoin('EventRegion', '"EventRegion"."ID" = "EventLocation"."RegionID"');
$sqlQuery->addWhere(['visible = ?' => 'yes']);
$sqlQuery->addOrderBy('StartDate ASC');

if ($result = $sqlQuery->execute())
{
    $list = DataList::create(EventPage::class);

    while ($record = $result->next())
    {
        $list->add(EventPage::create($record));
    }
        
    $pages = $list;   
}

if ($Audiences = $request->getVar('Audience'))
{
    $pages = $pages->filterAny(['Audiences.ID' => $Audiences]);
}

That appears to kind of work. It looses the duplicates though.
I’m assuming it’s actually ignoring the SQLSelect part altogether and running a new query from the filterAny