Select specific column(s) using Propel without aliasing

Question

I'm having quite a bit of trouble rewriting a simple query using methods provided by Propel 1.6.

The query is as follows:

SELECT type_id, COUNT(id) as `count` FROM visit GROUP BY type_id;

Using Propel, I've written the following:

$visitCount = VisitQuery::create()
                ->withColumn('COUNT(id)', 'count')
                ->groupBy('TypeId')
                ->find();

This generates the following query:

SELECT visit.ID, visit.JOB_ID, visit.ENGINEER_ID,
       visit.TYPE_ID, visit.VISIT_DATE, visit.STATUS, COUNT(id) AS count 
FROM `visit` 
GROUP BY visit.TYPE_ID

Which works, but I only need the columns type_id and the count, so I tried adding the following:

$visits = VisitQuery::create()
            ->withColumn('COUNT(id)', 'count')
            ->select(array('TypeId'))
            ->groupBy('TypeId')
            ->find();

This generates the following (working) query:

SELECT COUNT(id) AS count, visit.TYPE_ID AS "TypeId" 
FROM `visit` GROUP BY visit.TYPE_ID

However, I don't want to alias the type_id column. I tried passing the actual column name to the select array (->select(array('type_id'))), however that results in the following query (which obviously doesn't work):

SELECT COUNT(id) AS count,  AS "type_id" FROM `visit` GROUP BY visit.TYPE_ID

How can I retrieve the type_id column without it being aliased?

Answer 1

Propel abstracts the column names so that your PHP doesn't necessarily need to know about them.

From PropelORM.org:

Manipulating object model names allows you to be detached from the actual data storage, and alter the database names without necessarily updating the PHP code.

TypeID will be whatever phpName equals in your schema. Try to use this as opposed to the mysql column name.

With that in mind, if you really want to use the actual name of the columns you can do so by using propel's introspection classes. For instance:

var_dump(VisitPeer::getTableMap()->getColumnByPhpName('TypeId')->getName());
var_dump(VisitPeer::getTableMap()->getColumn('type_id')->getPhpName());

But as the documentation itself says:

Remember to always use the phpName in the PHP code