We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Issue with getCount

    • 17499 ☆ A M B ☆
    • 872 Posts
    lossendae Reply #1, 16 years, 7 months ago
    Hello,

    I was wondered how to use getCount properly.
    Whenever i use a groupBy or select(DISTINCT... statement, getCount always return a wrong value.

    Is there any proper way to use it with those properties?
      • 22303 MODX Staff
      • 10,725 Posts
      opengeek Reply #2, 16 years, 7 months ago
      Quote from: lossendae at Mar 15, 2010, 04:49 AM

      I was wondered how to use getCount properly.
      Whenever i use a groupBy or select(DISTINCT... statement, getCount always return a wrong value.

      Is there any proper way to use it with those properties?
      If you are specifying the select(), don’t use getCount(), just run the query and get the results normally. getCount() is a shortcut that replaces your select() with COUNT(DISTINCT primaryKeyField) automatically, based on the primary key definition of the class you specify. Group by should work, as long as it makes sense with the COUNT(DISTINCT primaryKeyField) select clause.
        Jason Coward
        Chief Architect @ MODX
        http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek
        • 17499 ☆ A M B ☆
        • 872 Posts
        lossendae Reply #3, 16 years, 7 months ago
        That’s what i was doing. Using $i++ to count the number of rows.

        Just wanted to make sure that there was no other more elegant method to do so.

        Thanks OpenGeek.
          • 17499 ☆ A M B ☆
          • 872 Posts
          lossendae Reply #4, 16 years, 7 months ago
          Ok, i’m talking nonsense.

          My above answer was so out of line and i DO have a problem with getCount or equivalent.

          I have a table with the following columns: id, model, failures, updatedon, whatever, whatever2.

          Here is my xPDO:

          <?php
$c = $modx->newQuery($table);
$c->select('id, model, SUM(failures) as failures, updatedon');
$c->groupBy('model');
$c->limit($limit, $start);
$c->sortby($sort,$dir);


          Where should i put a COUNT statement to have the total result of the query?

            • 28215
            • 4,149 Posts
            splittingred Reply #5, 16 years, 7 months ago
            Quote from: lossendae at Mar 15, 2010, 09:13 AM

            <?php
$c = $modx->newQuery($table);
$c->select('id, model, SUM(failures) as failures, updatedon');
$c->groupBy('model');
$c->limit($limit, $start);
$c->sortby($sort,$dir);


            Where should i put a COUNT statement to have the total result of the query?


            <?php
$c = $modx->newQuery($table);
$c->groupBy('model');
$count = $modx->getCount($table,$c);
$c->select('id, model, SUM(failures) as failures, updatedon');
$c->limit($limit, $start);
$c->sortby($sort,$dir);
$collection = $modx->getCollection($table,$c);

              shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com
              • 17499 ☆ A M B ☆
              • 872 Posts
              lossendae Reply #6, 16 years, 7 months ago
              I tested this combination, but $count return 4 instead of 575.

              Is there any way to debug xPDO to see where the problem might be? Or maybe it’s a PDO issue with Wamp?
                • 28215
                • 4,149 Posts
                splittingred Reply #7, 16 years, 7 months ago
                Quote from: lossendae at Mar 15, 2010, 09:37 AM

                I tested this combination, but $count return 4 instead of 575.
                Is there any way to debug xPDO to see where the problem might be? Or maybe it’s a PDO issue with Wamp?

                Maybe. What’s your table definition? Hard to say without knowing what the table looks like. Might be related to the groupby.
                  shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com
                  • 17499 ☆ A M B ☆
                  • 872 Posts
                  lossendae Reply #8, 16 years, 7 months ago
                  Here is my table definition:

                  <?php
<object class="Model" table="model" extends="xPDOSimpleObject">
	<field key="model" dbtype="varchar" precision="50" phptype="string" null="false" index="model"/>
	<field key="description" dbtype="varchar" precision="200" phptype="string" null="false"/>
	<field key="qty" dbtype="int" precision="11" attributes="unsigned" phptype="integer" null="false" default="0"/>
	<field key="failures" dbtype="int" precision="11" attributes="unsigned" phptype="integer" null="false" default="0" index="failures"/>
	<field key="updatedon" dbtype="datetime" phptype="datetime" null="true"/>
	<field key="updatedby" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0"/>
</object>
                    • 28215
                    • 4,149 Posts
                    splittingred Reply #9, 16 years, 7 months ago
                    Can you run the query I posted above, and then add this after the getCount() ?

                    echo $c->toSql();


                    Post that here.
                      shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com
                      • 17499 ☆ A M B ☆
                      • 872 Posts
                      lossendae Reply #10, 16 years, 7 months ago
                      Sure:

                      SELECT `Model`.`id` AS `Model_id`, 
`Model`.`model` AS `Model_model`, 
`Model`.`description` AS `Model_description`, 
`Model`.`qty` AS `Model_qty`, 
`Model`.`failures` AS `Model_failures`, 
`Model`.`updatedon` AS `Model_updatedon`, 
`Model`.`updatedby` AS `Model_updatedby` 
FROM `test_model` AS `Model` 
GROUP BY model


                      The query works if i use it in mySQLyog