Jump to content

Single Responsibility and SQL Joins?


fry2010

Recommended Posts

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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..

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.