Nearly everybody that has a somewhat bigger installation knows this problem: We need to get our product/customer/order/whatever data into this other system from this other system. How can we do it? For years my first idea was to bring up a Magento Extension that handles this. And then the same data has to go somewhere else: next extension, oh and a third one comes up. So we ended up with a multitude of extension for different channels. And then something changes, maybe you do an upgrade and it breaks on your side, or your remote service provider changes something and you have to adapt, hopefully without altering the behavior of the rest of your system. As everybody knows, this is THE example where it comes in handy to have tests in place but that is a whole set of other problems that I will not cover in this post.
Another problem this brings in is the execution time. If you have to fetch all this information multiple times it gets costly, as we all know about the great speed and little resource consumption if you try to get big collections and iterate over those. If you encounter something like this a middleware could be your rescue.
The idea
In our case we had another motivation to not do everything in this regard in Magento: we still use Magento1 and we know it will probably not last forever for us. Coupling to a solution that is already marked as “to be replaced soon” also sounded like a so-so idea. As I do nearly everything not Magento related in Laravel the platform was an easy choice. It brings in everything we needed: a solution for scheduled jobs, easy configuration, nice templating for “control-pages”, composer, an ORM…
The last one became one of the most loved things I did not expect to use much, as I knew the db-scheme quite well.
The starting phase
In our case we needed to bring the data into some kind of SAP-EDI compatible format and we also needed to consume the same format. We got all .xsd files for the validation and decided to use Array2XML and XML2Array for consumption and production of the XML structure as it does exactly what it is named for and it does that well. Even with attributes. Ok the structure becomes a bit more clunky but one of the few ready made solutions that support this.
We needed something to export products, orders, multi-stock stock movements (from one warehouse to another) and incoming stock announcement for the warehouses.
My initial thought was to get everything with direct mysql queries as they are fast and we did not really need to be concerned about some strange side effects like observers or such stuff. At least we thought so. I just could not figure out how to manage it to get a couple of products with all their needed attributes and attribute_options (in case of drop-downs) without our database complaining about “…only supports 67 joins in a single statement…” This alarmed me a bit and I looked for alternative solutions. Ok getting everything on its own and aggregate the whole stuff in PHP was easy, but also somewhat slow and resource hungry as iterating over thousands of items for multiple times was not so great. Especially if you considered “foreach-inception” like having multiple foreach-statements cascaded within each other.
ORM for the rescue
I looked further into the problem and the system and this lead to an idea: why not use the laravel-orm internal relation stuff to represent the data relation that is in the database. You just need to make the correct models and supply them with the needed relations and then you can access nearly everything that you need.
namespace App;
use Illuminate\Database\Eloquent\Model;
class CustomerOrder extends Model
{
protected $table = 'sales_flat_order';
protected $primaryKey = 'entity_id';
public function shippingAddress()
{
return $this->hasOne('App\CustomerOrderAddress', 'parent_id', 'entity_id')
->where('address_type', '=', 'shipping');
}
public function billingAddress()
{
return $this->hasOne('App\CustomerOrderAddress', 'parent_id', 'entity_id')
->where('address_type', '=', 'billing');
}
public function payment()
{
return $this->hasOne('App\CustomerOrderPayment', 'parent_id', 'entity_id');
}
}
namespace App;
use Illuminate\Database\Eloquent\Model;
class CustomerOrderAddress extends Model
{
protected $table = 'sales_flat_order_address';
protected $primaryKey = 'entity_id';
public $timestamps = false;
}
And then you can just access it like this:
$orders = new CustomerOrder::where('some-column', '=' 'some-value')->with(['payment', 'billingAddress', 'shippingAddress'])->get();
foreach ($orders as $order) {
$name = $order->billingAddress->firstname.' '.$order->billingAddress->lastname;
$paymentMethod = $order->payment->method;
}
And then I encountered a big problem for ORM and that is called EAV. Getting all those product attributes was not that easy but even there the relations that you could provide to the models were a great helper if you take it from the right angle. In the end we came up with a solution like this:
namespace App;
use App\Attribute\Decimal;
use App\Attribute\Integer;
use App\Attribute\Varchar;
use Illuminate\Database\Eloquent\Model;
class Product extends Model
{
protected $table = 'catalog_product_entity';
protected $primaryKey = 'entity_id';
public $timestamps = false;
public function varchar()
{
return $this->hasMany('App\Attribute\Varchar', 'entity_id')
->join('eav_attribute', 'eav_attribute.attribute_id', '=', Varchar::getTableName() . '.attribute_id')
->select('store_id', 'entity_id', 'value', 'attribute_code');
}
public function integer()
{
return $this->hasMany('App\Attribute\Integer', 'entity_id')
->join('eav_attribute', 'eav_attribute.attribute_id', '=', Integer::getTableName() . '.attribute_id')
->select('store_id', 'entity_id', 'value', 'attribute_code');
}
public function decimal()
{
return $this->hasMany('App\Attribute\Decimal', 'entity_id')
->join('eav_attribute', 'eav_attribute.attribute_id', '=', Decimal::getTableName() . '.attribute_id')
->select('store_id', 'entity_id', 'value', 'attribute_code');
}
public function name()
{
return $this->hasOne('\App\Attribute\Varchar', 'entity_id')
->where('store_id', '=', 0)
->where('attribute_id', '=', 71);
}
The attribute models are like the following one:
namespace App\Attribute;
use Illuminate\Database\Eloquent\Model;
class Varchar extends Model
{
protected $table = 'catalog_product_entity_varchar';
protected $primaryKey = 'value_id';
public $timestamps = false;
public function product()
{
return $this->belongsTo('App\Product', 'entity_id');
}
public static function getTableName()
{
return with(new static)->getTable();
}
}
This resulted in 3 queries for the EAV stuff (one per attribute-type-table) and as many as you want to access direct (in our example “name”), if you bypass eager relation loading and those where very simple. Having everything in place in 3-5 seconds (including PHP-object generation) instead of multiple minutes.
One problem is, that you cannot access something like
$product->price
directly with this, as you would either need to define a single relation for this or if you need multiple attributes, iterate over all varchar attributes and match for the attribute code.
Combination with Magento
A bit of a problem remain the data that has to get into Magento, like shipping information and other kind of responses. It would be easy to also use direct database access in here but as we have a multitude of different 3rd party stuff in place we got cautious to not miss triggers, observers or something else that added undocumented behavior. So we decided to just put the data into intermediate tables and bring up a slim Magento extension reading those tables and taking the needed actions. By this we are still completely in charge of the format Magento reads and not depending on strange 3rd party interfaces.