add support to define join-type in schema

[lvanderree]

I would like to propose an extension to the schema-syntax, to allow it to contain a preferred join-type.

I would appreciate it if the schema could contain information regarding the preferred join type of a relation, to simplify the call to the join-method in the PropelQueryLanguage. The goal is to get the default join-type (left, right, inner) for a join from the schema, instead of always use inner-join by default.

I am working on an implementation to (recursively) process dotted relation-names, but when you define dotted-relation-names you cannot provide the join-type along with the name. However since you probably already know which join you want, when you define the relation, I think it would be nice if it would be possible to define the join-type in the schema, like:

For example in test/fixtures/bookstore/schema.xml

        <table name="essay">
                <column name="id" required="true" primaryKey="true" autoIncrement="true" type="INTEGER" description="Book Id" />
                <column name="title" type="VARCHAR" required="true" primaryString="true" />
                <column name="first_author" required="false" type="INTEGER" description="Foreign Key Author" />
                <column name="second_author" required="false" type="INTEGER" description="Foreign Key Author" />
                <column name="subtitle" type="VARCHAR" phpName="SecondTitle" />
                <foreign-key foreignTable="author" onDelete="setnull" onUpdate="cascade">
                        <reference local="first_author" foreign="id" />
                </foreign-key>
-               <foreign-key foreignTable="author" onDelete="setnull" onUpdate="cascade">
+               <foreign-key foreignTable="author" preferedJoinType="LEFT JOIN" onDelete="setnull" onUpdate="cascade">
                        <reference local="second_author" foreign="id" />
                </foreign-key>
        </table>

Since the secondAuthor is optional you probably always want to use a left-join to retrieve the second-author while joining.

I've got a diff to support this all based on Propel1.5 beta1 that has extensions for

database.xsd
RelationMap.php
TableMap.php
ForeignKey.php
PHP5TableMapBuilder.php
ModelCriteria.php (for retrieving the prefered joinType for a given relation)

I have also updated the unit-tests to test this extension.

[lvanderree]

patch for runtime folder

[lvanderree]

patch for generator folder

[francois]

You use the joinType at runtime by introspection in the TableMap. I think it would be more effective to use it in the generated Query methods doing the joins, probably by adding a generated joinXXX() method for each relation, defaulting to the preferrend join type.

I'm not in favor of adding more information to the TabkeMap and RelationMap, since their initialization is already quite costly.

Could you rework on your patch to use the generator rather than runtime introspection?

[lvanderree]

sure I can,

although I need a way to resolve the DefaultJoin-type at runtime, I can create a behaviour that adds this information for me, as long as this information is defined in the schema.

I will rewrite my code later this week at some evening, or maybe Friday, since I have given some other prio's at work at the moment.

[francois]

Why do you need the default join type at runtime? If the generated query objects feature a 'joinFoo()' method using the correct default join type, just call this method at runtime and you're good to go.

[lvanderree]

good point ;)

What I currently do in my patched version is resolving the joinType and providing it to the join-method as second argument (see attachement), but when JoinXXX contains the correct joinType already, there obviously is no need for this anymore.

This will increase the efficiency of my runtime-parser as well!

Nice to have you thinking along.

[lvanderree]

a preview of my ObjectPath-Extension to the modelCriteria, using join after resolving the JoinType

[francois]

the joinFoo() generation was added in r1603

[lvanderree]

Hi Francois, welcome back already, you are too fast!

Last week and a half I had some other things to do at work, but today I had prepared some things regarding this issue, and could probably have finished it tomorrow.

The only thing that differs is, that I wasn't adding the decision making for the joinType based on the isColumnRequired method (which I like), but was adding support for the defaultJoin Type in the schema.yml.

I would like to add this support as well, to overrule the result of isColumnRequired if defaultJoin is set in the schema. I think I can finish this tomorrow, would you add this to to propel as well? What is the release schedule and are you interested in this feature?

[francois]

Being able to override the result of the isColumnsRequired() on a per fk basis would be great. If you can provide a patch including tests within the next week, it'll make its way into Propel 1.5.

[lvanderree]

Nice, I can probably finish this tomorrow, or at least on Wednesday! And of course covered by unit-tests.

[lvanderree]

Hi Francois, I was looking at your unit-tests and noticed you have added functionality to support left, inner and right joins by magic methodNames, like

$c = new ModelCriteria('bookstore', 'Book', 'b');

$c->leftJoin('b.Author a'); $c->innerJoin('b.Author a'); $c->rightJoin('b.Author a');

But I think this feature hasn't been (Php)Documented

is this right?

I've almost finished the additional functionality to define the defaultJoin in the schema.yml

[francois]

It's documented in docs/reference/ModelCriteria.txt, line 473. As for phpDoc, I don't know where to add it. Do you have an idea?

[lvanderree]

I've finished the patch to allow for defining the defaultJoin in the schema.yml

it has been provided in the attachement.

I will take a look at the phpDoc in my train and will come back to that.

[lvanderree]

Support for defining the defaultJoin in the schema.yml

[francois]

Great patch. Could you just refactor the repeated part in the QueryBuilder to the ForeignKey table? Something like:

/** 
 * Gets the defaultJoin for this foreign key 
 * @return     string 
 */ 
public function getDefaultJoin() 
{
  if (null === $this->defaultJoin) {
    $this->defaultJoin = $this->isLocalColumnsRequired() ? 'Criteria::INNER_JOIN' : 'Criteria::LEFT_JOIN';
  }
  return $this->defaultJoin; 
} 

[lvanderree]

agree, I will do this tomorrow morning.

[lvanderree]

New version of the patch to provide support for defining a defaultJoin Type in the schema.yml This patch also includes PhpDoc signatures for the magic left- ,right- & innerJoin(XXX) methods

[lvanderree]

Hi François,

I've attached a new patch.

I added the joinType-Resolve method to the OMBuilder, but I don't know if you want it there or in the QueryBuilder.

I've also wrote some phpDocs to support the magic left, right and innerJoin(XXX) methods. Do you like this implementation, or did you have something else in mind?

[francois]

That's fine. You have commits rights, would you like to commit the patch yourself?

[lvanderree]

Ok, great! I didn't knew I had commit rights, but will do so right now...

[lvanderree]

(In [1618]) [1.5] Added support to define the defaultJoin Type for a relation in the schema and added phpDoc for the magic [left|right|inner]Join(XXX)-methods (closes #870)

[francois]

(In [1754]) [1.5] Documented custom defaultJoin type (refs #870) (closes #936)