Geek Answers Handbook

Select a specific column (s) using Propel without smoothing

I have quite a few problems rewriting a simple query using the methods provided by Propel 1.6.

The request is as follows:

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

Using Propel, I wrote 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 type_id and count columns , so I tried to add the following:

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

This generates the following (working) request:

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

However, I do not want to use the type_id column type_id . I tried passing the actual column name to the select array ( ->select(array('type_id')) ), however this leads to 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 get a type_id column without its aliases?

+4
source share
2 answers

Propel abstracts column names, so your PHP need not know about them.

From PropelORM.org :

Manipulating the names of the object model allows you to separate from the actual data storage and change the database names without the need to update the PHP code.

TypeID will be what phpName equals in your schema. Try using this, as opposed to the mysql column name.

Given this, if you really want to use the actual column name, you can do this using propel introspection classes . For instance:

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

But since the documentation itself says:

Remember to always use phpName in your PHP code.

+3
source

Obviously, you are trying to request data, and not interact with your business model. Thus, the use of ORM can be counterproductive in this case. I recommend that you use a simple old PDO (which Propel encapsulates) for this kind of logic.

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

+2
source

More articles:


All Articles