MODX Staff
SELECT DISTINCT(`Model`.`id`) FROM `test_model` AS `Model` GROUP BY model
☆ A M B ☆
* Model : REF1
* Model : REF2
Count Model (Should be 2) : 3
MODX Staff
Backup, was the query I posted the expected query you want to run to get the count? Cause that is what is being executed. If you run that query, what is the result? I don’t think that will give you the count you want based on the query you are building.
No the query was good, the result count was false.
☆ A M B ☆
Backup, was the query I posted the expected query you want to run to get the count? Cause that is what is being executed.
If you run that query, what is the result?
I don’t think that will give you the count you want based on the query you are building.
I don’t think you are understanding how xPDO::getCount() should be used. You cannot return other columns or anything else; it is designed to return the DISTINCT primarykey values in the criteria you pass and that is all. If you need to get more complex query counts, you’ll need to handle that manually rather than with xPDO::getCount().
SELECT id, model, COUNT(DISTINCT serial)) FROM my table GROUP BY model
MODX Staff
No, nothing like mysql_num_rows(), which wouldn’t be appropriate here anway, since you are trying to set limit and offset. xPDO::getCount() will not give you the total number of rows, it gives you SELECT COUNT(DISTINCT primaryKey), i.e. it’s developed to give you the distinct number of objects of the class you specify returned by your criteria.
You’re right, I don’t understand how getCount work.
I thought it was an equivalent to the old mysql_num_rows.
I don’t want to have any other column result, i want the total number of rows regarding my query.
I use SELECT statement like this one:
SELECT id, model, COUNT(DISTINCT serial)) FROM my table GROUP BY model
What can i use to have the total number of rows found?
Note that i don’t really need the id column here, but i don’t know how to make a working query without using it (with xPDO).
☆ A M B ☆
No, nothing like mysql_num_rows(), which wouldn’t be appropriate here anway, since you are trying to set limit and offset.
<?php $qry = 'SELECT id, model, COUNT DISTINCT(serie) FROM mytable GROUP BY model'; $count = mysql_num_rows($qry); $sql = $qry.'LIMIT 0,10'
xPDO::getCount() will not give you the total number of rows, it gives you SELECT COUNT(DISTINCT primaryKey), i.e. it’s developed to give you the distinct number of objects of the class you specify returned by your criteria.
Just execute the SQL you want as a regular query and get the results of all the columns you are returning to accomplish what you are wanting to do.
<?php
$c = $modx->newQuery('Model');
$c->groupby('model');
//$countmodel = $modx->getCount('Model', $c);
$countmodel = count($modx->getCollection('Model', $c));
$c->select('id, model, code, SUM(qty) AS qty');
$models = $modx->getCollection('Model', $c);
MODX Staff
<?php
$limit=10;
$start=1;
$where='brand="BRAND1"';
$map='Data';
$c = $modx->newQuery($map);
$c->select('id');
$c->where($where);
$count = count($modx->getCollection($map, $c));
$c = $modx->newQuery($map);
$c->select('*');
$c->where($where);
$c->limit($limit, $start);
$c->toSql();
$models = $modx->getCollection($map, $c);
No. See:
when I need a totalcount for pagination for example is this the right way?
<?php
$limit=10;
$start=1;
$where=’brand="BRAND1"’;
$c = $modx->newQuery(’Data’);
$c->select(’id’);
$c->where($where);
$count = count($modx->getCollection(’Data’, $c));
$c = $modx->newQuery(’Data’);
$c->select(’*’);
$c->where($where);
$c->limit($limit, $start);
$models = $modx->getCollection(’Data’, $c);
$c = $modx->newQuery('Data');
$c->where(array(
'brand' => 'BRAND1',
));
$count = $modx->getCount('Data', $c);
$c->limit(10,0);
$models = $modx->getCollection('Data', $c);