Download the PHP package ongom/dite-orm without Composer

DiteORM.

This is PHP ORM for interacting with relational database like Mysql, Sqlite, Posgre, Oracle etc. Currently, it supports only Sqlite, Mysql, Sqlserver and Postgresql databases. It allows us to keep oursevles within only PHP code instead of switching between sql and PHP code.

DiteORM uses functions and classes to generate the right sql which is needed. This means sometimes you have to adjust the setting in the php.ini to fit the database you are using.

Installation.

This can be done using one of this two ways;

1 - Download this github repository and extract it into the root of your project.

2 - Using composer by running composer require dite-orm at the root of you project.

Setup.

To get started, create a .env file at the root of your project, this is where you will add some configaration settings for dite-orm.

Setup for sqlite.

• DRIVER is the type of database management system you are using. The value is sqlite.
• DATABASE_NAME is the name of the database you are using. The value is the name of your database like 'school_database'.

Setup for mysql, sqlserver, postgre.

First create the database example 'schooldb' then add the following code to the .env.

• DRIVER is the type of database management system you are using. List of posible values are;
  • sqlite
  • mysql
  • sqlsever
  • postgresql or pgsql
• DATABASE_NAME is the name of the database you are using. The value is the name of your database like 'school_database'.
• SERVER_NAME is the server name or port for example 'localhost' of 3605.

• USER_NAME is the user name for example 'root'.

• DATABASE_PASSWORD is the password of the database example '23R42'.

Other configarations that can be added to .env file.

• LOGGER is for debuging purpose.
  • 1 means you want to print queries and messeges on the screen, this should be only used in development.
  • 0 or any other number means no printing queries or messeges on the screen, it is used for production.
• RUN_SCHEMA. When you try to create the table using the Schema, the table will not be created, to solve this add RUN_SCHEMA = 1 to the .env file. After creating the tables you can turn the value 0 (RUN_SCHEMA = 0) or remove it completely to avoid rerunning the queries for creating the table again. When you add any new tables, you will have to turn it back to 1 and again run the code.
• FETCH_MODE defines how the records are going to be fetched from the database.
  • std_arrays means that the records will be fetched as a php standard array whose values can be access as shown bellow.

• SQL_COLOR definds the color of sql keywords when printing to the screen.

  • std_obj means records will be fetched as stdClass object which can be access using arrow syntax as below.

  • If you don't spacify this in the .env file, it will default to std_obj.

Creating tables.

Before any step you first need to autoload the outoload.php from vendor folder.

Creating tables can be done in two ways.

• Using existing database / other softwares (mysql workbench, navycat, DB browser, etc. ) to create.
• Using Dite Schema.

1. Using existing database or other softwares to create the database.

When using other software like myql workbench or PHPmyadmin, all you need to care about is the primary key field. Primary key field name is got from the name of the table written in lowercase. ie

• Users pk feild will be users_id
• Blog_Post pk feild will be blog_post_id
• Prices pk feild will be Prices_id

Then the model classes(representing each table) are defined like below.

2. Using Dite **Schema to create the database.**

Add the following code below the require statement.

The above code defines a table called users with the following fields.

• users_id - integer type, primary key, autoincrement and not null.
• name - varchar(255).
• gender - enum that accept only male or female as values.
• email - unique and not null.
• age - int and not null.
• created_at - default current timestamp.
• updated_at - default current timestamp that updateds when the record updates.

You can go ahead and add a post table. Below is the overall code on how to create three tables (users, posts, status) including foreign keys.

Note:

• We have instantiated the classes starting with Status.This is because the status table is being referenced by Posts in the foreign key fieled and the table have to be created first before its is referenced.Tables that are being reference must be above. If you change the order like below, you will get a foriegn key error mostly in mysql.

Run the code by openinng your file in the browser. This will create the table in the database.

• You can also use Users::class or 'Users' for table name called users and so for other tables.

After the tables has bean created, You can open .env and change RUN_SCHEMA = 0 or else the tables will try to be recreated.

1. Creating an intermidate table.

The intemediate table has a convention of creating it inorder for Dite to understand. You have to concatenate the two table names. For example teachers and courses tables, the intermediate table will be teachers_courses and the primary key feild will be teachers_courses_id . The intermediate table must be created like below.

Meaning of each of the methods used for building the table.

• id() - Defines an autoincrementing primary id feild and and set it not null. You can not chain any method on to id().

