Single Responsibility and SQL Joins?

[fry2010]

How could I create a class that has a single responsibility yet also can obtain data from other objects, whilst using joins?

 

I'll give an example:

 

I am obtaining user account data and then wish to grab their contact information and profile information, both of which are in different tables. My table structure:

 

create table user_accounts (
userAccountId mediumint( unsigned NOT NULL AUTO_INCREMENT,
email varchar(255) not null,
password varchar(40) not null
);

create table exists profile (
groupAccountId mediumint( unsigned not null,
avatar varchar(160) not null DEFAULT '',
groupName varchar(160) not null DEFAULT ''
);

create table user_contact_details (
contactDetailId mediumint( unsigned not null AUTO_INCREMENT,
userAccountId mediumint( unsigned not null,
addressLine1 varchar(60) not null DEFAULT '',
street varchar(60) not null DEFAULT '',
postcode varchar( not null DEFAULT 1,
telephone varchar(15) not null DEFAULT '',
mobile varchar(15) not null DEFAULT ''
)

 

So, if I want to grab information from all tables, but grab them from one class, how would this reflect on single responsibility?

 

How would you go about designing a class for this?

[gristoi]

You could take a look at Object Relational Mapping. A good example is the Doctrine project http://www.doctrine-project.org/

[fry2010]

I have looked at doctrine before when looking into symphony. It looks like a good solution, but I am still not sure how it would deal with joined queries. Could you possibly direct me to the correct part in the documentation that relates to that or even an example?

[ignace]

Your real question is: how do I cleanly separate an SQL query from the code that creates the objects. There are a few ways you can do this, here is one:

 

class UserRepository
{
    private $dbAdapter;
    private $factory;
    private $identityMap;
    
    public function findProfileById($id) {
        if ($this->identityMap->has($id)) {
            return $this->identityMap->get($id);
        }
        
        $sql = $this->dbAdapter->select('..')->from('..')->join('..', '..')->where()->equals('id', $id);
        $row = $this->dbAdapter->query($sql);
        $inst = $this->factory->newInstanceFromDbResult($row);
        
        $this->identityMap->add($id, $inst);
        return $inst;
    }
}

class UserFactory
{
    public function newInstanceFromDbResult(DbResult $foo) {
        // creation code
        return $instance;
    }
}

 

The factory creates the aggregate root and any relating objects like UserAccount, Profile, and UserContactDetail. The Repository is an in-memory collection of aggregate roots. It hits the database whenever you try to load a root it does not contain.

 

Even if you would opt to go with Doctrine, you would still at some point have to write the query and do all the necessary joins because if you don't and trying to access a not-yet-loaded field will hit the database to load ALL the necessary data.

[fry2010]

ok. Cheers guys. As always some great insight.

Igance, that looks like a nice solution. So would I be right in saying that, if a user updated their contact table, then in order to get these new values I would have to load ALL their account info with this proposed method? Basically I just create an sql to deal with all account resources (joins all tables) even though I may not want table 'x', I will get its columns anyway?

Sounds like a clean solution, although not as fully optimized as possible if I understand your method correctly..

 

unless of course I modified it to deal with more granular requests..

[ignace]

No. You only load what you need. Your factory will build the proper objects in the form of a Proxy or a Ghost object if need be. So that if for example you need the profile information, but you did not load it the first time (a Profile object with only an ID aka Ghost object) it will hit the database and load the profile information.

 

http://en.wikipedia.org/wiki/Lazy_loading#Ghost

[fry2010]

ok, obviously I didn't get my head round it right the first time. Looks like an elegant solution. Cheers ignace!