We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

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!



85.5k

i am not 100% sure i understood you correctly 

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


4.9k

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;
}


145.0k

Instead of execute use getSingleResult.



85.5k

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  //  <-----------------------------------------
                ]


4.9k
in reply to Wojciech Ślawski
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.



4.9k
in reply to Izo

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  //  <-----------------------------------------
               ]

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



4.9k

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.



145.0k
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.



85.5k

i am lost ...



4.9k
in reply to Wojciech Ślawski

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?



4.9k

but offsetGet is what I need really, thank you!



145.0k
edited Oct '16

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



4.9k

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


145.0k

It's known problem and definitely need improvment :)



79.0k

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.