How to leverage the model to access the database

hgtonight

This discussion was created from comments split from: New Plugin.

rbrahmson

Hi @R_J (and possibly @hgtonight). I've been following this discussion with interest because I sense that Rangerine is going through the same Vanilla learning journey I was going through, and while I may be in a different point on the road, I am still far away from where I want to be. . Your various howto/Example codes were very useful, but there is one more sample skeleton I would benefit from: a plugin that uses a new model to access a new table. There are many suggestions in replies to use models instead of direct sql, but no comprehensive example that demonstrate all the ways to access the data (create/get/insert/update/delete/count?) and the way to code the model that would support these calls (or is it all already done automatically by Vanilla???).
This would be the next stage for me in Vanilla, but would probably help @rangerine and others as well (if I understand this correctly, accessing sql directly in views [possibly in some hooks as well] is really not encouraged, so in a way I already learned bad habits...)

R_J

Basically it is quite easy.
You already know how to create a table, don't you? If not, look at the structure.php files in /applications/(dashboard|conversation|vanilla)/settings/structure.php There are numerous examples and you can directly compare the code and the outcome by looking at your database.

So in your example plugin you know that you have a basic setup like that:

public function setup() {
    $this->structure();
}

public function structure() {
    Gdn::structure()
        ->Table('Abracadabra')
        ->primaryKey('AbracadabraID')
        ->column('Hocus', ...)
        ->column('Pocus', ...)
        ...
        ->set();
}

public function pluginController_abracadabra_create($sender, $args) {
    // This is where you can write the following examples and then you'll be able to see the results at example.com/plugin/abracadabra

    // This is nearly all the magic.
    $abracadabraModel = new Gdn_Model('Abracadabra');

}

There is a model now for our custom table and that model is an instance of /library/core/class.model.php. Open up that file and take a look at the functions there. You can use all of them with your model:

decho(
    $abracadabraModel->getCount(),
    'Number of records in '.$abracadabraModel->Name
);

// Add some rows...
$abracadabraModel->save(
    [
        'Hocus' => 'Harry',
        'Pocus' => 'Hermione'
    ]
);
$abracadabraModel->save(
    [
        'Hocus' => 'Batman',
        'Pocus' => 'Robin'
    ]
);

decho($abracadabraModel->get(), 'New table content');

$HarryAndHermione = $abracadabraModel->getWhere(['Pocus' => 'Hermione']);
$abracadabraModel->delete(['AbracadabraID' => $HarryAndHermione->AbracadabraID]);

decho($abracadabraModel->getCount(), 'New count');

There is nothing complicated here. Go through class.model.php and you see all you can do.

If you feel comfortable with that, you should take a look at the validation!

You will even find some other goodies, I think. Take a look at addInsertFields() and saveToSerializedColumn(). They are nothing you can use all the time, but they might come handy in one of your projects some day.

Bleistivt

@rbrahmson

You would start by creating a new class.reviewmodel.php.
Note that it extends Gdn_Model. You call the constructor with the name of the table ("Review"):

<?php

class ReviewModel extends Gdn_Model {

    public function __construct() {
        parent::__construct('Review');
    }

}

And that's it, you have created your first model!

This is how you create a new instance:

$rm = new ReviewModel();

Now you can use the various methods it inherits from Gdn_Model.
A few examples:

Get a review by primary key.

$review = $rm->getID(123);

Delete a review by primary key.

$rm->deleteID(123);

Insert a new review.

$rm->insert([
    'FieldA' => 'bla',
    'FieldB' => 'bla'
    ...
]);

Note that you don't need to specify these fields when calling insert():

• ReviewID (the primary key)
• InsertUserID
• DateInserted
• InsertIPAddress

The model will take care of this.

Update a single row:

$rm->setField(123, 'FieldA', 'test');

Update multiple fields of row 123

$rm->setField(123, [
    'FieldA' => 'bla',
    'Recommended' => 1,
    ...
]);

Update all rows where InsertUserID = 5

$rm->update([
    'Recommended' => 0,
    ...
], [
    'InsertUserID' => 5
]);

Delete all rows where InsertUserID = 5 and Recommended = 0

$rm->delete([
    'InsertUserID' => 5,
    'Recommended' => 0
]);

Get all reviews

$reviews = $rm->get();

Get the latest 30 reviews

$reviews = $rm->get('DateInserted', 'desc', 30);

Get the latest 30 reviews where InsertUserID = 5

$reviews = $rm->getWhere(['InsertUserID' => 5], 'DateInserted', 'desc', 30);

---

All of the above will work without writing any code specific to your data model.

Once you define more complex relationships between data, you would start extending these functions in your model.

rbrahmson

Thanks @R_J , very helpful. Very helpful.

Out of curiosity I got a theoretical question. I understand the MVC separation of data/view, etc. that led my desire to move from direct sql to the model. But how does that work with the hooks? What I am saying is that hooks are called from different places in Vanilla, so wouldn't I eventually be making model calls where vanilla does sql, and sql where Vanilla does model calls, practically breaking the separation?

