2

What is the best way to work with non-craft database tables as models?

I have a given table and I should render its records with multiple filtering and pagination. Importing them to entries is not an option, the table is always changing. So I would like to use as an ElementCriteriaModel.

Where should I get started? Writing a plugin is the only way? Iy yes, how can I create a model from this external table?

4

Sounds like a plugin is your best option. Creating a model is simple, but if you want to use ElementCriteriaModel, you need to create a custom entity, which gets a little more complex. Check out https://github.com/pixelandtonic/Events for a great example on setting up custom elements.

If you were to just stick with a model, you could run queries to fetch models using BaseRecord and avoid writing queries.

Check out a these files from a plugin I wrote. I am storing API keys in a table with two custom columns, 'service' and 'api_key'.

Model (https://github.com/aberkie/transit/blob/master/models/Transit_KeyModel.php):

<?php

namespace Craft;

class Transit_KeyModel extends BaseModel
{
    protected function defineAttributes()
    {
        return array(
            'service' => AttributeType::String,
            'api_key' => AttributeType::String
        );
    }
}

Record (https://github.com/aberkie/transit/blob/master/records/Transit_KeyRecord.php):

<?php

namespace Craft;

class Transit_KeyRecord extends BaseRecord
{
    public function getTableName()
    {
        return 'transit_keys';
    }

    protected function defineAttributes()
    {
        return array(
            'service' => AttributeType::String,
            'api_key' => AttributeType::String
        );
    }

    public function create()
    {
        $class = get_class($this);
        $record = new $class();

        return $record;
    }
}

It is best to use Services to use the records to get the data and populate the models for use. Service (https://github.com/aberkie/transit/blob/master/services/Transit_KeyService.php):

<?php

namespace Craft;    
class Transit_KeyService extends BaseApplicationComponent
{
    protected $keyRecord;
    public function __construct($keyRecord = null)
    {
        if(is_null($this->keyRecord))
        {
            $this->keyRecord = Transit_KeyRecord::model();
        }
    }

    public function getKey($service = null)
    {
        if($service === null)
        {
            $records = $this->keyRecord->findAll();

            return Transit_KeyModel::populateModels($records);          
        } else {

            $condition = "service = '$service'";
            $records = $this->keyRecord->find($condition);

            $model = Transit_KeyModel::populateModel($records);

            return $model->api_key;
        }
    }
}

You can use Yii's find function on records to limit results and use populateModels to return models.

$condition = "service = '$service'";
$records = $this->keyRecord->find($condition);
$model = Transit_KeyModel::populateModel($records);

If you didn't want to get this fancy, you could even just write your own mysql queries in your service files using craft()->db->createCommand()

$query = craft()->db->createCommand()
->select('*')
->from('myTable')
->where("myTable.name = '$name'")
->order("myTable.name")
->queryAll();

Hope this helps! Long answer short...look into making a plugin!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy