Question 114220 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to build my sql query by queryBuilder in Yii2?

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%'

Tags:

3

Answers


  1. 0

    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();

    And if you have not a field label in your model then in you Users Class add pubblic var $label

        class Users extends  yiidbActiveRecord
    {

        public $lable;

        /**
         * @inheritdoc
         */
        public function rules()
        {
            return [
            ....
    Login or Signup to reply.
  2. 0

    SQL concat() concatenates strings.

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

    Login or Signup to reply.
  3. 0

    So, if you want to use a query builder, it should be like:

    $author_id = 25;
$users = Users::find()
    ->select(['label' => 'author_id'])
    ->distinct()
    ->andWhere(['ilike', new Expression('"author_id"::text'), $author_id])
    ->andWhere(['role' => 'Editor'])
    ->all();

    And note the @ScaisEdge answer about adding label property to the Users ActiveRecord class. All table column values are available through magic methods due to schema inspecting. Key names that are not presented in schema but declared in query result, you should define as public properties or private/protected with set<KeyName>() methods in the target ActiveRecord class.

    The other way is to use the array result of the query without creating objects. It reaches by adding ->asArray() to the calls chain before the ->all() method execution.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search