[FIXED] CakePHP 3 - using IN...SELECT in a custom finder

Issue

I have written a custom finder method as follows:

public function findSubstanceListNotifications(Query $query, array $options)
{
    $date_start = $options['date_start'];
    $date_end = $options['date_end'];
    $list_id = $options['list_id'];

    $list_substances = TableRegistry::getTableLocator()->get('TblListsSubstances')->find()
            ->select('substance_id')->where(['list_id IN' => $list_id])->enableHydration(false);

    if ($list_substances->isEmpty()) {
        return false;
    }

    $query = $this->find()
            ->select(['RevisionSubstances.date', 'RevisionSubstances.comment', 'RevisionSubstances.id', 'Substances.name', 'Substances.app_id'])
            ->where(['substance_id IN' => array_column($list_substances->toArray(), 'substance_id')]);

    $query->where(['RevisionSubstances.date >=' => $date_start, 'RevisionSubstances.date <=' => $date_end]);

    $query->contain('Substances');
    $query->order(['RevisionSubstances.date' => 'DESC', 'Substances.app_id' => 'ASC']);

    $query->enableHydration(false);

    if ($query->isEmpty()) {
        return false;
    }

    return $query;
}

I'm getting a PHP Fatal Error due to hitting a memory limit when executing this.

The reason, I suspect, is because the substance_id IN condition is using an array which has several thousand keys.

Is there an alterantive way to writing this where instead of using IN followed by an array, I could write the SELECT statement at this point? Similar to a subquery in MySQL.

The SQL for the condition is described by $list_substances but essentially is:

SELECT substance_id FROM tbl_lists_substances WHERE list_id IN(1,2,3,4);

The IN() condition above uses ID's pertaining to the logged-in user. However, this array would - at most - have under 20 keys so I don't think there will be any similar memory issues here. But I need to know how to access these at the point of running the subquery too.

CakePHP version is 3.7

I've read about using newExpr() in the docs but can't see how to inject the SELECT statement at this point in a way that would work.

Solution

Several thousand keys could easily be too much. I'm not sure what your concerns with a subquery are, but it should work just fine, simply pass $list_substances as the condition, like:

->where(['substance_id IN' => $list_substances])

Answered By - ndm