rbrahmson

Wow, @Bleistivt - that's great. much appreciated. But my mind wonders about how this works with a normalized sql approach that adds two tables. For example, let's say I want to create a plugin (I really don't) that allows users add a line of "Observation" to the discussion. So I'd have two tables:
DiscussionObservations - ObservationID, DiscussionID
Observations - ObservationID, Observation

Creating these two is shown in your example above (unless there is a way to create two related tables).

But clearly adding a row to Observations require a more complex process:

• Adding a row to Observations
• Picking the new ObservationID and adding a row to DiscussionObservations

One can argue that from a programming convenience it would be nice to be able to have one call that does both add and update (and not having to check whether to create a new observation entry sets in the code layer that handles that).

Andof course, the same two-table handling is true for delete.

Last but not least (that may be answered in the Vanilla code that I've yet to inspect), is there a "filtered" getcount (how many, subject to field values)?

hgtonight

@rbrahmson said:
...there is one more sample skeleton I would benefit from: a plugin that uses a new model to access a new table.

For a simple case, you just define the table via the structure method, then pass the table name to the Gdn_Model constructor and use that. The base class provides everything you need for CRUD operations (create, request, update, delete).

E.g.

public function structure() {
  $Structure = Gdn::Structure();
  $Structure->Table('Media')
     ->PrimaryKey('MediaID')
     ->Column('Name', 'varchar(255)')
     ->Column('Type', 'varchar(128)')
     ->Column('Size', 'int(11)')
     ->Column('ImageWidth', 'usmallint', NULL)
     ->Column('ImageHeight', 'usmallint', NULL)
     ->Column('StorageMethod', 'varchar(24)', 'local')
     ->Column('Path', 'varchar(255)')
     ->Column('ThumbWidth', 'usmallint', NULL)
     ->Column('ThumbHeight', 'usmallint', NULL)
     ->Column('ThumbPath', 'varchar(255)', NULL)
     ->Column('InsertUserID', 'int(11)')
     ->Column('DateInserted', 'datetime')
     ->Column('ForeignID', 'int(11)', TRUE)
     ->Column('ForeignTable', 'varchar(24)', TRUE)
     ->Set(FALSE, FALSE);
}

public function base_render_before($sender) {
  $model = new Gdn_Model('Media');
  $record = array(
    'Name' => 'My First Media',
    'Type' => 'gif',
    'Size' => 123,
    'Path' => '/root/images',
    'ForeignID' => 1,
    'ForeignTable' => 'discussion',
  );
  $id = $model->insert($record); // inserts the record into the table and returns the id of the record
  $data = $model->get(); // data contains all records in the Media table
  $data = $model->getID($id); // returns single record from where column {tablename}ID equals $id
  $record['Path'] = '/home/user/new/path';
  $result = $model->update($record, $id); // update the record identified by $id with the fields in $record
}

For any methods that aren't defined in the base model, you should extend it. I have a bunch of models in Yaga. I would suggest checking out the badges:

• Structure: https://github.com/hgtonight/Application-Yaga/blob/4d99c42fd11ebf5b946d345b96438e9c59183882/settings/structure.php#L45-L56
• Model: https://github.com/hgtonight/Application-Yaga/blob/4d99c42fd11ebf5b946d345b96438e9c59183882/models/class.badgemodel.php
• Controller: https://github.com/hgtonight/Application-Yaga/blob/4d99c42fd11ebf5b946d345b96438e9c59183882/controllers/class.badgecontroller.php

R_J

@rbrahmson said:
Last but not least (that may be answered in the Vanilla code that I've yet to inspect), is there a "filtered" getcount (how many, subject to field values)?

You should really do so, since the answer is obvious: https://github.com/vanilla/vanilla/blob/master/library/core/class.model.php#L485

rbrahmson

Thanks @hgtonight. Very insightful. I wish there was a way to click on both Insightful and Awesome! ;-)

rbrahmson

@R_J wrote:

You should really do so, since the answer is obvious:

You are absolutely correct, and I'm planning to once I get to a large screen. I'm on my mobile, so I couldn't contain my curiosity/urge to ask...

R_J

Concerning your MVC question: everything starts at your controller. There is no difference if an event is fired in a model or a view. You have to consider, what you are doing. Imagine you are taking care for an event that is fired in a view.

If you like to add e.g. a class name based on some dynamic information, you would handle that completely in your controller.
If you would like to add some information from the database, you might find that some yourModel()->getWhere('DiscussionID' => $args['Discussion']->DiscussionID). And if the information you like to add is no trivial, you might want to get that added information from an external view.

Even if you hook into a models "beforeSave..." you might decide to present a very, very verbose error message to the user, and you could use a view for that.

It is not imported where an event is fired. The only thing that is important for your decision is what your code is doing.

Bleistivt

Once you have multiple tables, you will need to write your own methods, since the framework can't really guess what their relationship is.

One important property of Gdn_Model is that there is always an SQL property available, which is the same as calling Gdn::sql().

class ObservationModel extends Gdn_Model {

    public function __construct() {
        parent::__construct('Observation');
    }

    public function insert($fields) {
        $result = parent::insert($fields);

        $this->SQL->insert('DiscussionObservation', [
            'ObservationID' => $result['ObservationID'],
            'DiscussionID' => $fields['DiscussionID'],
        ]);

    } 

}

Of course Models can also talk to each other: You could create a DiscussionObservationModel and call it from ObservationModel::insert().

---

Yes, getCount will also take a $wheres array:

$userReviewCount = $rm->getCount(['InsertUserID' => 5]);

hgtonight

I love when I am writing a lengthy reply and two others have posted before I do.

Feels like the community is doing good when this happens.

rbrahmson

@Bleistivt wrote:

Once you have multiple tables, you will need to write your own methods, since the framework can't really guess what their relationship is.

I understand. Though to be honest, the simple case I jotted with two tables for normalization is so "standard" that it would have made sense to create methods for them (even with enforced table name [for the connecting table] convention).

Thanks for all the guidance!

rbrahmson

@Bleistivt wrote in Modifying existing table structure - best practices?

Also note that DatabaseStructure::Set() doesn't remove columns or drop tables by default (unless you pass true to both arguments).

Not sure what arguments you refer to. Basically I want to know how does one uses the model to delete a table in the Ondisable hook of a plugin (in a case where one does want to do so rather than leave data behind)?

R_J

I do not know how this could be put into a class that matches all thinkable cases. But you surely are free to try your hands on it. I've made a start (code untested and fugly) just trying to think what the delete() method would have to look like...

class DoubleModel {
    /** Instance of parent model */
    public $parent;

    /** Instance of child model */
    public $child;

    public function __construct($parent, $child) {
        $this->parent = new Gdn_model($parent);
        $this->child = new Gdn_model($child);

        // Analyze the table structure of the parent table, since we will need the primary key.
        $this->parent->defineSchema();
        // Analyze the table structure of the child since we must ensure that it has a column Parent.PrimaryKey.
        $this->child->defineSchema();

        // Parent.PrimaryKey must be part of child so that this class does know the relation.
        if (!$this->child->Schema->getField($this->parent->PrimaryKey)) {
            die; die; die;
        }
    }

    public function delete($where = '', $limit = false, $resetData = false) {
        $whereParent = [];
        $whereChild = [];
        if (is_numeric($where)) {
            $whereParent = [$this->parent->PrimaryKey => $where];
            $whereChild = $whereParent;
        } else {
            // Parse $where array. If key is like "Table.Column" "Table" must be either parent->Name or child->Name. If key is only "Column", assume it is column in the parent table (or in both? not sure about that..).
        }

        // We need to know which children should be deleted. So before deleting the parent, we get their IDs.
        if (!array_key_exists($this->parent->PrimaryKey, $whereChild) {
            $parents = $this->parent->getWhere($whereParent, $limit)->resultArray();
            $whereChild[$this->parent->PrimaryKey] = array_column($parents, $this->parent->PrimaryKey);
        }


        if ($resetData) {
            $this->child->SQL->delete($this->child->Name, $whereChild);
            $result = $this->parent->SQL->delete($this->parent->Name, $where, $limit);

        } else {
            $this->child->SQL->noReset()->delete($this->child->Name, $whereChild);
            $result = $this->parent->SQL->noReset()->delete($this->parent->Name, $where, $limit);
        }
        return $result;
    }
}

rbrahmson

Ah!, so it's not implemented internally (suspected so from https://github.com/vanilla/vanilla/blob/master/library/database/class.databasestructure.php#L304-L308)

rbrahmson

To keep the information in one place I'm pasting @Bleistivt response from another discussion:

This will add a column to the discussion table:

Gdn::structure()
  ->table('Discussion')
  ->column('IsSave', 'tinyint(1)')
  ->set(false, false);

This will remove every column in Discussion table and add the column:

Gdn::structure()
  ->table('Discussion')
  ->column('IsSave', 'tinyint(1)')
  ->set(true, false);

This will drop/delete the Discussion table and then recreate it with only what is specified below:

Gdn::structure()
  ->table('Discussion')
  ->column('IsSave', 'tinyint(1)')
  ->set(true, true);

But all those set() examples have in common, that at the end of the call there is still a table "Discussion".

Gdn::structure()->table('TableName')->drop(); will kill your table once and for all ("Discussion" wouldn't be wise )

rbrahmson

Folder structure convention question -- is there one for the plugin (the same way design/xxx.css, locale/en-CA/definitions.php, modules/class.somemodule.php) to name and save the model?

R_J

@Bleistivt said:
@rbrahmson

You would start by creating a new class.reviewmodel.php.
Note that it extends Gdn_Model. You call the constructor with the name of the table ("Review"):

<?php

class ReviewModel extends Gdn_Model {

    public function __construct() {
        parent::__construct('Review');
    }

}

And that's it, you have created your first model!

This is how you create a new instance:

$rm = new ReviewModel();

Just put that in the same folder as your plugin file.

rbrahmson

Thanks!