“I always say, 'This will be my last 'Metal Gear.'” Hideo Kojima

Easy queries including joins with Spot2 ORM

Are you developing a PHP website which connects to a MySQL database through Spot2? If so, you may already know that Spot does not support joining tables. You have to write down the whole query (which may be not-so-readable) and pass it to Spot’s mapper.

But do you know what Spot2 does support? Views.

What a brilliant idea!

Well, not so fast. This method is (probably) not recommended for BIG databases, because SQL views are not performance friendly. But, for a small database (even for a not-so-small one), it’s a simple way to simplify your queries A LOT.

Imagine this query:

SELECT p.id, pn.name, p.stock FROM Products p
INNER JOIN ProductsI18N pn ON p.id = pn.idProduct
WHERE pn.langCode = 'en'

This will return something like this (the list of products with ‘en’ translations):

id name stock
1 Apples 16
2 Pears 12
3 Melons 1

The way to query this from Spot2 (since it does not support joins) will be:

$products = $mapper->query("SELECT p.id, pn.name, p.stock FROM Products p INNER JOIN ProductsI18N pn ON p.id = pn.idProduct WHERE pn.langCode = 'en');

BUT if you store this query in a View including the langCode field (remember to include whatever field you need for filtering), you will be able to retrieve the data using Spot2 entities.

CREATE VIEW 'productsTranslated' AS
SELECT p.id, pn.name, p.stock, pn.langCode FROM Products p
INNER JOIN ProductsI18N pn ON p.id = pn.idProduct
WHERE pn.langCode = 'en'
<?php

class productsTranslated extends \Spot\Entity
{
	protected static $table = 'productsTranslated';
	public static function fields() 
	{
		return 
		[
			'id'			=> [ 'type' => 'integer', 'primary' => true, 'unique' => true, 'autoincrement' => true],
			'name'			=> [ 'type' => 'text' ],
			'stock'			=> [ 'type' => 'integer', 'default' => 0],
			'langCode'		=> [ 'type' => 'text' ]
		];
	}
}

Now, with the entity created, you can use the entity as normal. So you can call where() or all() or whatever you need, simplifying things a lot. Our query will now look like this:

$mapper = $spot->mapper('productsTranslated');

$products = $mapper->where(['pn.langCode' => 'en']);

And $products will be a Spot entity, with all its fields accessible as always. Again, this method is for small projects. I’m not sure what impact may have on big projects as Views are probably not recommended for repetitive querying. Also, this trick is probably not qualifying as best practice.

But it works, and it may help you avoid excessive complexity with small or personal projects.

Happy coding!

Jordi