Code snippets for symfony 1.x


Refine Tags

Snippets tagged "propel criteria"

Collate in propel sort

$c->addAscendingOrderByColumn( 'NAME COLLATE utf8_bin' );

quite obvious

by karol _ on 2011-06-13, tagged criteria  orderby  propel  sort 

performing custom joins and hydrations with Propel

People using Propel who want to perform custom joins and especially hydrations probably all faced the limited functionality that Propel offers for this: the probably well known doSelectJoinAll and doSelectJoinXXX methods (and similar for the count functionality)

Whenever you want to join only two related tables (or more, but not all), a related-related table (or even deeper) or a table that is related by multiple foreign-keys (like created_by and updated_by both refering to a user) you will find it gets hard to do this with the default functionality offered by Propel.

I have written a Helper/Plugin that solves this limitation, that can be downloaded from the plugin-pages:

The plugin contains:

The plugin does not change the way how to work with Propel, it will only expand the possibilities while remaining completely backwards compatible.

An example of how to use this helper:

Imagine you have a class of albums, and that every album can contain photos as well as other albums. You request all photo's sorted reversed on album name and join the related table to get the table-name in one go.

    name:     varchar(50)
    name:     varchar(50)

php code:

$criteria = new Criteria();
$sortColumn = AlbumPeer::alias(str_replace('.', '_', 'Photo.Album') , AlbumPeer::NAME); // this should be done nicer some day
$objectPaths = array('Photo', 'Photo.Album'); // this can be reduced (is similar) to array('Photo.Album');
$criteria = addJoinsAndSelectColumns($criteria, $objectPaths);
$photos = hydrate($criteria, $objectPaths, $connection = null); // I haven't decided if I want hydrate to perform the joining as well
foreach ($photos as $photo)
  echo $photo->getAlbum()->getName()." -> ".$photo->getName()."<br>\n";

The resulting sql is:

SELECT Photo.ID, Photo.ALBUM_ID, Photo.NAME, Photo_Album.ID, Photo_Album.ALBUM_ID, Photo_Album.NAME FROM `photo` `Photo` LEFT JOIN album Photo_Album ON (Photo.ALBUM_ID=Photo_Album.ID) ORDER BY Photo_Album.NAME DESC

I designed so called ObjectPaths and PropertyPaths together with a colleague of mine (Frans van der Lek), to make it easier to define classes and respectively properties of the (related) classes. The Object Paths contain the name of the base-class, followed by the relation-names defined in the peer-classes. These relation-names are defined by the builder, but can be extended/modified by extending the basePeer class (getRelations)

You can extend your Classes with custom Get-methods that can be accessed with the property-paths. I will show how this all comes to use, in my next article about the sfDataSourcePlugin, that I will release soon. (A plugin that provides a generic interface to select, sort, filter and iterate-over rows, no matter if they are provided by Propel, Doctrine, an Array, an Imap connection, etc. The Datasource interface is very light (in contradiction to dbFinder (no offense)) which makes it easy to write your own implementation)

Please provide feedback, questions and if desired I can add more examples/info.

by Leon van der Ree on 2009-06-07, tagged criteria  custom  database  hydrate  join  model  propel  sfpropelhelperplugin 
(1 comment)

subqueries with criteria

I haven't found a way to use subqueries as alias with criteria in the book.

Here is a small piece of code that works very well:

the subquery is in an alias defined by Criteria::addAsColumn($alias, $expr)

it can be used to order the result, or in case you have to deal with foreign keys, etc.

$c = new Criteria();
$c->addAsColumn('brandname', '(SELECT FROM brand WHERE');
$this->products = ProductPeer::doSelect($c);

Product has a brand_id that references the id key of the brand table that contains the 'name' field which is used to order the result.

Patrice Blanchardie

by noname noname on 2008-01-26, tagged criteria  order  propel  sql  subquery 

Using SQL aggregate functions

I had some trouble finding information on how to use SQL aggregate functions like GROUP BY, COUNT and HAVING with Propel, so here is some info about that.

Suppose you have a system with a many-to-many relation between articles and authors, this example shows how to find out how many articles each author has worked on.

