Many to Many relation where modUser is involved.

22 Posts

km88 Reply #1, 13 years, 1 month ago

I would like to create a Many to Many relation in xPDO. In my case between Users and Games, with the joining table Favorites. When following the Many to Many example in the docs, I have to add

<composite alias="Favorites" class="Favorites" local="id" foreign="user_id" cardinality="many" owner="local" />

to modUser. My question is, how can I add this line to the modUser schema? Do I have to extend modUser?

Games & Favorites schema:

Games (simplified)

<object class="Game" table="games" extends="xPDOObject">
  <field key="id" dbtype="int" precision="10" phptype="integer" null="false" index="pk" generated="native" />
  <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
  <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
    <column key="id" collation="A" null="false" />
  </index>

  <composite alias="Favorites" class="Favorites" local="id" foreign="game_id" cardinality="many" owner="local" />
</object>

Favorites

<object class="Favorites" table="favorites" extends="xPDOObject">
  <field key="id" dbtype="int" precision="10" phptype="integer" null="false" index="pk" generated="native" />
  <field key="user_id" dbtype="int" precision="10" phptype="integer" null="false" index="pk" generated="native" />
  <field key="game_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
  <field key="time" dbtype="datetime" phptype="datetime" null="true"/>
  <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
    <column key="id" collation="A" null="false" />
  </index>

  <aggregate alias="modUser" class="modUser" local="user_id" foreign="id" cardinality="one" owner="foreign" />
  <aggregate alias="Game" class="Game" local="game_id" foreign="id" cardinality="one" owner="foreign" />
</object>

This question has been answered by BobRay. See the first response.

24,544 Posts

BobRay Reply #2, 13 years, 1 month ago

You shouldn't need to modify the MODX schema (and it's strongly discouraged). Just extend the modUser class and put the relationships in the schema for your extended class. Once you generate the class and map files, xPDO will know about the relationships as soon as you load the your class and you can do stuff like:

$myUser->getMany('Favorites');

BTW, I'd recommend changing the Alias for favorites to 'User' rather than 'modUser'. xPDO aliases in MODX don't include 'mod' and they always start with an uppercase letter.

I'd also recommend using a prefix with an underscore for your tables just like the MODX tables (but with a different prefix).

One more recommendation: Consider extending xPDOSimpleObject instead of xPDOObject and leaving out the 'id' field in your schema (it can remain in the aliases). That way every class will already have an auto-increment 'id' field it inherits from xPDOSimpleObject.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

22 Posts

km88 Reply #3, 13 years, 1 month ago

