Zend SELECT->WHERE using LIKE on each key of Array

Question

I'm not sure how to put this in English, so here it is in SQL:

SELECT "track_sub_genre".* FROM "track_sub_genre" 
WHERE "track_sub_genre"."name" LIKE '%rock punk%' 
OR ("track_sub_genre"."name" LIKE '%rock%' AND "track_sub_genre"."name" LIKE '%punk%')

The SQL above is generated by this code, however there is a problem I'll explain bellow.

$keywords = 'rock punk';
$key_arr = explode( ' ' , $keywords );

if( count($key_arr) > 1 ) {

    $select->where(new \Zend\Db\Sql\Predicate\Like('track_sub_genre.name', '%'.$keywords.'%'));
    $select->where(
        new \Zend\Db\Sql\Predicate\PredicateSet(
            array(
                new \Zend\Db\Sql\Predicate\Like('track_sub_genre.name', '%'.$key_arr[0].'%'),
                new \Zend\Db\Sql\Predicate\Like('track_sub_genre.name', '%'.$key_arr[1].'%'),
            ),
            \Zend\Db\Sql\Predicate\PredicateSet::OP_AND
        ),
        \Zend\Db\Sql\Predicate\PredicateSet::OP_OR
    );

} else {
    // Do not worry about this case
}

The problem is, of course, that I'm not going to know how many words I'll have in $key_arr, therefore I need a dynamic code which would generate this SQL.

I'm really curious how this is done. Thanks in advance.

Answer 1

if I understand you: Make array of needed objects, and pass this array into query

$key_arr = explode( ' ' , $keywords );
$insert_arr = [];
foreach ($key_arr as $value) {

    $insert_arr[] =  new \Zend\Db\Sql\Predicate\Like('track_sub_genre.name', '%'.$value.'%') 
}

and in the query

 $select->where(
        new \Zend\Db\Sql\Predicate\PredicateSet(
             $insert_arr,
            \Zend\Db\Sql\Predicate\PredicateSet::OP_AND
        ),
        \Zend\Db\Sql\Predicate\PredicateSet::OP_OR
    );