Database operations

Database Relationships

Database relationships are maintained through the communication of active user sessions with the database system. This implies that an existing database must be actively acessible which requires a pre-configuration of the database connection parameters in the default database configuration file. In spoova, there are three main types of database relationships which are the one-to-one, one-to-many and many-to-many relationships. These relationships makes it easier to interact with the database through the build-up of more concise, well organized and easily maintainable code structure. Here, most of the database operations are executed through automatically generated sql queries which are dependent on the data structure and how they expected to be managed. The format in which structures are defined in relation with session accounts which makes it easier to process queries are otherwise termed as database models. These models forms a building block for performing database operations. In spoova, the root Model class manages all user relationships with the database. All child models that are extended to the root Model class will inherit the essential properties needed for performing database operations. The following are helper methods within the Model class.

Model::where()
Model::read()
Model::update()
Model::delete()
Model::ofUser()
Model::of()

The methods above are basic helper methods within the model class for writing, reading, updating or deletion set of data from the database. They are mostly used to manage or control the flow of existing or stored database information. Each of these methods are discussed below in order to provide a much better explanation on the function which they perform.

▣Model::where()

This method is used to set a situation where a particular condition is met. It is usually called upon the model name itself. Any of the read(), update() and delete() methods can be applied upon it. Assuming we have a model class User as below:
1a - User model
```
  <?php 

  namespace spoova/mi/windows/Models;

  class User extends Model {

    function __construct(){

    }

  } 
                                        
```
Since the class above is a model, we can access the where() method from a route as shown below:
Example 1b - Using where() method of a model
```
  <?php 

  namespace spoova/mi/windows/Routes;

  use spoova/mi/windows/Models/User;

  class SomeRoute{

    function __construct() {

        User::where('id = ?', [1])->read()->User; // read user where id is one
        User::where('id = ?', [1])->delete(); // delete user where id is 1
        User::where('id = ?', [1])->update(['firstname'=>'Felix']); // update user where id is 1, set firstname as "Felix"
        
    }

  }
                                        
```
The examples above, are cases in which the where() condition can be applied. The first argument contains list of fields and placeholders for binded parameters while the second argument contains a list of binded parameter values. The assumed table name will be "users". The table name can be modified by re-defining a tableName() method on the model class which must return a string of the custom table name.
▣read()

This method is used to retrieve data from database. It takes two arguments. The first argument (string or array) is the number of selected columns to return while the second argument (array) defines the limit of data to be returned. Using Example 1a and 1b as reference, the Example 2 below describes how to use the read() method.
Example 2 - Using read() method
```
  User::read()->User; // fetch all user data

  User::read(['username'])->User; // fetch only the username of every user

  User::read(['firstname', 'lastname'], [10])->User; // fetch firstname and lastname of 10 users
                        
```
update()

This update() method is used to update a selected record. It takes a single array parameter. Which contains key(field name) and value(new) pairs. When a condition is not set upon it, all records will be updated:
Example 3 - Using update() method
```
  Posts::update(['date' => 2025-11-07 ]) //update all posts records, set all date rows as "2025-11-07"
    
```
It is generally advised to turn off live server when performing operations that modifies the database records to prevent auto-execution of queries.
delete()

This delete() method takes a single parameter which can either be a bool of true or an integer limit of number of data to be deleted. The limit may not be applicable on multiple table. Calling this method without a condition can be dangerous as all records belonging to the relative database table may get deleted. The bool argument of "true" ensures that a developer is aware of the changes they are about to make (i.e deleting all records) before making them. If no argument is supplied, and no condition is set on the delete method, this method will not delete any data. It is also advised to to keep the live server off if this method will be applied.
Example 4 - Using delete() method
```
  Posts::delete(true); //delete all posts
    
```

▣OfUser()

