Symfony Propel Pager - what is the proper way to send it a custom MySQL query?

Question

Here is the query I need to run

SELECT REPLACE(REPLACE(SUBSTRING_INDEX(LOWER(table.url), '/', 3), 'www.', ''), 'http://', '') AS domain FROM table GROUP BY domain

But I'm having trouble passing a query like this to the Propel pager as criteria. I was hoping this would work.

$criteria->addSelectColumn('SUBSTRING_INDEX(' . TablePeer::URL . ', \'/\', 3) AS table');

But unfortunately it doesn't. Any ideas how I could pass this using a criteria method?

UPDATE

For those interested, this is what ended up working, thanks!

$criteria->addAsColumn('domain', 'SUBSTRING_INDEX(' . TablePeer::URL . ', \'/\', 3)');
$criteria->addGroupByColumn('domain');

Answer 1

You need to use some combination of Criteria::CUSTOM and addAsColumn in your criteria object - youll obviously also need to use doSelectRs (<= 1.2) or doSelectStmt (>=1.3) Im not sure what the exact formulation would be but this might get you in the right direction (note the link is for 1.2 so update sysntaxt/api as needed for 1.3 or 1.4).

http://stereointeractive.com/blog/2009/07/21/propel-criteria-on-custom-columns-with-addascolumn/

Answer 2

how do you retrieve the data neatly? I don't like the doselectRS method, can we do it via doselect?