Learning PHP Active Record

I have been using PHP ActiveRecord for the last 5 years but recently their website stopped working so I thought to create this tutorial for those who are or want to use this beautiful library for managing their database in PHP.

Brief Introduction:

  • It’s an ORM (Object Relational Mapper) and it lets a programmer interact with the database without even writing actual query directly which eventually saves a lot of time
  • It’s based on Singleton Pattern i.e. you don’t have to worry about the architecture because you have the best one
  • Can be integrated with any project, we use it frequently with CodeIgniter for our day to day development activities
  • Supports MySQL, PostgreSQL, Oracle, and SQLite. We mainly use it for MySQL and would recommend it, if you want to simply download it and use
  • It’s based on MVC(Model-View-Controller) architecture and has the potential to be used for large projects

Getting Started

You can clone/download this library from https://github.com/jpfuentes2/php-activerecord and initialize this library just by using the code below

What we have done here is;

  • Specify Model Directory: This is going to serve as the directory where all our model classes are going to stay
  • Specify Different Connections: You can define different connections at the time of initialization. It’s helpful especially when you have one server and you want to develop on one database, test on another one and live on different one. You can pass even one connection also by removing the other ones
  • Set Default Connection: This line particularly tells your code as to which database should be used to establish the connection with

You can provide different settings here as well, such as; timezone, date & time format etc. but we will talk about it in different tutorials

After initializing the library, you can just go to your model directory and start creating models there. One sample model is below:

and that’s it!! Isn’t it easy?

A couple of things to notice here is, you should follow some simple naming conventions in order to achieve better results by writing minimum lines of code. Note the following things:

  • Your database table names should always be plural: In the above case the table name would be users
  • Your model name should be singular: Check the above code snippet
  • The primary key should always be id and the foreign key should always be “table_singular_name_id”: In the above case, if we want to refer to this “users” table in another one, then you would be using “user_id”

Defining Relations

Your project is going to have different tables and you will have to write complex queries by probably joining them. Even if you don’t join tables in a single query, you will probably have to refer to different tables quite often.

ActiveRecord library deals with this situation very nicely by providing; a) Relation Mechanism b) Lazy Query. Let’s check each method separately.

Relation Mechanism

It’s a way to tell your models as to how are they related to different models in plain English and it’s necessary to do that because you don’t wanna write complex queries, right?

One to many relationships

Let’s consider that we have two tables “users” and “orders” and the scenario is, A user can have multiple orders.

Look at this code carefully and observe a couple of things. In the User class, we are defining “has_many” relationship and calling Order class as “orders” and in the Order class, we are defining “belongs_to” relationship and calling User class as “user”.

Naming conventions can save a hell lot of time for you

One to one relationship

In the above scenario, let us consider that if each user could only have one order then? Let’s see

Check the naming conventions again.

Apart from the naming conventions, there is one important thing to see that “belongs_to” relationship exists in both one-to-many and one-to-one relationships!! It’s because the “orders” table has one column “user_id” in both scenarios. What it means is, each child will have belongs-to the parent relationship.

Many to many relationship

Let’s take another scenario; We have several books and authors in our database and that each book can have multiple authors, as well as each author, can have multiple books. This relationship is called a many-to-many relationship where each entity/table can multiple references in the other table.

Look at the “through” keyword. It tells both tables that they are connected to each other with the help of a third table.

By now, you should’ve got a clear understanding as to how can you define the relationships between tables and if not then I would suggest, you read the relationship section again and try to create above tables in your database and comment in the comment section. I will try to answer

Writing Queries

Basic Finder Queries

There are different finder queries available with ActiveRecord. Let us see them below:

Query to create & update

Deleting a record

Joining Tables & Lazy Queries

Here I will try to explain the join queries and lazy queries. Note, that these queries will run effectively only when a proper relationship is established between the tables. Check the code below to understand it properly. I’ve taken previous relationship references to explain it so if you are still not familiar with it, I would recommend to go up and read it again

Other Important Methods

In my opinion, if you can learn from above examples your most of the projects can be developed very easily without writing, however, if you still want/have to write your own queries, you can do so as following:

but first, try to utilize the inbuilt queries and you can see that I have not written a single line of SQL so this will save a lot of time in writing raw SQL queries and you can focus more on writing other important logics.

 

Let me know in the comment section if this tutorial helped you.

In the end, I would like to thank php-activerecord programmers for building such an elegant and beautiful library.

Back to Top