ActiveRecords with PostgreSQL's PL/pgSQL Functions

Yii 1.1 General Discussion
1

I am still relevant new to Yii, and really enjoy building web application around this great PHP framework! Kudos to some of you who contributed to development of Yii Framework!

Anyway, I am building my custom applications using quite advanced custom select SQL query using PostgreSQL’s Pl/PgSQL functions, PLUS LEFT OUTER JOIN. At beginning of development, I decided to go on with CDbConnection functions to help doing custom queries, then later on I realized that the presentation view, sorting, pagination, and the search features may be little extensive work of using without ActiveRecords.

I was hoping that I could take advantage of the ActiveRecords, but yet maintain this complicated data query… Here is example, giving you some idea of my SQL query I used:




		$start_time = Date('Y-m-d h:ia', strtotime('yesterday'));

		$end_time = Date('Y-m-d h:ia', strtotime('tomorrow'));

		$timezone = 'America/New_York';


		$connection = Yii::app()->db;

		$sql = "select id, frequency, CASE WHEN name IS NOT NULL THEN name ELSE 'undefined' END as title, frequency, " .

		"CASE WHEN starts_on IS NOT NULL THEN to_char(starts_on, 'YYYY-MM-DD') " .

		"ELSE to_char(starts_at::timestamp WITH TIME ZONE AT TIME ZONE :userzone, 'YYYY-MM-DD\"T\"HH24:MI:SS') END as start, " .

		"CASE WHEN starts_on IS NOT NULL THEN CASE WHEN ends_on IS NOT NULL THEN to_char(ends_on, 'YYYY-MM-DD') " .

		"ELSE to_char(starts_on, 'YYYY-MM-DD') END " . 

		"ELSE to_char(ends_at::timestamp WITH TIME ZONE AT TIME ZONE :userzone, 'YYYY-MM-DD\"T\"HH24:MI:SS') END as end, " .

		"CASE WHEN starts_on IS NOT NULL THEN true ELSE false END as \"allDay\", " .

		"srt_event_to_pad.pad_id ".

		"from recurring_events_for(:start_time, :end_time, :userzone, NULL) ".

		"LEFT OUTER JOIN srt_event_to_pad ON (event_id = id AND ((date = starts_on::date) OR (date = starts_at::date)))".

		"WHERE cal_id = :cal_id";

		$command=$connection->createCommand($sql);

		$command->bindValue(":userzone",$timezone,PDO::PARAM_STR);

		$command->bindValue(":start_time",$start_time,PDO::PARAM_STR);

		$command->bindValue(":end_time",$end_time,PDO::PARAM_STR);

		$rows=$command->queryAll();

You can notice some complicated sql query been used:

  • PL/PgSQL Function

  • LEFT OUTER JOIN

  • Converting timestamp

  • IF/ELSE

  • date/string comparisons

To sum up my long explanation, I was wondering if its possible to comprehensive those complicated sql functions along with Yii’s ActiveRecords? Also, feedback on different approach or method is welcome!

Thanks,

Jason

2

Hey Jason,

I have a similar post here.

I’ve been using this class, but I can’t get the pagination to work properly with it. Also, I had to modify it to take params for binding.

Have you had any luck at your end that you could share?

3

Jason, I can advice you to use more php and less sql for presentation layer.

Instead of writing CASE WHEN name IS NOT NULL THEN name ELSE ‘undefined’ in query, better to select the row value and then write a method in the model:




public function getPrettyName()

{

  if ($this->name)

      return $this->name;

   else

      return 'undefined';

}

Like that you can even return Yii::t(‘main’, ‘undefined’) and internationalize the word. And by the way is not very nice to sort undefined like word, what is the meaning of have the list of name like that:

Tommaso, Ugo, undefined, Valerio, Zita?

Undefined is not a name, should be an the beginning or at the end, not sorted by alphabetical value (that’s just my personal opipnion)

Like that your sql can be semplified a lot.

Anyway is possible to use the same approach but with AR, just using a CDbCriteria:




$criteria= new CDbCriteria;

$criteria->select="

	id, 

	frequency, 

	CASE WHEN name IS NOT NULL THEN name ELSE 'undefined' END as title, 

	frequency, 

	CASE 

		WHEN starts_on IS NOT NULL 

		THEN to_char(starts_on, 'YYYY-MM-DD') 

		ELSE to_char(starts_at::timestamp WITH TIME ZONE AT TIME ZONE :userzone, 'YYYY-MM-DD\"T\"HH24:MI:SS') 

	END as start, 

	CASE 

		WHEN starts_on IS NOT NULL 

		THEN 

			CASE 

				WHEN ends_on IS NOT NULL 

				THEN to_char(ends_on, 'YYYY-MM-DD') 

				ELSE to_char(starts_on, 'YYYY-MM-DD') 

			END 

		ELSE to_char(ends_at::timestamp WITH TIME ZONE AT TIME ZONE :userzone, 'YYYY-MM-DD\"T\"HH24:MI:SS') 

	END as end, 

	CASE 

		WHEN starts_on IS NOT NULL 

		THEN true 

		ELSE false 

	END as \"allDay\", 

	srt_event_to_pad.pad_id";

$criteria->join="RIGHT OUTER JOIN  recurring_events_for(:start_time, :end_time, :userzone, NULL)   ON (event_id = id AND ((date = starts_on::date) OR (date = starts_at::date)))";

$criteria->condition= " cal_id = :cal_id";