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?

score 3 · Accepted Answer · answered Apr 25 '13 at 10:07

3

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

answered Apr 25 '13 at 10:07

LeonardChallis

7,759
6
45
76

4

Or you can just use `VisitPeer::TYPE_ID` ... it's a shortcut to the column name based on the defined schema name. – Jordan Kasper Apr 25 '13 at 14:20
If he changed the db column to `my_type_id` would VisitPeer::TYPE_ID still be available (and point to the column)? – LeonardChallis Apr 25 '13 at 14:52
No, if he plans to change the column name and NOT let the phpName change, then your method is best. (And FYI, your method is good regardless, which is why my input isn't a separate answer, just a side note. :) ) – Jordan Kasper Apr 25 '13 at 15:22
Thanks the for clarification - appreciate it :) – LeonardChallis Apr 25 '13 at 16:54

score 2 · Answer 2 · answered Apr 26 '13 at 08:17

2

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

answered Apr 26 '13 at 08:17

François Zaninotto

7,068
2
35
56

Unfortunately, **Posterous Spaces is no longer available** – Paul T. Rawkeen Jan 22 '15 at 13:55
2

It's been migrated years ago to http://propelorm.org/blog/2011/02/02/how-can-i-write-this-query-using-an-orm-.html – François Zaninotto Jan 23 '15 at 14:28

Select specific column(s) using Propel without aliasing

2 Answers2