How to build my sql query by queryBuilder in Yii2?-CodePudding

My solution:

$author_id = 25;
$sql = "SELECT DISTINCT author_id AS label
          FROM users
         WHERE author_id ::text LIKE '%:term%' AND role = 'Editor'";
$query = Users::findbysql($sql, [ ':term' => $author_id ] )->all();

The problem is the label alias, which then fails with: author_id ::text LIKE '%:term%'

CodePudding user response：

try manage the param for like string using concat

    $author_id = 25;
    $sql = "SELECT DISTINCT author_id AS label
              FROM users
             WHERE author_id ::text LIKE concat('%', :term,'%') AND role = 'Editor'";
    $query = Users::findbysql($sql, [ ':term' => $author_id ] )->all();

CodePudding user response：

SQL concat() concatenates strings.

You should use ':term' to provide a string.