Thread

Normally you would set it up as a many to many between User and Site, with a relationship table “sites_users”. And with a many to many between this relationship table and Group.

Alternatively you could setup a relationship table with a compound key, where you create relations between any combination of user_id and site_id, an a Group.

But both scenario’s are currently not supported by DataMapper.

As a workaround, you could create a model for your “sites_users” table, and have that relate to Group. You can then define a one to many between User and this model, and between Site and this model, and give this model a many to many relation with Group

You can than then get a user, get the related “sites_users” record on site_id, and with that record, fetch all related groups.

I hope I made this clear, it’s quite a complicated workaround.

WanWizard, thanks for the update for sparks to 1.8.2.1. It works great now.

One thing I did to keep the code completely separated between the spark and my application folder is to add a DATAMAPPERPATH constant to the third_party directory in my web/index.php.

/*
 * --------------------------------------------------------------------
 * LOAD THE DATAMAPPER BOOTSTRAP FILE
 * -------------------------------------------------------------------- *
 */
define('DATAMAPPERPATH', '../sparks/DataMapper-ORM/1.8.2/');
require_once DATAMAPPERPATH.'third_party/datamapper/bootstrap.php';

Then a few changes to the paths in the code to make this work. Attached is the tarball of the third_party directory with all the code changes for anyone running Datamapper from sparks.

WARNING: This only applies to those running Datamapper as a spark and it allows you to keep all Datamapper code within the spark directory without copying code into your application directory. If you installed Datamapper from the downloaded zip file, this will break you install.

Good idea, thanks for the update!

I’ve added a check on the existence of DATAMAPPERPATH to bootstrap.php, and if not defined, set it to APPPATH. This way I only have to maintain one set of files for both the normal download and the spark.

I figured you would have an elegant way of integrating that in. Thanks!

I hope I made this clear, it’s quite a complicated workaround.

Yup, definitely clear enough. Thanks for the speedy reply, I’ll give your proposed solution a try now. Obviously the first two options would be preferred to the complicated workaround so I would be interested to know if you are planning to support this functionality in DataMapper ORM 2?

Yes, it is on the feature list.

In v2, relationship tables are not “hidden” anymore, they will require a model like all other tables. When you define a many-to-many relation, Datamapper will internally convert that to two one-to-many relations with the relationship table model in the middle, holding the foreign keys.

The relationship table model will become an object like all others, which also means no more clumsy workarounds for things like include_join_fields.Instead, you can just access the properties of the object like for all other DM models…

Yes, it is on the feature list.

That’s fantastic news, makes for a much more elegant solution - thanks! Let us know when you think DataMapper ORM 2 is ready for beta testing as I’d be more than happy to help.

than you, i will test as soos as i can

I hope I made this clear, it’s quite a complicated workaround.

Bah! I thought that your explanation was quite clear but I have been wandering around in circles for hours now and have completely confused myself!!

Can you tell me if this is what you had in mind, or am I barking up the wrong tree?

--------------------
Table: users
  id
  ...

Model: User
  has_one:
  has_many: site, site_user

--------------------
Table: sites
  id
  ...

Model: Site
  has_one:
  has_many: user, site_user

--------------------
Table: groups
  id
  ...

Model: Group
  has_one:
  has_many: site_user

--------------------
Table: sites_users
  id
  user_id
  site_id

Model: Site_User
  table: sites_users
  has_one:
  has_many: group

--------------------
Table: groups_sites_users
  id
  site_user_id
  group_id

Then with all of the above, would I get the groups for a particular user on a site like so?

$user = new User();
$user->where('id', 1)->get();

$site = $user->site->where_related_site_user('site_id', 1);

$groups = $site->groups->get();

I would say

$group = new Group();

// gets the groups of site 1, user 1
$group->where_related_site_user('site_id', 1)->where_related_site_user('user_id', 1)->get();

Alternatively, something like this

$user = new User();
$user->where('id', 1)->get();

$site = new Site();
$site->where('id', 1)->get();

$group = new Group();

// gets the groups of site 1, user 1
$group->where_related_site_user($site)->where_related_site_user($user)->get();

Ah, and don’t forget: Site_User needs a has_one relation to User and Site, otherwise it doesn’t have a mapping back to the models the the foreign keys point to.

It might also be that Datamapper can’t determine the correct table name for Site_User due to the underscore. In that case set the $table property in the model to override the detection mechanism.

Maybe pick easier model names as well, so you don’t have those long ones with underscores…

Now, I’ve done this to myself previous where my (lack of) understanding of DM relegated me to build some functions which turned out to be unnecessary.

Therefore, I must ask, for my sanity.. 😉

If you have a many-to-many relationship like shows & genres

  1 show can have n+ genres related to it
  1 genre can have n+ shows related to it

ie:
  Community [dark comedy, comedy, college, meta]
  Arrested Development [dark comedy, comedy, adult]

you cannot rely strictly on save() to remove existing relations?

$show->save($genres);

..would not remove ‘college’ from ‘Community’ just because it wasn’t within $genres

Did that make any sense?

[EXPANSION]
In my CRUD, I’m trying remove ‘college’ from the table ‘genres_shows’ by simply updating the $show->get_by_name(‘Community’); object with an object of $genre->where_in(‘name’, ‘dark comedy, comedy, meta’);

So, I’m thinking I have to compare what is existing in the DB and what was passed through _POST by user, in order to remove the erroneous $genre->get_by_name(‘college’); record.  Otherwise, DM just assumes you’re ADDING related records to the object.. not REPLACING.

Is my understanding correct?

Correct, save() only saves, it doesn’t delete.

You can delete existing relations by fetching them, and then delete them all:

// delete all old relations
$show->genres->get();
$show->delete($show->genres->all);

// and save the new ones
$show->save($genres);

You might want to encapsulate this in a transaction to be on the save side. If you do, use standard CI transaction calls, DM’s transaction method do an implied commit after each action.

Ahh, thanks Wiz.. I had that thought but had the wrong syntax and was deleting all genre relations!  D’oh!

I will do what you’ve suggested here and wrap in a CI transaction.  Cheers mate!  Oh, also, if I’ve made changes to htmlform helper that community might appreciate, do you have a bitbucket for that?  Or should I pass them along to you (I know you mention you’re not maintaining it.. I guess I could fork it…?)

With 1.8.2, htmlform was updated so the example application worked again.

The repo is on bitbucket (http://bitbucket.org/WanWizard/Datamapper), feel free to fork and send pull requests. Make sure you don’t break the example application when you do.

Cool beans, thanks Wiz.

Now friends, if you have the misfortune of being saddled w/ MSSQL (microsoft sql server) for your DB, pagination using get_paged and get_paged_iterated will not work (because of MS exclusion of a LIMIT function)!  That is, until you update your SQL driver with the code from this post:

http://ellislab.com/forums/viewthread/160626/

#yourwelcome

=P