I use CakePHP 3.0.10 and I'm confused about eager associations loading.
Documentation says (http://book.cakephp.org/3.0/en/orm/entities.html#lazy-loading-associations):
Eager loading uses joins (where possible) to fetch data from the database in as few queries as possible. When a separate query is required, like in the case of a HasMany association, a single query is emitted to fetch all the associated data for the current set of objects.
and (http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#eager-loading-associations):
The queries generated by eager loading can better leverage joins, allowing more efficient queries to be made. In CakePHP you define eager loaded associations using the ‘contain’ method
Example:
$query = $articles->find('all');
$query->contain(['Authors', 'Comments']);
As I understand, this code should create one SQL query with two 'join' statements.
In my project I have two associated tables, but even if I use 'contain' method, it creates two SQL queries.
To be more specific:
class UsersTable extends Table
{
public function initialize(array $config)
{
$this->table('users');
$this->displayField('id');
$this->primaryKey('id');
$this->addBehavior('Timestamp');
$this->hasMany('Friends', [
'foreignKey' => 'user_id'
]);
}
}
class FriendsTable extends BaseTable
{
public function initialize(array $config)
{
$this->table('friends');
$this->displayField('id');
$this->primaryKey('id');
$this->belongsTo('Users', [
'foreignKey' => 'user_id',
'joinType' => 'INNER'
]);
}
}
'friends' table in database has a constraint:
"users_friends_fk1" FOREIGN KEY (user_id) REFERENCES users(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
In my controller:
TableRegistry::get('Users')->find()->contain('Friends')->toArray();
And what I see in queries.log after executing this statement:
2015-07-31 18:27:28 Debug: SELECT Users.id AS "Users__id", Users.username AS "Users__username", Users.password AS "Users__password" FROM users Users
2015-07-31 18:27:28 Debug: SELECT Friends.id AS "Friends__id", Friends.user_id AS "Friends__user_id", Friends.friend_id AS "Friends__friend_id" FROM friends Friends WHERE Friends.user_id in (1,6,4,2,3,5,7,8)
I've excpeted one SQL query with one join.
Have I misunderstood the way that it should work? Or made I a mistake in my code?