Joining model multiple times by alias not working.

I have the model with following relations:

$this->belongsTo('consolidator_id', 'Models\\Companies', 'id', array('alias' => 'Consolidators'));
$this->belongsTo('customer_id', 'Models\\Companies', 'id', array('alias' => 'Customers'));

Then I try to leftJoin() the related models:

$this->modelsManager->createBuilder()
    ->from(['Orders' => 'Models\Orders'])
    ->columns([
        'Customers.name as customerName',
        'Consolidators.name as consolidatorName',
    ])
    ->leftJoin('Models\Companies', null, 'Customers')
    ->leftJoin('Models\Companies', null, 'Consolidators');

But I get the error:

There is more than one relation between models 'Models\Orders' and 'Models\Companies', the join must be done using an alias, when preparing: SELECT Customers.name as customerName, Consolidators.name as consolidatorName FROM [Models\Orders] AS [Orders] LEFT JOIN [Models\Companies] AS [Customers] LEFT JOIN [Models\Companies] AS [Consolidators]
#0 [internal function]: Phalcon\Mvc\Model\Query->_getJoins(Array)
#1 [internal function]: Phalcon\Mvc\Model\Query->_prepareSelect()
#2 [internal function]: Phalcon\Mvc\Model\Query->parse()

When you try to use getRelationByAlias() method of Phalcon\Mvc\Model\Manager it always will return false, unless you initialize() your model first. This is not really intuitive behaviour. Going further you will always get empty array from getRelations() method.

$this->modelsManager->initialize(new Orders());

var_dump($this->modelsManager->getRelationByAlias(
    'Models\\Orders',
    'Consolidators'
));

I went to the Phalcon repository, and found the following code there (line 1517 - 1546):

/**
 * Get the model name from its source
 */
let modelNameAlias = sqlAliasesModels[joinAlias];

/**
 * Check if the joined model is an alias 
 */
let relation = manager->getRelationByAlias(fromModelName, modelNameAlias);
if relation === false {

    /**
     * Check for relations between models
     */
    let relations = manager->getRelationsBetween(fromModelName, modelNameAlias);
    if typeof relations == "array" {

        /**
         * More than one relation must throw an exception
         */
        if count(relations) != 1 {
                throw new Exception("There is more than one relation between models '" . fromModelName . "' and '" . joinModel . "', the join must be done using an alias, when preparing: " . this->_phql);
        }

        /**
         * Get the first relationship
         */
        let relation = relations[0];
    }
}

This is the place where exception is thrown from. I guess there is problem with this code - manager->getRelationsBetween(fromModelName, modelNameAlias); will always return false, unless the model is somehow initialized in the manager first. I concluded this from the example above.

Is this a bug, or am I missing something?

I think autojoin won't work with aliases, you will have to specify the join clause:

$this->modelsManager->createBuilder()
    ->from(['Orders' => 'Models\Orders'])
    ->columns([
        'Customers.name as customerName',
        'Consolidators.name as consolidatorName',
    ])
    ->leftJoin('Models\Companies', 'Customers.id=Orders.customer_id', 'Customers')
    ->leftJoin('Models\Companies', 'Consolidators.id=Orders.consolidator_id', 'Consolidators');
edited May '16

As i wrote here many times. Aliases in relations definition are only for access related model in object(already selected).

If you wan to join same class multiple times in query builder you need to provide ON Condition(secon parameter).



1.0k

I provided the code excerpt, that is actually shows that this functionality is implied. What is the purpose of that code then? I didn't paste the whole excerpt. for the sake of brivety, but before there is a part that checks if conditions where provided.

As i wrote here many times. Aliases in relations definition are only for access related model in object(already selected).

If you wan to join same class multiple times in query builder you need to provide ON Condition(secon parameter).

The join function of querybuilder has 3 params: model, clause and alias. If you only provide the model, ORM will automatically generate the second clause. If you want to give it an alias, you MUST provide the second clause, null won't work.

As you see:

SELECT Customers.name as customerName, Consolidators.name as consolidatorName FROM [Models\Orders] AS [Orders] LEFT JOIN [Models\Companies] AS [Customers] LEFT JOIN [Models\Companies] AS [Consolidators]

Youy query is built without any conditions. You need to provide conditions if you are joining same model more than once.

Well actually null works, but only if you join it once.

The join function of querybuilder has 3 params: model, clause and alias. If you only provide the model, ORM will automatically generate the second clause. If you want to give it an alias, you MUST provide the second clause, null won't work.

If you give it an alias, null won't work ;]

Well actually null works, but only if you join it once.

