DataMapper ORM v1.8.2

[eluser]WanWizard[/eluser]
Additional benefit: it allows you to retrieve the join data without having to include the related table in the join.

A many to many query always joins both the related and the relation table...

[eluser]AAtticus[/eluser]
I'm still stuck with my question posted in the old topic.


So I have a RealEstate class, a Position class and a Client class.
A RealEstate has several Positions attached to it (you can look at these as groups. These Positions contain Clients. For example, in RealEstate APPARTMENT1 the Client John Doe is in the Position as owner. But he can be in another position for another RealEstate!

In my database I have

- RealEstates
- Clients
- Positions
- Clients_Positions (many to many)
- Positions_RealEstates (one to many)

How can I get all the Clients that have a Position at a given RealEstate.

Is my design incorrect? Should there also be a Clients_RealEstates table? But How can I ever connect those 3 classes?

[eluser]WanWizard[/eluser]
So, to check if I understand it correct:

RealEstate has_many Position, Position has_many RealEstate (as an owner can have multiple properties?)
Position has_many Client, Client has_many Position (something can have multiple owners, a client can own multiple properties?)

So far so good? If so, nothing special about this design.

So you need to create three models, for RealEstate, Position and Client. You will need table 'realestates', 'positions' and 'clients' for those models (tables are plural and lowercase).

To create the many-to-many, a relation table (or join table, or junction table) is required, which Datamapper constructs from the table names of both models in the relation. In this case, you need relation tables called 'clients_positions' and 'positions_realestates' as they are joined an alphabetical order.

'clients_positions' should contain the columns 'id', 'client_id' and 'position_id', 'positions_realestates' should contain the columns 'id', 'position_id' and 'realestate_id'.

This should be it. The models themselfs are simple, no advanced stuff needed, this is a basic Datamapper design.

[eluser]AAtticus[/eluser]
Hi WanWizard.

Thanks for you reply!
I already have these models setup and the database tables were already designed like you mentioned in your posts.

This is how I save a relationship between a client in a specific position attached to a real estate
(this function is in the REALESTATE controller

Code:

function add_client($id=-1) {
  $o = new RealEsate();
  $o->get_where(array('id' => $id));
  if(is_numeric($o->id)) {

   $c = new Client();
   $c->where('id', $this->input->post('client_id'))->get();
   $r = new Position();
   $r->where('id', $this->input->post('position_id'))->get();
   $r->save($c);

   if($o->save($r)) {
    $this->session->set_flashdata('message_type', 'notice success');
          $this->session->set_flashdata('message', 'Position for '. $c->name . ' ' . $c->first_name . ' as ' . $r->display_name . ' saved.');
          redirect($this->session->userdata('previous_page').'#tab-clients');
         }
  } else {
   $this->session->set_flashdata('message_type', 'notice error');
         $this->session->set_flashdata('message', 'Invalid Id');
         redirect('admin/realestates/');
  }
}

And it works, it saved the relationship between the project and the position and between the position and the client.
But how can I display a list of Clients for a RealEstate because there is only an indirect relation?

I know how I can get a list of POSITIONS attached to a REALESTATE (this is basic get_iterated()) but how can I get a list of POSITIONS with in that list the CLIENTS but ONLY for that specific REALESTATE?

Basically I know how the get the positions for a given realestate id, but I also want the clients from the clients table if the only thing I have is the realestate id.

[eluser]WanWizard[/eluser]
The same way as you get the position:

Code:

$o = new RealEsate();
$o->get_by_id(1);

foreach($o->position as $p)
{
    $p->client->get();
}

[eluser]AAtticus[/eluser]
// ignore, didn't see your reply until now!

in addition to the above.
In a normal setup I would do this in my database.
I'd have a table called Clients_Positions_RealEstates where you had
[id] [client_id] [position_id] [realestate_id]

So in one row the entire connection between a client, his position at a given real estate is saved.

But how to convert this to datamapper where I can only relate two tables at once?

[eluser]WanWizard[/eluser]
You can't have three tables in a relation, a relation is always between two (as in real life ).

Technically you can (have multiple relations that use the same relation table), but Datamapper will not be aware of those, so if you delete a relation, Datamapper will delete the record in the relation table even if it is still needed to maintain other relations.

What you can do is consider the relation between realestate and client primary. In this case you don't need to maintain the relation for the position if you unlink the client from the property.

So you can do like I've described above, and create a model for the relationship table too. You then get a one-to-many between the relationship table and client, between the relationship table and position, and between the relationship table and realestate.

You can then do

Code:

$r = new Realestate();
$r->get_by_id(1);

// get the related info (using relatedtable as an example of the model name)
$r->relatedtable->include_related('client')->include_related('position')->get();

this will return a join result with both the client and the position data.

[eluser]AAtticus[/eluser]
Thanks for your additional info.

So would the relationship table be positions_realestates or clients_positions?
And furthermore, should it be has_many("relationshipmodel") or has_one("relationshipmodel")?

[eluser]WanWizard[/eluser]
In this construction you don't have a relationship table anymore, all relations have become one-to-many:

relatedtable has_one realestate, realestate has_many relatedtable
relatedtable has_one position, position has_many relatedtable
relatedtable has_one client, client has_many relatedtable

so come up with a name for the relatedtable model, and name the table accordingly.

[eluser]AAtticus[/eluser]
Thanks WanWizard, I'll give it a try.