Hi,
I've just started learning Phalcon, so forgive me if I'm posting an already discussed topic.
What I'd like to achieve with the model hierarchy, is to load multiple entity records organized in the expected model hieararchy. Maybe it's not clear, I show a very basic sample.
Let's have a User and an Address entity where a user can have multiple addresses. The generated models:
class User extends \Phalcon\Mvc\Model
{
public $id;
public $name;
public function initialize()
{
$this->hasMany('id', 'Address', 'user_id', NULL);
}
}
class Address extends \Phalcon\Mvc\Model
{
public $id;
public $city;
public $user_id;
public function initialize()
{
$this->belongsTo('user_id', 'User', 'id', NULL);
}
}My task would be to list all users with all their addresses. So what I'd like is to go through all User records, display their name and in a list, display city fields from ALL of their address records.
First thought, let's query User records:
$users = User::find();
foreach ($users as $user)
{
echo "\n" . $user->name . ", cities: ";
foreach ($user->address as $address)
echo $address->city . ", ";
}It's trivial, the output is generated as expected. What's not good with this solution is the number of SQL commands generated! This way the addresses are lazy loaded, therefore extra SQL query is generated for EACH user record. For long lists (let's say 100 user records) we would have 100 extra SQL commands!
I think it arises 2 problems:
- performance problems (too many database activity because of lazy loading, for many users it could kill the db)
- integrity problems
1st is obvious I think, 2nd means that while I go through all the user records, some of them could be deleted meanwhile, and when I reach such deleted user, no addresses will be loaded by the lazy $user->address, I will see that this user exists but has no address. But that's not true, that user doesn't exists... So the integrity of my data is corrupted! I maybe could avoid it be special isolation levels and putting the whole processing in transaction (by I guess only for multi versioning databases).
What I imagine to work, would solve both problems.
I'm looking for a solution where only one SQL is generated, and all the records are loaded in User records and all User records have all corresponding Address records loaded in their 'address' properties (as the hierachy is constructed).
I've tried with PHQL inner joining, I see the result but each entity is loaded independently in the result set, couldn't find a solution where the hierarchy is loaded up fully in their corresponding list properties.
What I'd imagine is a way to tell PHSQL to loaded User records, load it's 'address' list as well (could befurther filtered as well?), this way a full hierarchy could be described that we'd like to load and work with and could avoid lazy loading each lists (and of course 'hasone', single references as well)!
I know, I could trick a few things (load users, gather their IDs, issue only one more SQL with the gathered IDs and load all the needed address record), but none of them would solve organizing them in the model hierarchy and not even persistency which I have with the mentioned basic method.
Is it possible somehow to solve this example by avoiding lazy loading and executing only one query? Of course I'm looking for a general solution for more complex hierarchies (even with multiple relations) but this sample could be a good start to have a solution for.