The ofUser() method is used to pull a data of the current user session account. The database default structure format demands that any table owned by the current user must have a user_id foreign key column that is mapped to the id field on the user's table. When data are obtained using models, ofUser() makes it possible to pull only data related to the current user. For example: If a database table "user" contains an id (primary key) column, then a table (comments) must have a field name with user_id. The user_id is then used to pull data from the comments table which belongs to the current session user.
Example 5a - PostModel.php
```
  <?php

    namespace spoova\mi\core\class\Models;
    use Models;

    Posts extends Models {       


    }

    public static function tableName() {
        return 'Posts';
    }

  }
                        
```
In Example above, a post model was extended to the Models class with the table name "Posts". When data is pulled from this class, it uses the "Posts" database table. If the ofUser() static method is applied on "Posts" (i.e Posts::ofUser() ), then spoova will try to find the Posts related to the current user id by looking for a user_id field in the Posts table. By default, this method uses the current user id to pull data, however, this can be modified. The ofUser() method takes its first argument as an integer. This integer is the id of the user whose data must be pulled from the database. For example:
Example 5b - Home.php (route)
```
  <?php

    namespace spoova\mi\windows\Routes;

    use Window
    use spoova\mi\windows\Models\Posts;

    Home extends UserFrame {       

        function __construct() {

            $currentUserPosts = Posts::ofUser()->read()->Posts;

            $customUserPosts = Posts::ofUser(2)->read()->Posts;

        }

    }

  }
    
```
In Example above, a post model was used to obtain data of the current user or a custom user just by setting the user id. This is by far the easiest way to pull data from database without writing any query. Spoova will run its queries internally to pull respective data from the database then store it using the current model name. It should be noted that the read() method is used to read data from the database while the ->Posts is the current Model name which stores the data obtained as a traversable object. When an error occurs, the last error is saved and can be obtained using the read()->DBError property. In the event that the foreignKey field name is not user_id, then a second argument can be supplied into the ofUser() method to define a new key name, In this relationship, the user database table (i.e user) will be the owner while the Model (e.g Posts) table is being owned. We don't need to set the user table since that has been done in the icore/init file during installation.
▣Of()

The of() method is similar to the ofUser() method. In this method, the table name can be customized if the relationship is between a model and any other database table. The first argument takes a new database table name while other arguments follows the ForeignId and ForeignKey structure respectively. By default, if the owner database table for example is "admin", then a owned table "posts" must have an "admin_id" foreign key field while the owner table must have an "id" local key field which helps spoova to naturally connect the two fields. For example:
Example 6 - Child of Parent table
```
  Posts::of('admin', 3)->read()->Posts;
                                
```
In the Example above, the posts database table will look for posts where admin_id is 3. It should be noted that the "admin_id" foreign key is generated from the combination of the singular form of the owner table along with an "id" local key by default. This means that for example, if the owner table was "admin" or "admins", then the default generated foreign key will be "admin_id". This is done by stripping off the last "s" character of the field name. This means that for a string with double "ss" last characters, one will be removed while the other will remain.
Example 7 - Child with custom foreign key of Parent table
```
  Posts::of('admin', 3, 'user_id')->read()->Posts;
                        
```
The example above is of a more complex relationship between database tables in which the foreign key is a custom user_id field. It is assumed that the custom foreign key supplied in this case is related to the admin table's local key "id". This means that the admin table must have an "id" primary key field.
▣bind()
The bind() method is a method that can be called upon the model's of() method. The bind() method is used to set up a connection between three database fields. In this connection, the binded table is the highest table while the current model is the lowest table. The database table structures resemble the format below:

A

----

B

----

C

The code chain structure, however resembles the format below:

A

--

C

------

B
The description for the model structure above is listed below:
- "A" is a child table to "C" while "C" is a child to binded table "B". Hence, "C" table is the link table or bridge between "A" and "B".
- This connection assumes that if table "A" is a child table, then table "C" is a parent table while table "B" is a grand table.
In order to set up this connection, the table "A" must have a Foreign key field name "C_id" (modifiable) relative to its direct parent "C" and the table "C" must have a Foreign key name "B_id" (non-modifiable) relative to its direct parent "B". Once the connection is successfully chained, we can proceed to obtain our data through the use of the current model's property which must be initialized with a capital letter case. The code below is an example of this connection.
Assuming we have a table structure as below:
```
  comments 
    -id
    -post_id 

  posts 
    -id 
    -user_id 

  user 
    -id
                        
```
In the table structure above, we can link to the owner of the comment's post through the post id.
```
  Comments::of('posts', 3)->bind('users')->read()->Comments;
                        
```
The setup above assumes that, the comments table has a foreign key field of post_id while the posts table has a foreign key field of user_id. From the sample above, the comment table will look within itself for where post_id foreign key is equivalent to posts table local key "id" 3. then the posts table will bind to its own parent "users" through the parent foreign key field name "user_id". This relationship can thus be defined as a complex relationship, one that is defined for a Child, Parent and GrandParent. In the event that the foreign key of the post table on comments table above is not post_id, this can be modified by supplying a third argument of the foreign key field name on the of() method.
```
  Comments::of('posts', 3, 'foreignKey')->bind('users')->read()->Comments;
                        
```

Docs Database Relationships