Code snippets for symfony 1.x

Navigation

Refine Tags

Snippets tagged "mysql"

Custom ORDER BY with Criteria

Hello everyone,

i accidently found a neat feature of MYSQL on the web, which isn't even documented in the official MySQL 5.x manual (original blog entry to be found at http://www.cfdan.com/posts/Handy_MySQL_-_ORDER_BY_FIELD.cfm">original Blog Posting)

Basicly i needed a way to create some custom order for my records, so i started to build a raw SQL query. Then i ran into troubles when i tried to get this working with a pager.. all in all, i tried it to solve it with Criteria.

I created a new class, myCriteria which inherits from the original Criteria class.

/**
 * Add order by column name with a custom Order for a given Field
 *
 * @param unknown_type $propertyBracket
 * @return unknown
 */
public function addCustomOrderByColum($propertyField, $propertyList) {
    $this->orderByColumns[] = 'FIELD('.$propertyField.', ' . $propertyList .' )';
    return $this;
}
 

An example could look like:

$c->addCustomOrderByColum(CustomPeer::TYPE, " 'Sold', 'Pending', 'Announced' ");
 

This way you can create ORDER BY Statements which are not bound to common ASC/DESC rules.

by Christian Weyand on 2007-12-12, tagged criteria  custom  mysql 
(2 comments)

Simulating a BETWEEN construct

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

Problem

Suppose you have a model that resembles the following:

  range:
    id:                                   
    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:

if
  a >= x
and
  a <= y
then
  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
$date1Criterion->addOr($date2Criterion);
$c->add($date1Criterion);

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 
(2 comments)

Propel: Select entries that are not alpha

Thanks to netcrash from the symfony irc channel I came along regular expressions in MySQL: http://dev.mysql.com/doc/refman/4.1/en/regexp.html

Tutorial: http://www.brainbell.com/tutorials/MySQL/Using_MySQL_Regular_Expressions.htm

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 

Simulating an enum column type in the model

As enum is a MySQL specific type, you cannot have a column of type enum in your schema.yml (which is database-independent). One solution to simulate it is to create another table to store the different possible values. But the Model class offer an alternative solution that is probably more elegant.

Imagine you have a status column in you article table you want to be an enumerated list with values like (open, closed). Simply declare the status column as integer and then modify the model as follows:

In ArticlePeer.php:

class ArticlePeer...
{
   static protected $STATUS_INTEGERS = array('open', 'closed');
   static protected $STATUS_VALUES = null;
 
   static public function getStatusFromIndex($index)
   {
     return self::$STATUS_INTEGERS[$index];
   }
 
   static public function getStatusFromValue($value)
   {
     if (!self::$STATUS_VALUES)
     {
       self::STATUS_VALUES = array_flip(self::$STATUS_INTEGERS);
     }
 
     $values = strtolower($value);
 
     if (!isset(self::STATUS_VALUES[$value])
     {
       throw new PropelException(sprintf('Status cannot take "%s" as a value', $value));
     }
 
     return self::STATUS_VALUES[strtolower($value)];
   }
}

In Article.php:

class Article
{
   public function setStatusName($value)
   {
     $this->setStatus(ArticlePeer::getStatusFromValue($value));
   }
 
   public function getStatusName()
   {
     return ArticlePeer::getStatusFromIndex($this->getStatus());
   }
}

(Original tip from Fabien)

by Francois Zaninotto on 2006-10-27, tagged enum  getter  model  mysql  setter 
(8 comments)

Schema for Creole/DB Session Storage

Here is the schema you need to set up database session storage.

CREATE TABLE `session` (
  `sess_id` varchar(32) NOT NULL,
  `sess_data` text NOT NULL,
  `sess_time` int(11) NOT NULL
);
by Romain Dorgueil on 2006-05-31, tagged cookie  creole  database  mysql  session  storage  user 
(3 comments)

Use InnoDB with Propel

To enable InnoDB support in Propel, you can add this line at the end of your config/propel.ini configuration file:

propel.mysql.tableType = InnoDB
by Fabien Potencier on 2006-05-20, tagged mysql  propel