The join function of querybuilder has 3 params: model, clause and alias. If you only provide the model, ORM will automatically generate the second clause. If you want to give it an alias, you MUST provide the second clause, null won't work.

It will work :) Im using everywhere null values and it's working fine. You just need join it once.

If you give it an alias, null won't work ;]

Well actually null works, but only if you join it once.

The join function of querybuilder has 3 params: model, clause and alias. If you only provide the model, ORM will automatically generate the second clause. If you want to give it an alias, you MUST provide the second clause, null won't work.



1.0k

Here is the code, that explains why null works with one relation and fails otherwise:

/**
 * More than one relation must throw an exception
 */
if count(relations) != 1 {
    throw new Exception("There is more than one relation between models '" . fromModelName . "' and '" . joinModel . "', the join must be done using an alias, when preparing: " . this->_phql);
}

/**
 * Get the first relationship
 */
let relation = relations[0];

But if you look close this code tries to resolve the relation as if no alias was provided. And as I am providing alias this clause shouldn't be executed at all.

Well actually null works, but only if you join it once.

The join function of querybuilder has 3 params: model, clause and alias. If you only provide the model, ORM will automatically generate the second clause. If you want to give it an alias, you MUST provide the second clause, null won't work.

Hmm... last I tried it on ~2.0.3 and didn't work, must've been fixed since. Disregard my smartass then :D

It will work :) Im using everywhere null values and it's working fine. You just need join it once.

If you give it an alias, null won't work ;]

Well actually null works, but only if you join it once.

The join function of querybuilder has 3 params: model, clause and alias. If you only provide the model, ORM will automatically generate the second clause. If you want to give it an alias, you MUST provide the second clause, null won't work.

edited May '16

But you are providing ALIAS for QUERY BUILDER, ALIASES WHEN DEFINING RELATIONS are diffrent story and are not related/same things.

Alias in belongsTo and other relations are basically for:

class Orders extends Model
{
  public function initialize()
  {
  $this->belongsTo('consolidator_id', 'Models\\Companies', 'id', array('alias' => 'Consolidators'));
  $this->belongsTo('customer_id', 'Models\\Companies', 'id', array('alias' => 'Customers'));
  }
}

$order = Orders::findFirst();
$customers = $orders->Customers; // we can access this thanks to alias in relation definition

Also not sure why you have everywhere plural form. In Customers class for example you have some array of Customer ? If not then why you name it like this ? Just name it as Customer etc.



1.0k

I pasted code from "parser" class, that if I understand correctly parses query, and tries to build native sql. And here it tries to resolve relation from model by alias:

/**
 * Check if the joined model is an alias
 */
let relation = manager->getRelationByAlias(fromModelName, modelNameAlias);

So following this logic this is the same story, otherwise this code is simply useless. But you are free correct me if I am wrong.

But you are providing ALIAS for QUERY BUILDER, ALIASES WHEN DEFINING RELATIONS are diffrent story and are not related/same things.

edited May '16

@Sevavietl In your case, they aren't the same. You provided the relational alias name as a builder alias name.

Try this:

$this->modelsManager->createBuilder()
    ->from(['Orders' => 'Models\Orders'])
    ->columns([
        'Customers.name as customerName',
        'Consolidators.name as consolidatorName',
    ])
    ->leftJoin('Customers')
    ->leftJoin('Consolidators');

If that won't work either, stick to providing the join clause manually.



1.0k

For now I made the stub for myself, but using the same logic, as I think was implied to be implemented in the parser:

$this->modelsManager->initialize(new $modelName);

array_walk($queryParameters['join'], function ($join) use (
    &$queryBuilder,
    $modelName,
    $modelAlias
){
    $relation = $this->modelsManager->getRelationByAlias(
        $modelName,
        $join['alias']
    );

    if ($relation === false) {
        throw new \Exception("There is not relation provided for {$join['alias']}.");
    }

    $condition = "";
    $condition .= $modelAlias . '.' . $relation->getFields();
    $condition .= ' = ';
    $condition .= $join['alias'] . '.' . $relation->getReferencedFields();

    $queryBuilder->leftJoin($join['model'], $condition, $join['alias']);
});
edited May '16

Yes you are wrong. As was stated above - if there is two relations to same model:

let relations = manager->getRelationsBetween(fromModelName, modelNameAlias);

count(relations) will obviously return 2, that's why you have exception. It just writeing wrong message, you need to provide on condition as well when joining same model more than once.

Initialize method is already done.

YOU DON'T UNDERSTAND.

