Symfony Propel criteria

Question

Hi,

Is there any possible way to convert the MySQL object into criteria object? I tried this query
"select p.disrepid, p.subject, p.body, c.disrepid as disrepid1, c.subject as subject1, c.body as body1 from discusreply as p, discusreply as c where p.distopid='.$this->id.' and (c.disrepid = p.parentid or c.parentid = p.distopid) order by p.disrepid ASC". I tried a lot for converting this query into a Criteria, But nothing happened. I want this criteria object for passing this into Pager class for completing the pagination. $pager->setCriteria($c);...

Thanks, Raj

Answer 1

This site will help a lot for learning to write criteria: http://propel.jondh.me.uk/ You can use it to generate criteria code from pseudo SQL. I would also recommend grabbing the Symfony/Propel cheat sheets from http://trac.symfony-project.org/wiki/CheatSheets

For your query in particular you will want something like this:

$c = new Criteria();
$c->addJoin(discusreply::DISREPID, discusreply::PARENTID, Criteria::INNER_JOIN);  
$c->clearSelectColumns();
$c->addSelectColumn(discusreplyPeer::Disrepid); 
...
$c->add(discusreplyPeer::DISTOPID, $this->id, Criteria::EQUAL);
... 
$c->addAscendingOrderByColumn(discusreply::DISREPID);

I'm not sure that the Criteria system supports multiple clauses for an inner join so you may have to revert back to ad-hoc SQL for this query. (If it does I would love to know how) The following code will create a ResultSet object similar to what you would get from simple database abstraction layers.

$sql = "SELECT ...";
$dbh = Propel::getConnection([DB]);
$sth = $dbh->createStatement();
$res = $sth->executeQuery($sql, ResultSet::FETCHMODE_NUM);

I don't think there is much of a disadvantage to using the ad-hoc method on a query like this since you will have to deal with ResultSet objects rather than table-specific objects when you are returning only specific columns.

Answer 2

You can try auto-generating the criteria from sql using this site: http://propel.jondh.me.uk/criteria/analyse

Answer 3

You can use your own SQL do perform a query, but there is no automated way to turn sql into a Criteria object.

$con = Propel::getConnection(DATABASE_NAME);
$sql = "SELECT books.* FROM books 
    WHERE NOT EXISTS (SELECT id FROM review WHERE book_id = book.id)";
$stmt = $con->createStatement();
$rs = $stmt->executeQuery($sql, ResultSet::FETCHMODE_NUM);
$books = BookPeer::populateObjects($rs);

This bypasses Criterion objects all together. You mentioned wanting a criteria object so you could feed this into a pager. You can instead set a custom select method into your pager, which will then perform your custom query. If you need to pass a parameter into this, I would recommend extending sfPropel with your own pager class that can optionally pass parameters to your peer select methods so you don't have to use Criteria objects at all. As a quick alternative, you can do something like this, using your Criteria as a container for your select parameters:

$c = new Criteria();
$c->add(DiscussreplyPeer::ID, $myId);
$pager = new sfPropelPager();
$pager->setCriteria($c);
$pager->setPeerMethod('getReplies');

And then in your peer class:

public static function getReplies(Criteria $c) {
    $map = $c->getMap();
    $replyId = $map[DiscussreplyPeer::ID]->getValue();

    $con = Propel::getConnection(DATABASE_NAME);
    $sql = "select p.disrepid, p.subject, p.body, c.disrepid as disrepid1, c.subject as subject1, c.body as body1 from discusreply as p, discusreply as c where p.distopid=? and (c.disrepid = p.parentid or c.parentid = p.distopid) order by p.disrepid ASC";

    $stmt = $con->prepareStatement($sql);
    $stmt->setString(1, $replyId);

    $rs = $stmt->executeQuery();

    $results = array();
    while ($rs->next()) {
      // for example
        $results['disrepid'] = $rs->getInt('disrepid');
    }

    return $results;
}

More tips on propel and symfony can be found at: http://stereointeractive.com/blog/2007/06/12/propel-queries-using-custom-sql-peer-classes-and-criterion-objects/