• string() -Sql varchar feild. It takes in two parameter, one mandatory string parameter(field name like comments), second optional integers parameter which defaults to 255 (max length of the charactors accepted).

• text() - Sql text field. It takes in two parameter, one mandatory string parameter(field name like comments), second optional integers parameter which defaults to 65535 (max length of the charactors accepted).

• longText() - Sql text field. It takes in one mandatory string parameter(field name like Posts).

• int() - Sql integer field. It takes in one mandatory string parameter(field name like comments).

• bigint() -Sql big interger field. It takes in one mandatory string parameter(field name like comments).

• enum() - It takes in two mandatory parameter, one string parameter(field name like size), second optional array parameter(only values ccepted).Tis method does not work for postgre sql.

• unsigned() - Sql unsign datatype field. It take in field name

• boolean() - Sql boolean datatype field(true or false). It take in field name.

• float() - Sql floating point datatype field. It take in field name

• double() -Sql double datatype field. It take field in name.

• decimal() -Sql decimal datatype field. It take in field name. This may not be avilable in other DRIVER

• year() -Sql year datatype field. It take in field name.

• timestamp() -Sql timestamp. It will create the created_at and updated_at columns.

• sql() -This takes in a string parameter, the query for creating a table. You must not chain anything on to this method

• foreignKey() -Sql foreign key field. It take name the feild being reference, no need for table name since sparkes can figure it out.

Field constrains

• notnull() -Sql NOT NULL constrain. You can not do this on id() method..

• unique() -Sql UNIQUE constrain.

• cascade() -This is chained only on foriegn ky feilds and it is optional. Its sets ON DELETE and ON UPDATE constrian to CASCADE.

• cascade() -This is chained only on foriegn key feilds and it is optional. It sets ON DELETE and ON UPDATE constrian to CASCADE.

• restrict() -It sets ON DELETE and ON UPDATE constrian to RESTRICT.

• setnull() -It sets ON DELETE and ON UPDATE constrian to SET NULL.

• noaction() -It sets ON DELETE and ON UPDATE constrian to NO ACTION.

  You can also set this costrain one by one as shown below

• cascadeDelete() -It sets ON DELETE CASCADE.

• cascadeUpdate() -It sets ON UPDATE CASCADE.

• restrictDelete() -It sets ON DELETE RESTRICT.

• restrictUpdate() -It sets ON UPDATE RESTRICT.

• setnullDelete() -It sets ON DELETE SET NULL.

• setnullUpdate() -It sets ON UPDATE SET NULL.

• noactionDelete() -It sets ON DELETE NOACTION.

• noactionUpdate() -It sets ON UPDATE NOACTION.

  Below is a very valid chain.

Querying the database.

This section will teach us how to create, read, update and delete record.

Create new record.

Creating a new user into users table as shown below.

Don't pass the primary key feild, created_at and updated_at feild because they get feild up automatically.

The create method returns the new record created only if you are creating a single record.

Updating record.

The static method update() and updateMany() are used, they takes in two paramters, the record you want to update (id or selector) the and associative array of the new values

The update method returns the new updated record. The updateMany method returns void.

Deleting record.

Static method delete will delete a record by it's id.

Deleting many records.

Static method deleteMany will delete all the record that match the where clouse passed as paramete.

NB:

Part of the query that makes the where clause is passed as a parameter to the method

Counting the number of records that matcht the query.

Static method countRecords() is used for Counting the number of records that match the query.

Reading records from the database.

This can be done using many methods which are all discussed below. All static methods without chaining functionality that are used for reading records takes in atleast two optional parameters, the where clause array/int and the feilds you want back. The fields you want back can be passed as comma separated string value of the columns you want back OR as an array of all the feilds you want back. As shown below

• all().

  This is a static method that gets all the records that matches the query.

• first().

  This is a static method that gets the first records that matches the query.

• last().

  This is a static method that gets the last records that matches the query.

• findById().

  This is a static method that gets the records by its primary id.

• findOne().

  This is a method that gets records that matches the query.

• exist().

  This is a method that checks if the record exists. It returns a true or false

• sql().

  This is a static method inherited from Model class. It takes in two parameter, An sql query and the values you want to bind to the query. This can be usefull when writing a much complext query.

join

There are several ways of joining two tables.

• joins() and innerJoins().

  This two methods do the same thing , they will inner join the two the tables.

There are methods for other type of joins.

• leftJoins() for left joins.

• rightJoins() for right joins.

  So far we have looked at only static methods without chaining, Let us look at other chaining functionality available.