$join['alias'] IS WHOLE DIFFRENT THING THAN second parameter in this - $this->modelsManager->getRelationByAlias

It's just alias for builded SQL Query, you can't join data in sql from two same tables - if you want - you need to alias them(AS in your query)



1.0k

It should resolve relation by alias before and not go there at all, as I provided the same relation aliases in the model and in the builder.

Yes you are wrong. As was stated above - if there is two relations to same model:

let relations = manager->getRelationsBetween(fromModelName, modelNameAlias);

count(relations) will obviously return 2, that's why you have exception. It just writeing wrong message, you need to provide on condition as well when joining same model more than once.

edited May '16

You just don't unerstand that alias in query builder is whole diffrent thing than alias in relation definition. They are not related, they are totally diffrent thing. Alias in query builder is just alias for SQL Query, alias in relation definition is for accessing this relation in already selected object.

edited May '16

Please listen to what we are saying, relation alias and builder alias ARE NOT THE SAME.

It should resolve relation by alias before and not go there at all, as I provided the same relation aliases in the model and in the builder.

Yes you are wrong. As was stated above - if there is two relations to same model:

let relations = manager->getRelationsBetween(fromModelName, modelNameAlias);

count(relations) will obviously return 2, that's why you have exception. It just writeing wrong message, you need to provide on condition as well when joining same model more than once.



1.0k

$join['alias'] is my own variable and it has nothing to do with phalcon.

Yes you are wrong. As was stated above - if there is two relations to same model:

let relations = manager->getRelationsBetween(fromModelName, modelNameAlias);

count(relations) will obviously return 2, that's why you have exception. It just writeing wrong message, you need to provide on condition as well when joining same model more than once.

Initialize method is already done.

YOU DON'T UNDERSTAND.

$join['alias'] IS WHOLE DIFFRENT THING THAN second parameter in this - $this->modelsManager->getRelationByAlias

It's just alias for builded SQL Query, you can't join data in sql from two same tables - if you want - you need to alias them(AS in your query)

edited May '16

Okay you just implemented some your own stuff. But you don't understand that there is no bug, everything is working as it should be. You just don't understand that relation alias is diffrent thing than query alias.

And this code:

let relation = manager->getRelationByAlias(fromModelName, modelNameAlias);

Yes it can get relations from model. But you have to use relation aliases here so:

$this->modelsManager->createBuilder()
    ->from(['Orders' => 'Models\Orders'])
    ->columns([
        'Customers.name as customerName',
        'Consolidators.name as consolidatorName',
    ])
    ->leftJoin('Customers', null, 'Customers')
    ->leftJoin('Consolidators', null, 'Consolidators');

Then it will work fine. Without alias as 3rd argument there will be exception thrown.



1.0k

