CODEWITHSUNDEEP
phppropel
billyonecan
billyonecan

Reputation: 20270

Select specific column(s) using Propel without aliasing

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?

Upvotes: 4

Views: 7982

Answers (2)

François Zaninotto
François Zaninotto

Reputation: 7355

You're obviously trying to query data, not to interact with your business model. As such, using an ORM may be counterproductive in this very case. I recommend that you use plain old PDO (which Propel encapsulates) for this kind of logic.

I recommend this resource on the Propel blog answering most common questions about how to write special queries with the ORM : http://propel.posterous.com/how-can-i-write-this-query-using-an-orm

Upvotes: 2

LeonardChallis
LeonardChallis

Reputation: 7783

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

Upvotes: 3

Related Questions