$c = new Criteria();
// optionally look only for certain authors whose IDs are in $results
$c->add(AuthorPeer::ID, $results, Criteria::IN);
// JOIN them with the article IDs
$c->addJoin(ArticleAuthorPeer::AUTHOR_ID, AuthorPeer::ID);
// list each author only once and count the number of articles they have worked on
$c->addAsColumn('numArticles', 'COUNT('.AuthorPeer::ID.')');
// optionally retrieve only those authors that have a certain number of articles (like 'numArticles=2' or 'numArticles>2')
// the first argument does not really matter since this is a custom criteria
// according to the SQL standard this cannot be done with a WHERE clause
$c->addHaving($c->getNewCriterion(AuthorPeer::ID, 'numArticles=2', Criteria::CUSTOM));
// order by the number of articles
// get a ResultSet and iterate over it
$rs = AuthorPeer::doSelectRS($c);
$counts = array();
$results = array();
while ($rs->next())
  $author = new Author();
  // hydrate the object and store how many columns it has
  $lastColumn = $author->hydrate($rs);
  $results[] = $author;
  // then retrieve the COUNT from the first column not belonging to the object
  $counts[] = $rs->getInt($lastColumn);
$this->results = $results;
$this->counts = $counts;
by Georg Sorst on 2007-12-18, tagged aggregate  count  criteria  manytomany  propel  sql 

update according to the primary key using propel

If you want to make an update according to the primary key you just need to use one criteria:

$c = new Criteria();

and the query is:

UPDATE my_object SET NAME = 'ola',CITY = 'pepito' WHERE my_object.ID=123

Compared to the update query using Propel snippet which pass 2 criterias to the BasePeer:doUpdate method, here we pass only one criteria to the MyObjectPeer::doUpdate method. This method remove the index ID of the "set" criteria and use it for the "select" criteria.

by sampq on 2007-12-05, tagged criteria  propel 
(1 comment)

Simulating a BETWEEN construct

This was asked on the forum, so I thought I would place it here too.


Suppose you have a model that resembles the following:

    start_date: {type: date}
    end_date: {type: date}

And you want to know the following: are there any records where either $date1 or $date2 is between start_date and end_date?

Consider that $date2 could be some fixed $offset from $date1, so that the question becomes: are there any records which partially cover the $offset period following $date1?

Using Criterion Objects

Since the BETWEEN construct is unavailable through Propel (at least not as an object or constant), we have to use the knowledge that:

  a >= x
  a <= y
  x <= a <= y

Which gives us:

$c = new Criteria();
$date1 = '2007-08-20';
$date2 = '2008-08-20';
// test against date1
$date1Criterion = $c->getNewCriterion(RangePeer::START_DATE, $date1, Criteria::LESS_EQUAL);
// test against date2
$date2Criterion = $c->getNewCriterion(RangePeer::START_DATE, $date2, Criteria::LESS_EQUAL);
// conjunction
$date1Criterion->addAnd($c->getNewCriterion(RangePeer::END_DATE, $date1, Criteria::GREATER_EQUAL));
$date2Criterion->addAnd($c->getNewCriterion(RangePeer::END_DATE, $date2, Criteria::GREATER_EQUAL));
// disjunction

Using Criteria::CUSTOM

Or if you absolutely must have your BETWEEN, try this:

$c = new Criteria();
$date1 = '2007-08-20';
$date2 = '2008-08-20';
$c->add(RangePeer::START_DATE, "'{$date1}' between ".RangePeer::START_DATE." and ".RangePeer::END_DATE, Criteria::CUSTOM);
$c->addOr(RangePeer::START_DATE, "'{$date2}' between ".RangePeer::START_DATE." and ".RangePeer::END_DATE, Criteria::CUSTOM);

Note that the first arguments to Criteria::add() and Criteria::addOr() can be any column, but they must be the same column.

Note also that when providing the argument, {$dateN} appears between single quotes. It must appear quoted (and escaped) in order for this query to return correct (or any) results.

by Jesse Dhillon on 2007-08-22, tagged between  criteria  criterion  date  mysql  propel  query  range 

Propel: Select entries that are not alpha

Thanks to netcrash from the symfony irc channel I came along regular expressions in MySQL:


I'm using it to select all entries that don't start with a letter in an alphabetical pagination:

$c = new Criteria();
$regex = CmsUserPeer :: USERNAME." NOT REGEXP '^[[:alpha:]]'";
$c->add(CmsUserPeer :: USERNAME, $regex, Criteria::CUSTOM);
$this->cms_user = CmsUserPeer :: doSelect($c);

Instead of selecting by REGEXP pookey had the idea of "using substr to get the first char, then getting it's character code, and doing a BETWEEN on it" for performance reasons.

There is another faster solution:

$c = new Criteria();
$cq = "substring( ".CmsUserPeer :: USERNAME.", 1, 1 ) NOT BETWEEN  'a' AND  'z'";
$c->add(CmsUserPeer :: USERNAME, $cq, Criteria::CUSTOM);
$this->cms_user = CmsUserPeer :: doSelect($c);
by ian iam on 2007-02-12, tagged criteria  mysql  propel  regexp 

