Topic: Dealing with unary relations in Rails...

I'm running across a situation where I have something roughly analagous to the following setup:

class Entity < ActiveRecord::Base
  has_many :entity_components
  has_many :items, :through=>:entity_components
end
class EntityComponent < ActiveRecord::Base
  belongs_to :entity
  belongs_to :item
end
class Item < ActiveRecord::Base
  has_many :entity_components
  has_many :entities, :through=>:entity_components
end

The DB schema is (actually done via migrations, but...)
CREATE TABLE entities (
  id SERIAL PRIMARY KEY
);
CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  item_name TEXT NOT NULL,
  ...
);
CREATE TABLE entity_components (
  id SERIAL PRIMARY KEY,
  entity_id INT NOT NULL REFERENCES entities ON UPDATE CASCADE ON DELETE CASCADE,
  item_id INT NOT NULL REFERENCES items ON UPDATE CASCADE ON DELETE CASCADE,
  ...
);

Note that the entities relation(table) is unary -- its only an autogenerated primary key.   There is no meaningful content that can be directly tagged to the entity -- though entities need to be linked to from other relations. Rails doesn't seem to like this.  I've played with it in the console and Entity.create or Entity.new.save both generate errors, the generated SQL is "INSERT INTO entities () VALUES ()".  Inspecting the contents of Entity.new shows that it hasn't assigned a new id to the item and thus has no attributes to save.

Now I could fix this by adding a dummy field to the entities table and infact adding a

add_column :entities, :dummy, :int, :null=>false, :default=>1

works and argumentless Entity.new or Entity.create now works, so having a table with all the fields autogenerated/defaulted seems to work.  However its a hack that I would prefer to avoid.  Does anyone have any experience with unary relations in rails?

My RoR journey  -- thoughts on learning RoR and lessons learned in applying TDD and agile practices.

Re: Dealing with unary relations in Rails...

Hmm, I can't duplicate the problem in MySQL version 5.0.18. What version are you running?

mysql> CREATE TABLE entities (
    ->   id SERIAL PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE items (
    ->   id SERIAL PRIMARY KEY,
    ->   item_name TEXT NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE entity_components (
    ->   id SERIAL PRIMARY KEY,
    ->   entity_id INT NOT NULL REFERENCES entities ON UPDATE CASCADE ON DELETE CASCADE,
    ->   item_id INT NOT NULL REFERENCES items ON UPDATE CASCADE ON DELETE CASCADE
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO entities () VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM entities;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

Last edited by ryanb (2006-08-04 11:38:18)

Railscasts - Free Ruby on Rails Screencasts

Re: Dealing with unary relations in Rails...

I might just be waxing semantics here, but a unary relationship refers to a relationship within a single table, not a relationship with a table with a single row. As for your problem, I wonder if there is anyway that you could reconfigure your schema so that you do not have this arbitrary "entity" object. I think if you are stuck with this schema then you are probably doing the best with what Rails has to offer, but it seems as though there would have to be something else.

Something you could do is add created_at and updated_at DATETIME columns. That way you would have more than the ID field, but you wouldn't have to specify any null information.

Most code examples are usually pulled out of the air and not tested. Use at your own risk!

Re: Dealing with unary relations in Rails...

ryanb wrote:

Hmm, I can't duplicate the problem in MySQL version 5.0.18. What version are you running?

I'm running PostGreSQL, not MySQL.  I'll have to test from directly within psql to see if its a pgsql issue or if its the postgresql-adapter that's getting confused.  [Edit:  It appears to be an issue with PostGreSQL itself.]

reedy wrote:

I might just be waxing semantics here, but a unary relationship refers to a relationship within a single table, not a relationship with a table with a single row. As for your problem, I wonder if there is anyway that you could reconfigure your schema so that you do not have this arbitrary "entity" object. I think if you are stuck with this schema then you are probably doing the best with what Rails has to offer, but it seems as though there would have to be something else.

I'd disagree with your definition of the semantics in this issue.  Or at least beg to point out that its a "unary relation" not a unary "relationship".  Relation is the preferred term by many database scientist to refer to what SQL deems a "table" (but relation does have a few nice theoretical properties that SQL table's don't posses.)

How would you re-organize a schema when you need a referenceable, but un-named aggregate (and non-identifiable aside from its composition.)

Last edited by NielsenE (2006-08-04 12:29:01)

My RoR journey  -- thoughts on learning RoR and lessons learned in applying TDD and agile practices.

Re: Dealing with unary relations in Rails...

If there's another way to insert a row into PostgreSQL without setting any values, I'd submit a ticket/patch to http://dev.rubyonrails.org (if the trac will stay up long enough...sigh)

Railscasts - Free Ruby on Rails Screencasts

Re: Dealing with unary relations in Rails...

Yup, either
INSERT INTO table DEFAULT VALUES; or
INSERT INTO table (id) VALUES (DEFAULT);

(Do either of those work in MySQL as well?)

My RoR journey  -- thoughts on learning RoR and lessons learned in applying TDD and agile practices.

Re: Dealing with unary relations in Rails...

First one doesn't work, second one does.

mysql> INSERT INTO entries DEFAULT VALUES;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT VALUES' at line 1
mysql> INSERT INTO entries (id) VALUES (DEFAULT);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM entries;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)


I'm not exactly sure how ActiveRecord works underneath, but you could probably just override the PostgreSQL connection adapter to add this exception so it doesn't have to work in every other type of database. Then you can submit a patch.

Last edited by ryanb (2006-08-04 13:33:24)

Railscasts - Free Ruby on Rails Screencasts

Re: Dealing with unary relations in Rails...

Good point, I'll see what i can do.

My RoR journey  -- thoughts on learning RoR and lessons learned in applying TDD and agile practices.