These methods end with get() and where clause is either passed as parameter in array or integer form or chain where() method but not both

::findByPk().

For getting a single record by primary key, it works like the atatic method findById() record fro a table, it works like all()

find().

For getting all the records from a table, it works like all()

There are few more methods you can chain onto the method find() as described below.

orderBy().

Will sort the result in descending or asscending order . The values can be asc or desc for ascending and descending order respectively.

groupBy()

This will group the result by the suplied feild. groupBy() takes in a string parameter.

select().

This will select only the spacified feilds. The parameter is either string or arrays.

limit().

This is used to spacify the number of record you want to fetch. It defaults to 12

skip() or offset().

Both of these do the same thing. They are used to spacify the number of records that will be skiped. It defaults to 0

A combination of skip and limit can be used for paginating your result

pagination.

Pagination helps to query only a slice of records from the database. It has two methods;

• page() - Takes in the page you want
• perpage() - Takes number of records for each page.

The above query will return something like below

Using Model class to query data.

Pass the name of the table to the model constrctor if you want to query using the Model class

You can chain any valid method like select, join, group, etc.

Joining tables.

join().

Earlier we looked at joins but we were able to join only two tables using the static methods, now lets join more than two tabes. This are the different types of joins which are avaiable; join(), innerJoin, leftJoin(), rightJoin().

Three tables will be involved in this join, user, post and comments. You can join over 20 differnt tables together using any of the above types of join and apply pagination, select, border by , etc like below

• find() and findBypk can not be chained together, you will get an error
• page() and perpage() methods will activate pagination

joinOn

This will require you to pass the name of the toble you are joining and the condition on which you are joining

There are also other methods for joining like;

• leftJoinOn()
• rightJoinOn()
• innerJoinOn()
• joinOn()

Where clause.

The where clause is passed as a parameter in the following methods.

• ::all()
• ::findOne()
• ::findbyId()
• ::first()
• ::last()
• ::delete()
• ::deleteMany()
• ::countRecords()
• ::exist()
• ::findBypk()

you can also chain the where() method on the following methods

• ::find()
• ::table()
• ::findBypk()
• ::hasMany()
• ::hasOne()
• ::hasManyThrough()

It can be passed in the following ways

1. Passing an integer.

When you pass an integer to methods like findById() or findBypk() or delete(), the integer is primary id of the of the record you will get back

The above code will return a single record whose primary id is 2.

2. Passing an associative array.

When you pass an associative array to methods like findOne() or all(), the array generate the query as bellow

3. Passing nested associative array.

Sometimes you want to apply operators like <, >, <=, >=, =, like, etc. this is done the following ways

Instead of using ':', you can use '$', for example, the output of this code is the same.

4. Passing associative array where key is $and or :and.

This will only write queries in which the where clause is separated by AND.

5. Passing associative array where key is $or or :or.

This will only write queries in which the where clause is separated by OR.

6. Passing associative array where key is $nand or :nand.

This will negate the entire :and.

7. Passing associative array where key is $nor or :nor.

This will negate the entire :nor.

The same way, putting n just after $ or : in the operator will negate that part of the query,

Passing the same column name more than one times.

If the same key is going to appear more than once, normally associative array will only pick up the key which is written last, to deal with this kind of behavior , you have to append a leading underscore (_) before the column name in the array key, this is demonstrated below.

You will have to append many underscores if the column name is repeating many times in that same associative array.

List of available operators for the where clause.

Relationships.

This will establish connection between some tables, for example if you have a post, you can easily get its comments, or if you have a user you can get all his posts. There are three types of relationships you can use here,

• One To One relationship.
• One To Many relationship.
• Many To Many relationship.

1. One To One relationship.

One user has one credit card and a credit card belongs to one user. To establish a One To One relationship here , you have to create one function in the Users class and CreditCards calss defination as shonw below.

2. One To Many relationship.

The code will be as below.

3. Many To Many relationship.

This will only work if you had created an intermediate table for the two tables. The intemediate table has a convention of creating it inorder for Dite to understand. You have to concatenate the two table names. For example teachers and courses tables, the intermediate table will be teachers_courses and the primary key feild will be teachers_courses_id .The intermediate tabble must be created like below.

Each time you create a teacher or a course remember to update the intermediate table.

Let's define the relationship.

NB On to any relationship, you can chain any valid method chain exept ->findByPk()

Dropping database table

This is done using the static method drop() like below. It returns a boolean , true for successful deleting and false for failure to delete the table.

Thanks from Dite