Automatically joining tables without breaking defa

Question

The situation is as follows: I've got 2 models: 'Action' and 'User'. These models refer to the tables 'actions' and 'users', respectively.

My action table contains a column user_id. At this moment, I need an overview of all actions, and the users to which they are assigned to. When i use $action->fetchAll(), I only have the user ID, so I want to be able to join the data from the user model, preferably without making a call to findDependentRowset().

I thought about creating custom fetchAll(), fetchRow() and find() methods in my model, but this would break default behaviour.

What is the best way to solve this issue? Any help would be greatly appreciated.

Answer 1

You could always make a view in your database that does the join for you.

CREATE OR REPLACE VIEW VwAction AS
SELECT [columns]
  FROM action
  LEFT JOIN user
    ON user.id = action.user_id

Then just use

$vwAction->fetchAll();

Just remember that views in MySQL are read-only (assuming this is MySQL)

Answer 2

I designed and implemented the table-relationships feature in Zend Framework.

My first comment is that you wouldn't use findDependentRowset() anyway -- you'd use findParentRow() if the Action has a foreign key reference to User.

$actionTable = new Action();
$actionRowset = $actionTable->fetchAll();
foreach ($actionRowset as $actionRow) {
  $userRow = $actionRow->findParentRow('User');
}

Edit: In the loop, you now have an $actionRow and a $userRow object. You can write changes back to the database through either object by changing object fields and calling save() on the object.

You can also use the Zend_Db_Table_Select class (which was implemented after I left the project) to retrieve a Rowset based on a join between Action and User.

$actionTable = new Action();
$actionQuery = $actionTable->select()
  ->setIntegrityCheck(false) // allows joins
  ->from($actionTable)
  ->join('user', 'user.id = action.user_id');
$joinedRowset = $actionTable->fetchAll($actionQuery);
foreach ($joinedRowset as $joinedRow) {
  print_r($joinedRow->toArray());
}

Note that such a Rowset based on a join query is read-only. You cannot set field values in the Row objects and call save() to post changes back to the database.

Edit: There is no way to make an arbitrary joined result set writable. Consider a simple example based on the joined result set above:

action_id  action_type  user_id  user_name
   1          Buy          1       Bill
   2          Sell         1       Bill
   3          Buy          2       Aron
   4          Sell         2       Aron

Next for the row with action_id=1, I change one of the fields that came from the User object:

$joinedRow->user_name = 'William';
$joinedRow->save();

Questions: when I view the next row with action_id=2, should I see 'Bill' or 'William'? If 'William', does this mean that saving row 1 has to automatically update 'Bill' to 'William' in all other rows in this result set? Or does it mean that save() automatically re-runs the SQL query to get a refreshed result set from the database? What if the query is time-consuming?

Also consider the object-oriented design. Each Row is a separate object. Is it appropriate that calling save() on one object has the side effect of changing values in a separate object (even if they are part of the same collection of objects)? That seems like a form of Content Coupling to me.

The example above is a relatively simple query, but much more complex queries are also permitted. Zend_Db cannot analyze queries with the intention to tell writable results from read-only results. That's also why MySQL views are not updateable.

Answer 3

isn't creating a view sql table a good solution to make joint ? and after a simple table class to access it

I would think it's better if your logic is in sql than in php