Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

How to get first column of first row from queryBuilder?

Hello!

In my case I have a helper method which returns a query builder object for some base parameters. And using it like this:

...
      // standart query
      $items = $this->getQueryBuilder($query)
        ->getQuery()
        ->execute($params);

      // getting count of items with some query modifier
      $total = $this->getQueryBuilder(array_merge($query, ['freshOnly' => false]))
        ->columns('COUNT(*)')
        ->getQuery()
        ->execute($params);
...

What the best way to get int result of COUNT(*)

UPD: Needed analogue of this: www.yiiframework.com/doc-2.0/yii-db-query.html#column()-detail

Executes the query and returns the first column of the result.

Thank you!



60.9k

i am not 100% sure i understood you correctly

(int) $total = $this->getQu.....

I need something simpler than:

$res = $this->getQueryBuilder(array_merge($query, ['freshOnly' => false]))
        ->columns('COUNT(*)')
        ->getQuery()
        ->execute($params);

foreach ($res as $row) {
  $total = $row[0]; // or so
  break;
}

Instead of execute use getSingleResult.



60.9k

in your db service try with configuring an option

"options"  => [
                    \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
                    \PDO::ATTR_PERSISTENT => true,
                    \PDO::ATTR_EMULATE_PREPARES => false,
                    \PDO::ATTR_DEFAULT_FETCH_MODE  =>  \PDO::FETCH_ASSOC,
                    \PDO::ATTR_STRINGIFY_FETCHES => false  //  <-----------------------------------------
                ]
edited Oct '16

Instead of execute use getSingleResult.

Its returns a model (\Phalcon\Mvc\ModelInterface) but in the query it is not applicable at all.

in your db service try with configuring an option

```

"options" => [ \PDO::MYSQLATTRINITCOMMAND => 'SET NAMES utf8', \PDO::ATTRPERSISTENT => true, \PDO::ATTREMULATEPREPARES => false, \PDO::ATTRDEFAULTFETCHMODE => \PDO::FETCHASSOC, \PDO::ATTRSTRINGIFYFETCHES => false // <----------------------------------------- ] ```

String is not a problem. I means int at SQL...

How can I get a value(string or int) of COUNT(*) from the query? There is no any other columns or rows, no needs to any traverse. So I wish to get it via one single method, like this one: www.yiiframework.com/doc-2.0/yii-db-query.html#column()-detail :)

Executes the query and returns the first column of the result.



99.6k
Accepted
answer
edited Oct '16

What you mean not applicable at all ?

 $total = $this->getQueryBuilder(array_merge($query, ['freshOnly' => false]))
        ->columns('COUNT(*)')
        ->getQuery()
        ->getSingleResult($params)
        ->offsetGet('0');

Im using it myself and it's fine.



60.9k

i am lost ...

What you mean not applicable at all ?

Ofcos it is possible to use some methods practically :) But there is no any data in the query result which can be applicable to any model - count of models is not a property of a(any) model, imho. So why to create an object/model at all?

but offsetGet is what I need really, thank you!

edited Oct '16

ModelInterface is just interface :) Is not model yet :) Well at least is shouldn't be.

btw, my IDE(PhpStorm) marks next as unknown:

Method 'getSingleResult' not found in \Phalcon\Mvc\Model\QueryInterface

and even

Method 'offsetGet' not found in \Phalcon\Mvc\ModelInterface

last one, after adding to stubs of 'Phalcon\Mvc\Model\QueryInterface':

  /**
   * @param array|null $bindParams
   * @param array|null $bindTypes
   * @return \Phalcon\Mvc\ModelInterface
   */
  public function getSingleResult($bindParams = null, $bindTypes = null);

It's known problem and definitely need improvment :)

Do not always trust in IDE editors. Especially with Phalcon where you don't have 1:1 mapping of classes and methods signature like the ones in memory.