Friends of Cake Search: IN clause in callback

Need Help
,
1

I’m using Friends of Cake Search in my project, and I have a specific situation that requires an IN subquery in a WHERE clause. I thought the best way to handle this was with a callback in my search collection. The search field is configured like this:

<?php echo $this->Form->control('related_doc', [
    'label' => 'Related Report', 
    'options' => $related_doc, 
    'empty' => [
        '' => ''
    ]
]) ?>

The value of related_doc comes from a dropdown in my search engine. and it’s the ID of a record in a table called ml_relations. All I need to do is get the chosen value of related_doc into my callback (see ‘WHAT GOES HERE??’ below):

$this->add('related_doc', 'Search.Callback', [
    'callback' => function (\Cake\ORM\Query $query, array $args, \Search\Model\Filter\Base $filter) {
        // Return all mission lessons with the selected related report)
        $query->where("MissionLessons.id IN(SELECT ml_relations.ml_id 
            FROM ml_relations 
            WHERE ml_relations.related_noncon_id = WHAT GOES HERE??
            AND ml_relations.deleted_record = 0)");
    }
]);

I tested this out by hardcoding an ID in the IN subquery (e.g., WHERE ml_relations.related_noncon_id = 4262), and I got back the results I expected. I’m just blanking on how to make that a variable. How do I reference the actual search field related_doc in this callback?

2

Update: This works, but I don’t know if it’s the best way in Cake 4:

WHERE ml_relations.related_noncon_id = ". $args['related_doc'] ."

(This would be PDO’d in production, obviously).

Right results, but best answer?

3

You can resolve it with value

$search->value('related_doc', [
    'field' => 'MissionsRelations.relation_id',
    'beforeProcess' => function (Query $query) {
        return $query->innerJoinWith('MissionsRelations');
    },
]);

Its like a where, you just have to setup the joins in the beforeProcess

4

Thanks. I’ll give this a try.