thekid/rfc — Issue #129: RFC 0129: Criteria and Joins
Author: thekid
Created: 2011-10-09 15:17:22+00:00
State: open

Scope of Change
====
Extend the existing Criteria API for the possibility to join over multiple tables.

Rationale
====
- make joins over multiple tables
- the result is an object tree
- Dataset related datasets are prefetched with a single query
- grouping, sortorder and projections will be ignored when tables are joined

Functionality
====
The join result can be accessed like relations in rfc0096, but the database will only be queried once.

```php
setFetchMode(Fetchmode::join('Author'))
->setFetchMode(Fetchmode::join('Recipient'))
->setFetchMode(Fetchmode::join('Recipient->MmessageAuthor'))
;
foreach (Mmessage::getPeer()->doSelect($crit) as $Message) {
Console::writeLine('==> ', $Message);
Console::writeLine('Author =====> ', $Message->getAuthor());
Console::writeLine('Recipient ==> ', $Message->getRecipient());
Console::writeLine('Author of ==> ', $Message->getRecipient()
->getMmessageAuthorList()
);
}
?>
```

result:

```
==> de.schlund.db.rubentest.Mmessage@(0.96254200 1179486060){
[message_id PK,I] 2
[title ] 'foo'
[body ] 'foo'
[valid_from ] NULL
[expire_at ] Mon, 05 Mar 2007 12:15:50 +0100
[recipient_id ] 2
[author_id ] 1
}
Author =====> de.schlund.db.rubentest.Mperson@(0.96282200 1179486060){
[person_id PK,I] 1
[name ] 'Peter'
}
Recipient ==> de.schlund.db.rubentest.Mperson@(0.96295000 1179486060){
[person_id PK,I] 2
[name ] 'Paul'
}
Author of ==> [
0 => de.schlund.db.rubentest.Mmessage@(0.41309200 1179486585){
[message_id PK,I] 1
[title ] 'bar'
[body ] 'bar'
[valid_from ] NULL
[expire_at ] Mon, 05 Mar 2007 12:11:58 +0100
[recipient_id ] 1
[author_id ] 2
}
2 => de.schlund.db.rubentest.Mmessage@(0.41354300 1179486585){
[message_id PK,I] 3
[title ] 'buzz'
[body ] 'buzz'
[valid_from ] NULL
[expire_at ] Mon, 05 Mar 2007 12:15:50 +0100
[recipient_id ] 3
[author_id ] 2
}
]

```

Restrictions with joins
----

```php
setFetchMode(Fetchmode::join('Author'))
->setFetchMode(Fetchmode::join('Recipient->MmessageAuthor->Author'))
;

// fetch only Mmessages where the author id is 3
Mmessage::getPeer()->doSelect(clone($crit)
->add(Mmessage::column('author_id')->equal(3))
);

// fetch only Mmessages where the authors name is Peter
Mmessage::getPeer()->doSelect(clone($crit)
->add(Mmessage::column('Author->name')->equal('Peter'))
);

// fetch only Mmessages with Recipients who wrote Messages to Peter
Mmessage::getPeer()->doSelect(clone($crit)
->add(Mmessage::column('Recipient->MmessageAuthor->Recipient->name')->equal('Peter')
));
?>
```

It is mandatory to use the whole path, relative to the first table of the join, to address a field

Security considerations
====
n/a

Speed impact
====
- May be a bit slower than joins via rdbms.Statement, because joins are generated in realtime.
- faster than fetching relations like defined in rfc0096 because only one query will be used

Dependencies
====
Note:

```
To use this API, the database classes have to be regenerated!

```

- method isJoin was added to the interface rdbms.SQLExpression
- abstract method rdbms.DBConnection::getFormatter has to be added
- abstract method rdbms.DBConnection::identity has parameter $field now
- second parameter of the interface rdbms.criterion.Criterion has been changed from array to peer
- new interfacee rdbms.join.JoinExtractable
- rdbms.DataSet implements interface rdbms.join.JoinExtractable
- Naming Strategy of rfc0096
- SQLDialect of rfc0124
- deprecates the method rdbms.Peer::doJoin

Related documents
====
- rfc0096 http://xp-framework.net/rfc/0096.rfc.html
- rfc0124 http://xp-framework.net/rfc/0124.rfc.html
- xp-lab
http://experiments.xp-framework.net/?people,wagner,rfc,0129
- Joint patch for RFC0096, RFC0123, RFC0124 and RFC0129 against skeleton
http://xp-framework.net/rfc/contrib/rfc0096_0123_0124_0129/skeleton.diff
- Joint patch for RFC0096, RFC0123, RFC0124 and RFC0129 against ports
http://xp-framework.net/rfc/contrib/rfc0096_0123_0124_0129/ports.diff

Comments
====