Custom column selection and use of distinct using Propel Criteria

Here is an example of obtaining a custom set of select columns using Propel Criteria, as opposed to using a custom SQL statement. The use of distinct is also demonstrated.

// Set up empty select
$c = new Criteria();
// Read distinct(owningdepot) from the load table...
// ...which are IN the provided list
$c->add(TmsLoadPeer::LOAD_ID, $loadIds, Criteria::IN);
$depots = TmsLoadPeer::doSelectRS($c);
// Then return an array of depots
$arrDep = array();
foreach ($depots as $depot)
  $arrDep[] = $depot[0];
by halfer on 2006-08-23, tagged criteria  propel 

Sub-selects using Propel

Let say you want to write a sub-select, and have a Propel object returned.

As an example, lets generate the following SQL:

    orders.STATUS IN (
             STATUS.ORDER_TYPE = 'purchase'

Propel does not natively handle this sort of SQL. There are however two ways to create this SQL or to get this data set and have Propel objects returned.

Overview of the different methods

  1. Use of Criteria::CUSTOM
  2. Rewrite the SQL using JOINs
    • PRO: Very readable code
    • CON: Not using a sub-select
    • CON: You may be using a sub-select for SQL performance, so this may be slower

Way 1: Use of Criteria::CUSTOM

$c = new Criteria();
$subSelect = "orders.STATUS IN (
         status.ORDER_TYPE = 'purchase'
$c->add(StatusPeer.STATUS, $subSelect, Criteria::CUSTOM);
$orders = StatusPeer::doSelect($c);

Way 2: Rewrite the SQL using JOINs

Example of the rewritten SQL:

    AND STATUS.ORDER_TYPE = 'purchase'

This can be written as follows in your action:

$c = new Criteria();
$c->addJoin (OrderPeer::STATUS, StatusPeer::NAME);
$c->add(StatusPeer.ORDER_TYPE, 'purchase');
$orders = StatusPeer::doSelect($c);
by Greg Militello on 2006-08-17, tagged criteria  propel  query  sql 

Custom criteria for comparing 2 fields from the same record

Imagine that you have a table with the following columns:


If you want to retrieve the records having col1 greater than col2, the following doesn't work:

$c = new Criteria();
$c->add(MyTablePeer::COL1, MyTablePeer::COL2,  Criteria::GREATER_THAN);

Instead, you need to add a custom condition to your Criteria:

$c = new Criteria();
$c->add(MyTablePeer::COL1, MyTablePeer::COL1.'>='.MyTablePeer::COL2, Criteria::CUSTOM);
by Francois Zaninotto on 2006-07-10, tagged criteria  propel 

update query using Propel

When you need to update several records in a row, you don't have to loop over the result of a doSelect() call and do a save() for each object (which would make way too many queries).

Instead, you can use the BasePeer method doUpdate() as follows:

BasePeer::doUpdate($select_criteria, $update_criteria, $connection);

For instance:

$con = Propel::getConnection();
// select from...
$c1 = new Criteria();
$c1->add(CommentPeer::POST_ID, $post_id);
// update set
$c2 = new Criteria();
$c2->add(CommentPeer::RATING, 5);
BasePeer::doUpdate($c1, $c2, $con);

If course, if you are in a Peer class, you will need to use the self object:

$con = Propel::getConnection();
// select from...
$c1 = new Criteria();
$c1->add(self::POST_ID, $post_id);
// update set
$c2 = new Criteria();
$c2->add(self::RATING, 5);
BasePeer::doUpdate($c1, $c2, $con);
by Francois Zaninotto on 2006-07-03, tagged criteria  propel 

Little trick to randomize results

More a PHP trick than symfony's one, but as you can't with propel randomize order of results, just do:

$c = new Criteria()
... fill your criteria there ...
$result = MyTablePeer::doSelect($c);

obvious? sorry, seen someone asking on IRC once :D

by Romain Dorgueil on 2006-05-29, tagged column  criteria  database  order  propel  random 

Query objects between two dates with Criteria

Say you look for the objects of class Foo being created between $from_date and $to_date. This should do the trick:

$c = new Criteria();
$criterion = $c->getNewCriterion(FooPeer::CREATED_AT , date('Y-m-d', $from_date), Criteria::GREATER_EQUAL  );
$criterion->addAnd($c->getNewCriterion(FooPeer::CREATED_AT , date('Y-m-d', $to_date), Criteria::LESS_EQUAL ));
$shows = FooPeer::doSelect($c);
by Francois Zaninotto on 2006-05-24, tagged criteria  date  propel