How to get row count with a query using Propel 1.6's Custom SQL?

Question

I have a project in Symfony2 using Propel ORM 1.6 and PostgreSQL. I'm trying to get the number of rows from a Query using Custom SQL as shown in this link in the following way:

$con = Propel::getConnection(VerbNounPeer::DATABASE_NAME);
$countSql = "SELECT COUNT(*) FROM ("
               ." SELECT DISTINCT ON (fk_noun_id) *"
               ." FROM verb_noun"
               ." ORDER BY fk_noun_id, verb_noun_vote_count DESC"
               .") inner_tb";
$countSqlStmt = $con->prepare($countSql);
$countSqlStmt->execute();

The above query works fine. However, how do I get the integer value of the row count from the stmt (the above $countSqlStmt) object? I tried using:

$recordsCount = $countSqlStmt[0];

However, given that $countSqlStmt is an object and not an array I get "Fatal error: Cannot use object of type DebugPDOStatement as array..."

I also tried to convert it to an array using Propel's formatter:

$countSqlFormatter = new PropelArrayFormatter();
$countSqlRow = $countSqlFormatter->format($countSqlStmt);

However, this does not work either as I need to specify a criteria for the array, and I don't know what to put since my query result is just a row with a count value and not a class. If it were a class I would use:

$formatter->setClass('VendorName\NameBundle\Model\VerbNoun');

Any ideas? In this link there is a use of PropelArrayFormatter() but it was not of much help to me...

K-Phoen K-Phoen · Accepted Answer · 2012-07-16T07:53:53

1

votes

In order to get the data out of a PDOStatement, you have to call the fetch method on it.

How to get row count with a query using Propel 1.6's Custom SQL?

1 Answers