Splitting data over multiple databases

No, you can't join tables across databases on different servers, Phalcon resolves a PHQL query into a single SQL statement that is sent to the first server in the FROM clause, so it expects "Brands" is also a table in the same database server as "Cars".

Phalcon, thank you for the reply!

From my another question (about horisontal sharding): If pieces of the data (example "Cars") will be stored on several database servers (and selectReadConnection will be set)? How a PHQL query will be resolved?

Actually everything uses PHQL, Cars::findFirst/Cars::find use PHQL too

Cars::findFirst(1) == 'SELECT * FROM Cars WHERE id = 1'
Cars::find('id > 100 AND id < 1000') == 'SELECT * FROM Cars WHERE id > 100 AND id < 1000'

The PHQL's AST is passed to selectReadConnection so you can return a connection according to the conditions in the PHQL

Ok, I undertand it.

My case: 2 computers with 2 databases. 1 computer: MySQL, dbname = "Cat1", table = "Brands" (id, name) and table = "Cars" (id, user, brand) 2 computer: MySQL, dbname = "Cat2", table = "Brands" (id, name) and table = "Cars" (id, user, brand). I set up Phalcon Framework enviroment to get cars with id<1000 from 1st computer and cars with id>1000 from 2nd computer.

How such PHQL query will be resolved php $phql = "SELECT Cars.name AS car_name, Brands.name AS brand_name FROM Cars JOIN Brands"; ?

That depends on how you set up the 'selectReadConnection':

For example:

<?php

class Cars extends Phalcon\Mvc\Model
{
    /**
     * Dynamically selects a shard
     *
     * @param array $intermediate
     * @param array $bindParams
     * @param array $bindTypes
     */
    public function selectReadConnection($intermediate, $bindParams, $bindTypes)
    {
        //Check if there is a 'where' clause in the select
        if (isset($intermediate['where'])) {

            $conditions = $intermediate['where'];

            //Choose the possible shard according to the conditions
            if ($conditions['left']['name'] == 'id') {
                $id = $conditions['right']['value'];
                if ($id > 0 && $id < 10000) {
                    return $this->getDI()->get('dbShard1');
                }
                if ($id > 10000) {
                    return $this->getDI()->get('dbShard2');
                }
            }
        }

        //Use a default shard
        return $this->getDI()->get('dbShard0');
    }

}

Since no-conditions are set in "SELECT Cars.name AS car_name, Brands.name AS brand_name FROM Cars JOIN Brand", the connection used is the one by default 'dbShard0'

Thank you! Now I clearly understand that the "horizontal sharding" will work only if "WHERE" conditions are set!

But still it is not "real horizontal sharding" when I can store parts of the data on different servers and Phalcon framework in the all cases will retrieve "right" parts of the data from the "right" server. Isn't it?

Hello, Could you tell me how to use this method?