What is the proper way to fetch items from a custom db table and output them in twig? My main issue is perhaps not totally understanding the unique idioms of Yii, but I need to output data from my custom data tables in a template. So obviously I have a MyPluginVariables class and a MyPluginService to handle to actual business logic.

Using my MyPlugin_FooRecord I can use ActiveRecord to fetch some data. But this obviously returns the actual record objects, when I really just need a model passed to the template. From what I understand, I should use models not the records themselves. Is this correct?

Also, how exactly should I build a chainable syntax, similar to fetching entries in a template?

For example, I am trying to get this to work:

{% set rows = craft.myPlugin.someMethod().limit(10).order('some_col').sort('asc') %}


So relating this syntax back to my original question, how would I build this and return an array of models? The ElementCriteriaModel will automatically execute the find() method when it's used an array, should I just overriding the __toArray() method?

I have been searching and looking through the docs, so perhaps I missed something. I have also been looking through the code in Craft, but due to the abstractions and not being familiar with Yii core classes, I am getting a little lost in figuring out how this works.
I was able to figure almost all of this stuff out by diving through the code. The only exception is triggering the find() method automatically.

The key here was to follow the code in the ElementCriteriaModel. First thing I did was create a method in the MyPluginService class to fetch the data.

public function foo($criteria = false)
{
    return new MyPlugin_MyCriteriaModel($criteria ?: array());
}


I added a wrapper to my MyPluginVariable class.

public function parcels($criteria = array())
{
    return craft()-&gt;myPlugin-&gt;foo($criteria);
}


This will allow for the following syntax:

{% set data = craft.myPlugin.foo().limit(5).offset(1).find() %}


You must define a find() method in your MyPlugin_MyCriteriaModel class that will perform the SQL query to actually fetch your data. You also have to loop through the results set and instantiate MyPlugin_FooModel for each db row if you want to return a Model to the template instead of a stdObject or array.

For example, my find method looks like this:

public function find($attributes = null)
{   
    $this-&gt;setAttributes($attributes);

    if (!isset($this-&gt;_matchedObjects))
    {
        $objects = craft()-&gt;myplugin_foo-&gt;find($this);

        $this-&gt;setMatchedObjects($objects);
    }

    return $this-&gt;_matchedObjects;
}


Then in MyPlugin_FooService I have this:

public function query(MyPlugin_MyCriteriaModel $criteria)
{
    $record = new MyPlugin_FooRecord();

    $query = craft()-&gt;db-&gt;createCommand()
        -&gt;select('*')
        -&gt;from($record-&gt;getTableName())
        -&gt;limit($criteria-&gt;limit)
        -&gt;offset($criteria-&gt;offset)
        -&gt;order($criteria-&gt;order);

    if($criteria-&gt;id)
    {
        $query-&gt;where('id = :id', array(':id' =&gt; $criteria-&gt;id));
    }

    if($criteria-&gt;uid)
    {
        $query-&gt;where('uid = :uid', array(':uid' =&gt; $criteria-&gt;uid));
    }

    // More conditionals may go here

    return $query;
}

public function find(MyPlugin_MyCriteriaModel $criteria)
{
    $query = $this-&gt;query($criteria);

    return $this-&gt;_populateModelsFromArray($query-&gt;queryAll());
}

protected function _populateModelsFromArray(Array $array)
{
    $models = array();

    foreach($array as $row)
    {
        $models[] = MyPlugin_FooModel::populateModel($row);
    }

    return $models;
}