Modeling objects with multiple table relationships in Zend Framework

Question

I'm toying with Zend Framework and trying to use the "QuickStart" guide against a website I'm making just to see how the process would work. Forgive me if this answer is obvious, hopefully someone experienced can shed some light on this.

I have three database tables:

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `email` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL default '',
  `first` varchar(128) NOT NULL default '',
  `last` varchar(128) NOT NULL default '',
  `gender` enum('M','F') default NULL,
  `birthyear` year(4) default NULL,
  `postal` varchar(16) default NULL,
  `auth_method` enum('Default','OpenID','Facebook','Disabled') NOT NULL default 'Default',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `user_password` (
  `user_id` int(11) NOT NULL,
  `password` varchar(16) NOT NULL default '',
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `user_metadata` (
  `user_id` int(11) NOT NULL default '0',
  `signup_date` datetime default NULL,
  `signup_ip` varchar(15) default NULL,
  `last_login_date` datetime default NULL,
  `last_login_ip` varchar(15) default NULL,
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I want to create a User model that uses all three tables in certain situations. E.g., the metadata table is accessed if/when the meta data is needed. The user_password table is accessed only if the 'Default' auth_method is set. I'll likely be adding a profile table later on that I would like to be able to access from the user model.

What is the best way to do this with ZF and why?

Very unclear on why this is down-voted: is there a problem with this question? I couldn't find a similar question that explain this in the detail I'm looking for. Let me know if I can be more clear. — Andy Baird
– Andy Baird, Commented Mar 12, 2009 at 13:34
Dunno, excess of data perhaps? You could replace all that SQL with brief description of tables. — vartec
– vartec, Commented Mar 12, 2009 at 13:39
@andybaird: your title is a little bit off. these are not multiple data sources, they're just multiple tables from one data source, your db. having many tables (like really loads) is a normal thing for big applications. — markus
– markus, Commented Mar 13, 2009 at 10:24

markus · Accepted Answer · 2009-03-13 11:28:31Z

23

class Users extends Zend_Db_Table_Abstract
{
    protected $_name = 'users';
    protected $_rowClass = 'User';
    protected $_dependentTables = array ('UserMetadata', 'UserPassword');

...

class UserMetadata extends Zend_Db_Table_Abstract
{
    protected $_name = 'user_metadata';
    protected $_referenceMap = array (
    'Users'=> array (
    'columns'=>'user_id',
    'refTableClass'=>'Users',
    'refColumns'=>'id'
    )
    );

...

class UserPassword extends Zend_Db_Table_Abstract
{
    protected $_name = 'user_password';
    protected $_referenceMap = array (
    'Users'=> array (
    'columns'=>'user_id',
    'refTableClass'=>'Users',
    'refColumns'=>'id'
    )
    );

Fetching data:

$id = //get your user id from somewhere

$users = new Users();
$user = $users->fetchRow('id=?', $id);
if ($user->authMethod == 0)
{
    $metadata = $user->findDependentRowset('UserMetadata')->current();
}

or

$user = $users->fetchRow($users->select()
              ->where('gender=?, 'M')
              ->order('email ASC');

... etc.

Inserting data:

$newRow = $users->fetchNew();
$newRow->email = [email protected];
$newRow->save();

or

$users = new Users();
$data = array('email'     => '[email protected]',
              'firstname' => 'Me');
$users->insert($data);

Updating:

$user->email = '[email protected]';
$user->save();

Deleting a row:

$user->delete();

Using transaction:

$db->beginTransaction();
$db->commit();
$db->rollback();

etc... it's all in the ZF Manual!

edited Mar 13, 2009 at 11:28

answered Mar 12, 2009 at 21:53

markus

40.8k23 gold badges100 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andy Baird Over a year ago

This makes the most sense to me - so how do you handle an insert into the users table? Logically, there should be a user_metadata row created as well. Can I handle that on the model level somehow?

markus Over a year ago

The logics to create a meta-data row when creating a user-row you have to write yourself. but that's easy. if you want to do it the clean way use transactions...

Andy Baird Over a year ago

This is exactly what I ended up doing, and it worked quite well. Thank you.

vartec · Accepted Answer · 2009-03-12 13:37:09Z

2

Basically instead of using Zend_Db_Table use more general Zend_Db_Select or Zend_Db_Statement to retrieve data.

BTW. You might want to access password data not directly in User model, but rather in your User auth class derived from Zend_Auth_Adapter.

answered Mar 12, 2009 at 13:37

vartec

135k38 gold badges227 silver badges248 bronze badges

3 Comments

Andy Baird Over a year ago

I don't plan on accessing password data via user model, except to update it. Doesn't using Zend_Db_Select completely circumvent the idea of modeling user data as a single object?

vartec Over a year ago

It's not ORM, there's no need for 1-to-1 class-table mapping.

vartec Over a year ago

BTW. the 1=1 mapping would be possible if your DB was in 3NF, your problem comes from the fact, that you denormalized your DB, splitting user data into 3 tables.

rg88 · Accepted Answer · 2009-03-12 21:36:10Z

2

In a nutshell I would create a model for each table, not one model that accesses all three. I would then define relationships between the tables.

To be honest it seems not very "DRY" to have to create a model for each table but that is what I see done repeatedly in the various examples online and it is what I have done in the handful of projects that I have created with the Zend Framework. If anyone has a better way of handling this I hope the will post it here.

answered Mar 12, 2009 at 21:36

rg88

21k18 gold badges80 silver badges110 bronze badges

Comments

Adrian Grigore · Accepted Answer · 2009-03-12 16:01:03Z

0

I would say that the best way to do this with ZF is by using Doctrine.

answered Mar 12, 2009 at 16:01

Adrian Grigore

791 silver badge5 bronze badges

Comments

Saem · Accepted Answer · 2009-03-12 17:03:31Z

0

Just a few notes straight off the bat:

The first User table, seems more like a Person table, except for the auth method, but you could put that in the User_Password table, which you could probably rename User. The User_Metadata table seems like it could just be amalgamated with the User_Password/User table.

Now, even without those changes, you have three distinct tables, with three distinct concepts, if you model each of those separate as different classes, and then had a UserModel class as a facade of sorts to access each, it would likely make it easier.

answered Mar 12, 2009 at 17:03

Saem

3,41325 silver badges12 bronze badges

Collectives™ on Stack Overflow

Modeling objects with multiple table relationships in Zend Framework

5 Answers 5

3 Comments

3 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

3 Comments

Comments

Comments

Comments

Linked

Related