If you have used PHP, you likely had to deal with a database in one form or another, There are various ways to communicate with the database, you could start with PDO and start using raw queries or you could interact with the database at an object level using a Repository pattern or Active Record pattern, My goal here is to start a conversation with the user land PHP developers, ORM/Active Record developers as well as the core PHP devs on some existing ideas and an additional one that I want to propose.
I have been going through a few available implementations of using PHP to communicate with a database, I tried to compare them with some of the best out there like JOOQ and LINQ, so before I talk about my approach let us look at how a usual LINQ query looks like.
var todaysOrders = from o in unitOfWork.Orders
where o.OrderDate >= DateTime.Today
select o;
As you can see, there are no strings, everything is typed and insanely easy to refactor.
Your team mate changed a column name? No Problem!
your IDE or the build system will scream errors at you and with modern IDEs like IntelliJ, you don’t have to go to every file and do a search-replace.
Before we get into what exists today, let us give some credit to people suggesting to use SQL<< SQL; and projects that tried to mimic LINQ as closely as possible, some of them are, YaLinqo, Ginq, and Pinq. An example query is given below,
$youngPeopleDetails = $people
->where(function ($row) { return $row['age'] <= 50; })
->orderByAscending(function ($row) { return $row['firstName']; })
->thenByAscending(function ($row) { return $row['lastName']; })
->take(50)
->indexBy(function ($row) { return $row['phoneNumber']; })
->select(function ($row) {
return [
'fullName' => $row['firstName'] . ' ' . $row['lastName'],
'address' => $row['address'],
'dateOfBirth' => $row['dateOfBirth'],
];
});Now let us look at how data is queried using current PHP equivalents, the first one being currently the most used one, i.e, Laravel/Eloquent
$orders = App\Orders::has('order_date', '>=', $now)
->get();Yes, it looks beautiful for simple queries, though you almost always fallback to raw queries or the query builder as you cannot do a lot of complex queries using the above approach, It quickly devolves into dealing with a thin wrapper over PDO and messy strings concatenations .
The doctrine project, though much better in it’s design has similar drawbacks.
$q = Doctrine_Query::create()
->select('a.name')
->from('Account a')
->where('a.amount > 2000');DQL is really powerful, though it doesn’t help when you refactor your entities and doesn’t instil confidence when dealing with stringly typed entity references.
Cycle ORM is a joy to work with, though not very different, It still deals with strings.
$users = $orm->getRepository(User::class)
->select()
->where('active', true)
->fetchAll();If you look at all the above approaches, column names are the ones that are difficult to deal with, they simply cannot be extracted out for typing. You can pass a variable’s value, you can pass it’s reference but not it’s name.
I looked at a few other languages and frameworks on how they achieve this, and after a few hours of tinkering, here is how the end result of my approach looks like,
Now if we dump $sel, it will have the following,
string(58) "SELECT * XX\XX\Tests\domain\User WHERE id = ?"Auto-completion works as expected, Refactoring property names works as expected and believe me, there is no additional IDE autocompletion plugin involved.
If you like the concept, read on, the rest of the post describes the dirty hack. Current and future ORM devs might find it interesting to read and see what could you do that makes this(StrongQL concept) possible today and if it will improve your end developer experience?
and for core PHP devs, does PHP as a language need some extra constructs to make this happen in a nicer way. I would be very happy if someone comes back and tells me, you could use this magical construct and there ye go!
The first thing is the User Entity itself,
class User extends Entity
{
public BigInt $id;
public Varchar $username;
public Varchar $password;Told you it was a hack! I am sure this will look much better with PHP 8’s Attribute feature.
BigInt and Varchar extend a class called Column as shown below,
class Column
{
public $_columnName = "";
public function setColumnName($columnName): void
{
$this->_columnName = $columnName;
}
public function getColumnName(): string
{
return $this->_columnName;
}
}
Now the Entity class is as follows and is among the ugliest code out there,
class Entity
{
private static $instance = null;
public static function col(): self
{
$calledClass = (get_called_class());
if (self::$instance == null) {
self::$instance = new $calledClass();
self::$instance->setAllColumnNames();
}
return self::$instance;
}
private function setAllColumnNames(){
$reflect = new ReflectionClass($this);
$props = $reflect->getProperties(ReflectionProperty::IS_PUBLIC);
foreach($props as $prop){
$name = "".$prop->getName();
$clsName = $prop->getType()->getName();
$this->{$name} = new $clsName;
$this->{$name}->setColumnName($prop->getName());
}
}Yes the $instance variable needs some logic to stop being overwritten, but apart from that, the logic is as simple as getting the “called class” and then get it’s properties, and set the internal column name for each of them and store them in the $instance variable.
The Select Class is basically a fancy string concatenation program,
class Select{
private array $tokens = ['SELECT'];
private function __construct($tokens)
{
$this->tokens = array_merge($this->tokens, $tokens);
}
public static function columns($arr=null) : self
{
$tokens = [];
if($arr==null){
$tokens[] = "*";
}
return new Select($tokens);
}
public function isEqualTo($value) :self {
$this->tokens[] = "=";
$this->tokens[] = '?';
return $this;
}
public function where() :self {
$this->tokens[] = "WHERE";
return $this;
}
public function column(Column $column) :self {
$this->tokens[] = $column->getColumnName();
return $this;
}
public function from(String $entity) :self
{
$this->tokens[] = $entity;
return $this;
}
public function build() :string {
return implode(" ",$this->tokens);
}
}
The code is incomplete but is enough to demonstrate the above logic.
I understand I could be wrong or very wrong, People will at least know this was tried earlier and why not to repeat it.
Well, that’s about it, Any feedback or points to further the discussion around this or possible alternate solutions will be highly appreciated.
A humble thanks for your time 🙂
Disclaimer: Though the title has the word “Strong” in it, it doesn’t mean it is Strongly Typed, it just hints at the possibility and close to similar benefits that you could gain.
Tags: PHP