sfPropelCustomSelectPlugin sample usage

This article has moved to this location.


A few days ago, i’ve made sfPropelCustomSelectPlugin, a plugin that allow to select arbitrary columns without loosing the power of Propel ORM. In this article, i would like to show you how to use the plugin in your application. This plugin was designed to work well with doSelect, doSelectJoinXXX functions and sfPropelPager class. If you are using propel 1.5 you can using ModelCriteria::select() and ModelCriteria::withColumn() together to achieve the same result (http://www.propelorm.org/ticket/1172).
I assume that you have already installed the plugin correctly.

Schema

The schema below is taken from jobeet tutorial

# config/schema.yml
propel:
  jobeet_category:
    id:           ~
    name:         { type: varchar(255), required: true, index: unique }
 
  jobeet_job:
    id:           ~
    category_id:  { type: integer, foreignTable: jobeet_category, foreignReference: id, required: true }
    type:         { type: varchar(255) }
    company:      { type: varchar(255), required: true }
    logo:         { type: varchar(255) }
    url:          { type: varchar(255) }
    position:     { type: varchar(255), required: true }
    location:     { type: varchar(255), required: true }
    description:  { type: longvarchar, required: true }
    how_to_apply: { type: longvarchar, required: true }
    token:        { type: varchar(255), required: true, index: unique }
    is_public:    { type: boolean, required: true, default: 1 }
    is_activated: { type: boolean, required: true, default: 0 }
    email:        { type: varchar(255), required: true }
    expires_at:   { type: timestamp, required: true }
    created_at:   ~
    updated_at:   ~
 
  jobeet_affiliate:
    id:           ~
    url:          { type: varchar(255), required: true }
    email:        { type: varchar(255), required: true, index: unique }
    token:        { type: varchar(255), required: true }
    is_active:    { type: boolean, required: true, default: 0 }
    created_at:   ~
 
  jobeet_category_affiliate:
    category_id:  { type: integer, foreignTable: jobeet_category, foreignReference: id, required: true, primaryKey: true, onDelete: cascade }
    affiliate_id: { type: integer, foreignTable: jobeet_affiliate, foreignReference: id, required: true, primaryKey: true, onDelete: cascade }

Let’s us generate backend module for JobeetJob class model

php symfony propel:generate-admin backend JobeetJob --module=job

Open and configure config/generator.yml file found in the module:

generator:
  class: sfPropelGenerator
  param:
    model_class:           JobeetJob
    theme:                 admin
    non_verbose_templates: true
    with_show:             false
    singular:              JobeetJob
    plural:                JobeetJobs
    route_prefix:          jobeet_job
    with_propel_route:     1
    actions_base_class:    sfActions

    config:
      actions: ~
      fields:  ~
      list:
        title: Job Management
        max_per_page: 10
        peer_method: doSelectJoinJobeetCategory
        layout:  stacked
        display: [company, position, location, url, is_activated, email]
        params:  |
          %%is_activated%% <small>%%jobeet_category%%</small> - %%company%%(<em>%%email%%</em>) is looking for a %%=position%% (%%location%%)
      filter:
        class: false
      form:    ~
      edit:    ~
      new:     ~

Open your favourite browser, and visit the job module you’ve just created in the development mode, then have a look at the SQL statements in the web debug toolbar.

SQL Query before using sfPropelCustomSelectPlugin

SQL Query before using sfPropelCustomSelectPlugin


As you can see from the picture above, propel will always select all columns from both tables jobeet_job and jobeet_category, and it’s common behavior in the ORM frameworks.
But, for some reasons, sometimes we only want to select a few columns. Yes, we could still manually select the columns we want by using addSelectColumn() function. But unfortunately, it won’t work with doSelect and doSelectJoinXXX functions and so with sfPropelPager, because sfPropelPager expecting the collection of objects.
So how to solve the problem stated above, while we still want the power of Propel ORM?

 

Using sfPropelCustomSelectPlugin

Now open the actions.class.php file in the module/job/actions directory, then override the executeIndex() function.
First, we have to define which columns we want to select, by supplying the array of selected columns to the setSelectedColumns function.

public function executeIndex(sfWebRequest $request)
{
   JobeetJobPeer::setSelectedColumns(array(JobeetJobPeer::ID, JobeetJobPeer::IS_ACTIVATED, JobeetJobPeer::COMPANY, JobeetJobPeer::EMAIL, JobeetJobPeer::POSITION, JobeetJobPeer::CATEGORY_ID, JobeetJobPeer::LOCATION));
   JobeetCategoryPeer::setSelectedColumns(array(JobeetCategoryPeer::NAME));
   parent::executeIndex($request);
}

Now have a look again at the SQL statements in the web debug toolbar.

SQL Query after using sfPropelCustomSelectPlugin

SQL Query after using sfPropelCustomSelectPlugin


What you can see? Yes, propel now simply select the columns which we have defined previously.

 

Thanks you for visiting my blog, have fun with Symfony and Propel!!!.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: