Models
Almost every API will need a solid database to interact with. In fact, the primary focus of most APIs is the
database. In most frameworks, each model is a one to one relationship with a table in the database. We wanted to
keep this relationship and expand on it. We took most of the queries people write and created methods for them,
so we can do a lot of the dirty work behind the scenes, leaving you with simple calls to interact with your database.
It all starts with the table design. By using a standardized naming convention and forcing a few fields to be in every table,
we can reduce the amount of code you write by quite a bit. We didn't want to create an ORM where
you never write SQL and have to basically learn a new language, and have no idea what SQL is being written behind the scenes.
Instead, we take the common things you do like select single records, multiple records, delete, insert, update,
and archive, and make these things extremely simple. Everything else, you can still write custom queries.
One question you will probably have, is doing inner joins, where should the query go? This is completely up to you. If we
are joining user/roles/user_roles in a single query, depending on the purpose of the query, you could make a case that it could
go in any of those 3 models, so we leave it up to you.
Creating MYSQL Tables
Each table that you want to attach a model to needs to have these 7 fields. Every table and field "must" be lower case with underscores. Example: user_roles (user_id, role_name).
id int(11) NOT NULL AUTO_INCREMENT, inserted_date datetime DEFAULT NULL, updated_date datetime DEFAULT NULL, archived_date datetime DEFAULT NULL, inserted_by int(11) DEFAULT NULL, updated_by int(11) DEFAULT NULL, archived_by int(11) DEFAULT NULL,
Creating A New Model
Using the UserModel as an example, you would create the file Models/UserModel.php.
The code below is all it takes to create a new model. You now have access to all of the built in model features.
All you have to do is set the table name, add your validation for each field, and you are good to go. All of the
custom user queries you need to write, you can add to the bottom of this file.
//UserModel.php //This is the example model namespace SlenderApi\Models; use SlenderApi\Models as Models; class UserModel extends Models\Model { public function __construct(object $db, ?int $authenticatedUserId) { //Important to set the name of the table here, which in this case is user. parent::__construct('user', $db, $authenticatedUserId); } //Set your validation for each field. //Look in the Validation docs for more validation rules. public static function getValidationRules(): array { return array( 'id' => 'isint', 'email_address' => 'email|maxlength:200|minlength:3', 'password' => 'required|minlength:8|maxlength:20', 'user_name' => 'required|maxlength:20|alphanumeric|minlength:3', 'first_name' => 'maxlength:100', 'last_name' => 'maxlength:100', ); } }
select()
This is used for performing a basic select on a single table. Create your method in your model, then call it from your libraries.
Example: $mySelectData = $userModel->selectUserByLoginUsername($emailAddress);
public function selectUserByLoginUsername(string $emailAddress): array
{
//List the fields you want to select.
$fields = 'id, email_address, user_name, password, first_name, last_name';
//Build your where condition.
$cond = 'WHERE email_address = :email_address AND disabled = 0 AND archived_date IS NULL';
//Create an array of values to pass to the where condition.
$values = array(
'email_address' => $emailAddress
);
//call select() and return your results.
return $this->select($cond, $values, $fields);
}
selectIntValueWhere()
Use this method to select a single record that is an integer with a custom where condition.
Example: $testModel->selectIdByCode('test');
public function selectIdByCode(string $code): int
{
$field = 'id';
$cond = 'WHERE code = :code';
$values = array(
'code' => $code
);
return $this->selectIntValueWhere($field, $cond, $values);
}
selectStrValueWhere()
Use this method to select a single record containing a string value with a custom where condition.
Example: $testModel->getPasswordById($id);
public function getPasswordById(int $id): string
{
$field = 'password';
$cond = 'WHERE id = :id';
$values = array(
'id' => $id
);
return $this->selectStrValueWhere($field, $cond, $values);
}
deleteWhere()
Use this method to delete records from your table using a custom where condition.
Example: $testModel->deleteRecordsWhereNameLike('%abr%', '%lin%');
public function deleteRecordsWhereNameLike(string $firstName, string $lastName): void
{
//You can do like, as long as you surround the values with %.
$cond = 'WHERE first_name like :first_name and last_name like :last_name';
$values = array(
'first_name' => $firstName,
'last_name' => '$lastName
);
$this->deleteWhere($cond, $values);
}
archiveWhere()
Use this method to archive/soft delete records from your table using a custom where condition. This will
set the archived_by and archived_date in your table, using the logged in user id.
Example: $testModel->archiveById($id);
public function archiveById(int $id): void
{
$cond = 'WHERE id = :id';
$values = array(
'id' => $id
);
$this->archiveWhere($cond, $values);
}
customQuery()
If you want to join multiple tables or do a special insert, update, or delete, you can write custom queries.
Example: $roles = $userModel->getRolesByUserId($userId);
public function getRolesByUserId(int $userId): array
{
$query = '
SELECT role.name
FROM role
INNER JOIN user_role
ON user_role.role_id = role.id
AND user_role.user_id = :user_id
AND user_role.archived_date IS NULL
WHERE role.archived_date IS NULL
GROUP BY role.name
ORDER BY role.name
';
$values = array(
'user_id' => $userId
);
return $this->customQuery($query, $values);
}
save()
The save method is one of the biggest time savers in the history of coding with databases. Pass in any array of data,
If the fields in the array match the fields in the table, they will get saved. In fact, you could have a form on your
UI with 3 user fields, first name, last name, email. The user table might have 30 fields. You can pass an object with those
3 fields to your API, run your validation and security checks, the pass that data straight to the save function with their id.
That record will be updated and return a success response. Maybe those are the only 3 required field for adding a user,
pass them in without an id and it will insert the record.
Imagine you have 8 different places user information can be updated
on your UI. Are you going to write 8 different update statements, custom coding each one, some that may be updating 30 fields or more?
Think about the size of an enterprise application and how many different custom insert and update statements
would need to be written. It's still easy to debug, you can output the exact query being executed,
which is a basic insert or update statement.
$parameters = array(
'first_name' => $requestParameters->firstName,
'last_name' => $requestParameters->lastName,
'email' => $requestParameters->email,
'dummy_field' >= 'this will be ignored'
);
$userModel->save($parameters, $requestParameters->id);
executeSp()
If you need to execute a stored procedure, use this method.
Example: $stats = $userModel->generateUserStats($userId);
public function generateUserStats(int $userId): array
{
$values = array(
'user_id' => $userId
);
//Specify the name of the stored procedure and pass in the parameters.
return $this->executeSp('spGenerateUserStats', $values);
}
getTableCount()
If you need to get a simple row count, you can use this method by passing in a custom where condition.
Example: $userCount = $userModel->getUsersGreaterThanId(10);
public function getUsersGreaterThanId(int $userId): int
{
$cond = 'WHERE id > :id';
$values = array(
'user_id' => $userId
);
return $this->getTableCount($cond, $values);
}
exists()
If you want to see if any records exist for a given where condition, use this method. Example: $usersExist = $userModel->usersThatExistGreaterThanId(10);
public function usersThatExistGreaterThanId(int $userId): bool
{
$cond = 'WHERE id > :id';
$values = array(
'user_id' => $userId
);
return $this->exists($cond, $values);
}
Saves And Validation Combo - Example
Since you have set your validation
rules for this model already, in your library classes, you can run validation on any array of
key value pairs and return errors with a couple lines of code. For saving, call the model->save
method and pass it any array of key value pairs. In both validation and the save, all fields in your
array that do not exist in the table will be ignored. This allows you to save any number of fields
at a single time. In the example case of users, there could be many different forms created on your
UI for updating only a few user fields at a time.
Instead of writing custom inserts and updates for users,
just call the save method and pass the array in. If you are doing an insert, leave out the $id and it will
insert the record and return the new id. If you are doing an insert, all required fields should be passed to
the validation and to the save in order to avoid getting a SQL error.
//UserLib.php $userModel = new UserModel($this->db, null); //Access your validation rules. $validationRules = $userModel->getValidationRules(); //Let's say you have a form with just first and last name. $requestParameters = $this->request->getParameters(); //Create a custom array with only the first and last name. //In case the form had other values it passed through, we only want these 2 fields. $parameters = array( 'first_name' => $requestParameters->firstName, 'last_name' => $requestParameters->lastName ); //Pass the array to the validation. //Notice that user_name is required. //Since it's not passed into the validation, it will be ignored. $errors = Validation::getErrors($parameters, $validationRules); if (empty($errors)) { //If the validation catches anything, it will output the errors and die. $this->response->outputResponse(400, null, $errors); } //All validation passed, so save the data. //If you were to add another field to the $parameters array, it would be ignored. //The save method will auto update updated_date and updated_by. $userModel->save($parameters, $requestParameters->id); //To do an insert, leave out the id or set it to 0. $newId = $userModel->save($parameters);