PHP Design Patterns: Active Record
Tags: php, designpatterns
Published: 2023-09-25 22:45:37

## What is Active Record?

Active Record is a design pattern that features a class that has both persistence methods and business methods.From the point of view of Object Orientation, this class ends up mixing many responsibilities, thus breaking the principle of single responsibility.


![Class Active Record](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zxqwpenqjv9sborg8gsc.png)

So one way to solve this “problem” is to create a parent class that implements the persistence methods in a generic way, finally having a separation between the business methods and the persistence methods, this design pattern is known as **Layer Supertype**.

## Example

Next we will have an example of how Active Record works.

**Step 1 - Directory System:**

```
📦Active_Record
 ┣ 📂class
 ┃ ┗ 📜Product.php
 ┣ 📂config
 ┃ ┗ 📜config.ini
 ┣ 📂database
 ┃ ┗ 📜product.db
 ┗ 📜index.php
```

**Step 2 - Database Config File:**

```bash
host = 
name = database/product.db
user = 
pass = 
type = sqlite
```

**Step 3 - Database:**

```sql
CREATE TABLE product(
  id INTEGER PRIMARY KEY NOT NULL,
  description TEXT,
  stock FLOAT,
  cost_price FLOAT,
  sale_price FLOAT,
  bar_code TEXT,
  date_register DATE,
  origin CHAR(1)
);
```

**Step 5 - Product Class:**

```php
class Product
{
	private static $conn;
    private mixed $data;
}
```
The **conn** property is static to maintain the value, so we don't need to open the same connection more than once.
The **data** variable is defined as mixed, as it can take on several types.

- **setConnection** method

```php
public static function setConnection(PDO $conn)
{  
	self::$conn = $conn;
}
```
setConnection receives a PDO connection as a parameter and stores it in the static attribute conn.

- **__get** and **__set** methods 

```php
public function __get(String $prop): mixed
{  
	return $this->data[$prop];
}

public function __set(String $prop, mixed $value)
{
	$this->data[prop] = $value;
}
```
**__get** is a magic method which is invoked when reading the value of a non-existent or inaccessible property, we receive the name of the property and pass it to the **data** attribute as the key.
**__set** is a magic method which is invoked when writing a value to a non-existing or inaccessible property, we receive the name and value of the property respectively

- **find** method

```php
public static function find(int $id): Product
{
    // Store the SQL in a variable
    $sql = "SELECT * FROM product WHERE id = :id";
    // Print the SQL
    print "{$sql} <br>";
    // perform the prepare method
    $result = self::$conn->prepare($sql);
    // Binds a parameter to the specified variable name
    // PDO::PARAM_INT Represents the SQL INTEGER data type
    $result->bindParam(':id', $id, PDO::PARAM_INT);
    // execute sql statement
    $result->execute();
    // Fetches the next row and returns it as an object
    // __CLASS__ receives the name of the created class
    return $result->fetchObject(__CLASS__);
}
```
The find method receives an id as a parameter and returns an object of the Product class as a value

- **all** method

```php
public static function all(String $filter = null): array
{
    $sql = "SELECT * FROM product";

    // checks if there is a filter
    if($filter)
    {
      // concatenate the filter to sql statement
      $sql .= " WHERE $filter";
    }

    print "{$sql} <br>";
    $result = self::$conn->prepare($sql);
    $result->execute();
    // Sets the remaining rows of a result set
    // PDO::FETCH_CLASS Returns a new instance of the requested class
    return $result->fetchALL(PDO::FETCH_CLASS, __CLASS__);

}
```
the all method can receive a filter and return a array of objects as a value

- **delete** method

```php
public function delete(int $id, String $operator = '=')
{  
    $sql = "DELETE FROM product WHERE id $operator :id";
    print "{$sql} <br>";
    $result = self::$conn->prepare($sql);             
    $result->bindParam(':id', $id, PDO::PARAM_INT);
			
    if($result->execute())
    {
      // returns an array indexed by column name as returned in your result set
      return $result->fetch(PDO::FETCH_ASSOC);
    }
			
}
```

- **save** method

```php
public function save()
{
			
    if(empty($this->data['id']))
    {
      $sql = "INSERT INTO product (description, stock, cost_price, sale_price, bar_code, date_register, origin)
	          VALUES (:description, :stock, :cost_price, :sale_price, :bar_code, :date_register, :origin)";
    }
    else
    {
      $sql = "UPDATE product SET description = :description, stock = : stock, cost_price = :cost_price, sale_price = :sale_price,
		      bar_code = :bar_code, date_register = :date_register, origin = :origin WHERE id = :id";
    }
			
    print $sql;
    $result = self::$conn->prepare($sql);
    return $result->execute($this->data);

}
```
The save method performs both the save and update.

## Testing

```php
<?php

  require_once 'class/Produto.php';

  try
  {
    $ini = parse_ini_file('config/config.ini');
    $name = $ini['name'];

    $conn = new PDO('sqlite:' . $name);
    $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

    Product::setConnection($conn);

    $product = new Product;

  }
  catch(Exception $e)
  {
    print $e->getMessage();
  }
```

- **INSERT**

```php
  $product->description = 'Juice';
  $product->stock = 8;
  $product->cost_price = 12;
  $product->sale_price = 18;
  $product->bar_code = '123123123';
  $product->origin = 'S';
  $product->date_register = date('Y-m-d');
  $product->save();
```

- **UPDATE**

```php
  $update = Product::find(1);
  $update->description = "Grape Juice";
  $update->save();
```

- **ALL**

```php
  foreach(Product::all() as $product)
  {
    print $product->description . ' ';
    print $product->cost_price . ' ';
    print $product->sale_price . "<br>";
  }
```

If you have any questions, just leave a comment below.