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:
LEFT OUTER JOIN
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!