Thanks for your reply! If I get it right, it would be like this (don't know for sure how to extend the modUser class):

Games

<object class="Game" table="games" extends="xPDOSimpleObject">
  <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
  <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
    <column key="id" collation="A" null="false" />
  </index>
 
  <composite alias="Favorites" class="Favorites" local="id" foreign="game_id" cardinality="many" owner="local" />
</object>

Favorites

<object class="Favorites" table="favorites" extends="xPDOSimpleObject">
  <field key="user_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
  <field key="game_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
  <field key="time" dbtype="datetime" phptype="datetime" null="true"/>
  <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
    <column key="id" collation="A" null="false" />
  </index>
 
  <aggregate alias="User" class="modUser" local="user_id" foreign="id" cardinality="one" owner="foreign" />
  <aggregate alias="Game" class="Game" local="game_id" foreign="id" cardinality="one" owner="foreign" />
</object>

Extending user

<object class="extUser" extends="modUser">
  <composite alias="Favorites" class="Favorites" local="id" foreign="user_id" cardinality="many" owner="local" />
</object>

Also, I would like to set the primary key on two columns for Favorites (on user_id and game_id, since this combination is always unique). Can I achieve this like so (problably I have to set it to xPDOObject, since xPDOSimpleObject already sets a primary key?):

<object class="Favorites" table="favorites" extends="xPDOObject">
  <field key="id" dbtype="int" precision="10" phptype="integer" null="false" index="pk" generated="native" />
  <field key="user_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
  <field key="game_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
  <field key="time" dbtype="datetime" phptype="datetime" null="true"/>
  <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
    <column key="user_id" collation="A" null="false" />
    <column key="game_id" collation="A" null="false" />
  </index>
 
  <aggregate alias="User" class="modUser" local="user_id" foreign="id" cardinality="one" owner="foreign" />
  <aggregate alias="Game" class="Game" local="game_id" foreign="id" cardinality="one" owner="foreign" />
</object>

MODX Staff
10,725 Posts

opengeek Reply #4, 13 years, 1 month ago

Looks good from 10,000ft. You don't need the id column in Favorites if user_id+game_id is the Primary Key.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

22 Posts

km88 Reply #5, 13 years, 1 month ago

Quote from: opengeek at Sep 12, 2013, 08:29 AM

Looks good from 10,000ft. You don't need the id column in Favorites if user_id+game_id is the Primary Key.

Thanks! I just have one remaining problem, what is the correct way insert a record in the "Favorites" table? (In other words, create a favorite game).

I have the following code, but I doubt this is correct, regarding the relations with extUser (extending modUser) and Games.

$userfav = $this->modx->newObject('Favorites',array(
	'game_id' => $gameid,
	'user_id' => $userid
));
$userfav->save();

Schema

// Games
<object class="Game" table="games" extends="xPDOSimpleObject">
  <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default=""/>
  <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
    <column key="id" collation="A" null="false" />
  </index>
  <composite alias="Favorites" class="Favorites" local="id" foreign="game_id" cardinality="many" owner="local" />
</object>

// Extending modUser
<object class="extUser" extends="modUser">
  <composite alias="Favorites" class="Favorites" local="id" foreign="user_id" cardinality="many" owner="local" />
</object>

// Favorites
<object class="Favorites" table="favorites" extends="xPDOObject">
  <field key="user_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
  <field key="game_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
  <field key="time" dbtype="datetime" phptype="datetime" null="true"/>
  <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
    <column key="user_id" collation="A" null="false" />
    <column key="game_id" collation="A" null="false" />
  </index>
  <aggregate alias="User" class="extUser" local="user_id" foreign="id" cardinality="one" owner="foreign" />
  <aggregate alias="Game" class="Game" local="game_id" foreign="id" cardinality="one" owner="foreign" />
</object>

24,544 Posts

BobRay Reply #6, 13 years, 1 month ago

Looks perfect to me. wink

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

22 Posts

km88 Reply #7, 13 years ago

Quote from: BobRay at Sep 17, 2013, 03:40 PM

Looks perfect to me.

It saves the value in the database, however when I delete a user or a game, the relational favorites are not getting deleted.

[2013-09-18 08:53:57] (ERROR @ /assets/components/games/connector.php) Error removing dependent object: Array
(
    [user_id] => 1
    [game_id] => 266
    [time] => 2013-09-18 08:53:38
)

MODX Staff
10,725 Posts

opengeek Reply #8, 13 years ago

Quote from: km88 at Sep 18, 2013, 01:58 AM

Quote from: BobRay at Sep 17, 2013, 03:40 PM
Looks perfect to me.

It saves the value in the database, however when I delete a user or a game, the relational favorites are not getting deleted.
[2013-09-18 08:53:57] (ERROR @ /assets/components/games/connector.php) Error removing dependent object: Array
(
    [user_id] => 1
    [game_id] => 266
    [time] => 2013-09-18 08:53:38
)

How are you deleting the user/game records?

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

22 Posts

km88 Reply #9, 13 years ago

In my CMP, using this processor (like in the CMP tutorial):

class GameRemoveProcessor extends modObjectRemoveProcessor {
    public $classKey = 'Game';
    public $languageTopics = array('games:default');
    public $objectType = 'games.game';
}
return 'GameRemoveProcessor';

In my component, I'm also using a one-to-one relation. When I delete a record (game), the record in the relational one-to-one table is succesfully removed, but fails for the many-to-many relation.

22 Posts

km88 Reply #10, 13 years ago

Quote from: opengeek at Sep 18, 2013, 06:59 AM

Quote from: km88 at Sep 18, 2013, 01:58 AM
Quote from: BobRay at Sep 17, 2013, 03:40 PM
Looks perfect to me.

It saves the value in the database, however when I delete a user or a game, the relational favorites are not getting deleted.
[2013-09-18 08:53:57] (ERROR @ /assets/components/games/connector.php) Error removing dependent object: Array
(
    [user_id] => 1
    [game_id] => 266
    [time] => 2013-09-18 08:53:38
)
How are you deleting the user/game records?

Did I set the primary keys right for Favorites? Or could it be something with the extUser relation?

Answered Many to Many relation where modUser is involved.