Here is whole code excerpt:

                /**
                 * Check if the model already have pre-defined conditions
                 */
                if !fetch preCondition, joinPreCondition[joinAlias] {

                    /**
                     * Get the model name from its source
                     */
                    let modelNameAlias = sqlAliasesModels[joinAlias];

                    /**
                     * Check if the joined model is an alias
                     */
                    let relation = manager->getRelationByAlias(fromModelName, modelNameAlias);
                    if relation === false {

                        /**
                         * Check for relations between models
                         */
                        let relations = manager->getRelationsBetween(fromModelName, modelNameAlias);
                        if typeof relations == "array" {

                            /**
                             * More than one relation must throw an exception
                             */
                            if count(relations) != 1 {
                                throw new Exception("There is more than one relation between models '" . fromModelName . "' and '" . joinModel . "', the join must be done using an alias, when preparing: " . this->_phql);
                            }

                            /**
                             * Get the first relationship
                             */
                            let relation = relations[0];
                        }
                    }

                    /*
                     * Valid relations are objects
                     */
                    if typeof relation == "object" {

                        /**
                         * Get the related model alias of the left part
                         */
                        let modelAlias = sqlModelsAliases[fromModelName];

                        /**
                         * Generate the conditions based on the type of join
                         */
                        if !relation->isThrough() {
                            let sqlJoin = this->_getSingleJoin(joinType, joinSource, modelAlias, joinAlias, relation);
                        } else {
                            let sqlJoin = this->_getMultiJoin(joinType, joinSource, modelAlias, joinAlias, relation);
                        }

                        /**
                         * Append or merge joins
                         */
                        if isset sqlJoin[0] {
                            for sqlJoinItem in sqlJoin {
                                let sqlJoins[] = sqlJoinItem;
                            }
                        } else {
                            let sqlJoins[] = sqlJoin;
                        }

                    } else {

                        /**
                         * Join without conditions because no relation has been found between the models
                         */
                        let sqlJoins[] = [
                            "type": joinType,
                            "source": joinSource,
                            "conditions": []
                        ];
                    }

Tell me, please, the hypothetical situation when this let relation = manager->getRelationByAlias(fromModelName, modelNameAlias); will be not false.

You just don't unerstand that alias in query builder is whole diffrent thing than alias in relation definition. They are not related, they are totally diffrent thing. Alias in query builder is just alias for SQL Query, alias in relation definition is for accessing this relation in already selected object.

edited May '16

I wrote this situation above. Here you have this method : https://github.com/phalcon/cphalcon/blob/master/phalcon/mvc/model/manager.zep#L1098

$this->modelsManager->createBuilder()
    ->from(['Orders' => 'Models\Orders'])
    ->columns([
        'Customers.name as customerName',
        'Consolidators.name as consolidatorName',
    ])
    ->leftJoin('Customers', null, 'Customers')
    ->leftJoin('Consolidators', null, 'Consolidators');

Its accessing exactly the relation by RELATION ALIAS from your defined relations. But it has to be a first attribute in joins, thrid attribute is QUERY ALIAS(in sql)

This will return a relation object instead of false:

$manager->getRelationByAlias('Models\Orders','Consolidators');


1.0k

Ok, thank you. I tried this, but it causes error:

Model 'Customers' could not be loaded
#0 [internal function]: Phalcon\Mvc\Model\Manager->load('Customers', true)
#1 [internal function]: Phalcon\Mvc\Model\Query->_getJoin(Object(Phalcon\Mvc\Model\Manager), Array)
#2 [internal function]: Phalcon\Mvc\Model\Query->_getJoins(Array)
#3 [internal function]: Phalcon\Mvc\Model\Query->_prepareSelect()
#4 [internal function]: Phalcon\Mvc\Model\Query->parse()

I wrote this situation above. Here you have this method : https://github.com/phalcon/cphalcon/blob/master/phalcon/mvc/model/manager.zep#L1098

$this->modelsManager->createBuilder()
   ->from(['Orders' => 'Models\Orders'])
   ->columns([
       'Customers.name as customerName',
       'Consolidators.name as consolidatorName',
   ])
   ->leftJoin('Customers', null, 'Customers')
   ->leftJoin('Consolidators', null, 'Consolidators');

Its accessing exactly the relation by RELATION ALIAS from your defined relations. But it has to be a first attribute in joins, thrid attribute is QUERY ALIAS(in sql)

edited May '16

And now this is bug, beacause it supposed to load this relation, but it doesn't.

Actually this happens beacause of this line https://github.com/phalcon/cphalcon/blob/master/phalcon/mvc/model/query.zep#L1316 - it just tries to load as model name.



1.0k
edited May '16

Still, I thik that this is not really intuitive, because why I have to specify two same aliases here:

->leftJoin('Customers', null, 'Customers')
->leftJoin('Consolidators', null, 'Consolidators');

I understand what you mean. But this is not really pretty logic.

And now this is bug, beacause it supposed to load this relation, but it doesn't.

Actually this happens beacause of this line https://github.com/phalcon/cphalcon/blob/master/phalcon/mvc/model/query.zep#L1316 - it just tries to load as model name.

Beacause first is relation alias OR MODEL NAMESPACE, thrid is query alias. Totally diffrent things. But im guessing if there could possibly be working joining using relation aliases then we won't even need those third parameters beacause well, there is already some unique alias we can use.

It's just looking like someone wanted to implement such an ability but forgot about it.



1.0k

Can you advise me if there is a tutorial on how to debug and compile phaclon myself?

And now this is bug, beacause it supposed to load this relation, but it doesn't.

Actually this happens beacause of this line https://github.com/phalcon/cphalcon/blob/master/phalcon/mvc/model/query.zep#L1316 - it just tries to load as model name.

Install zephir, clone phalcon repository and just zephir fullclean and then zephir build.

edited May '16

Install zephir (https://docs.zephir-lang.com/en/latest/install.html), then:

git clone https://github.com/phalcon/cphalcon -b 2.0.x --single-branch
cd cphalcon
zephir build
cd build
./install

Can you advise me if there is a tutorial on how to debug and compile phaclon myself?

And now this is bug, beacause it supposed to load this relation, but it doesn't.

Actually this happens beacause of this line https://github.com/phalcon/cphalcon/blob/master/phalcon/mvc/model/query.zep#L1316 - it just tries to load as model name.