How to create sequential objects?

Question

First of all, I want to mention that I am extremely beginner in POO.

Well, I’ve been racking my brain the last few days, but I couldn’t come to a conclusion on my own, I couldn’t get answers to my question (maybe because the doubt is so big that I don’t know how to search properly).

I would like to know how I could create a method of a 'Database' class to insert into the database with the following syntax:

$user = Database::table('users')->where('name', 'John')->first();

Where:

table() is the method by which the SELECT;

where() would be the corresponding WHERE of SQL and first() determines that only the first query result should be returned.

The question is not about SQL, but about how to create the syntax from above.

Answer 1

There are numerous ways to implement this, but in essence just understand exactly what the line of code does:

$user = Database::table('users')->where('name', 'John')->first();

Database is a class that has a static method table responsible for instantiating an object to the table 'users' and return it. The method where is what we call the modifier method, because it receives parameters that will modify the object itself and return it in the new state. The method first will be responsible for executing the SQL generated with the previous instructions and return the result.

Quite simply would be something like:

class Database
{
    private $table;
    private $conditions = [];
    private $columns = ['*'];

    private function __construct($table)
    {
        $this->table = $table;
    }

    public static function table($table): self
    {
        return new self($table);
    }

    public function where($column, $value): self
    {
        $this->conditions[] = "{$column} = '{$value}'";

        return $this;
    }

    public function select(array $columns): self
    {
        $this->columns = $columns;

        return $this;
    }

    public function first()
    {
        $columns = join(', ', $this->columns);
        $sql = "SELECT {$columns} FROM {$this->table}";

        if (count($this->conditions) > 0) {
            $sql .= " WHERE ";
            $sql .= join(" AND ", $this->conditions);
        }

        $sql .= " LIMIT 1";

        return $sql;
    }
}

To make it simple, I did the method first return the generated SQL instead of executing it and returning the result, as this would depend directly on having the database and makes it difficult to reproduce the answer.

This way, we can do:

$users = Database::table('users')->first();
// string(27) "SELECT * FROM users LIMIT 1"

$users = Database::table('users')->select(['id', 'nome'])->first();
// string(34) "SELECT id, nome FROM users LIMIT 1"

$users = Database::table('users')->where('nome', 'João')->first();
// string(48) "SELECT * FROM users WHERE nome = 'João' LIMIT 1"

$users = Database::table('users')->select(['id', 'nome'])->where('nome', 'João')->first();
// string(55) "SELECT id, nome FROM users WHERE nome = 'João' LIMIT 1"

Among others...