[RoR] Moving Past Active Record

[JNKlein]

I'm using RoR for the frontend of a db that stores info about a game so as to make league statistics and the like available. This is being built to replace a pre-existing PHP frontend, although the database is also being redesigned to ease the transition.

Lets make a few simplified tables that are indicative of my question:

create table players (
	id	int		not null auto_increment,
	name	varchar(100)	not null,
	primary key (id)
);

create table events (
	id		int		not null auto_increment,
	happened_on	datetime	not null,
	primary key (id)
);

create table event_players (
	id		int	not null auto_increment,
	event_id	int	not null,
	player_id	int	not null,
	foreign key (event_id) references events(id),
	foreign key (player_id) references players(id),
	primary key (id)
);

create table points (
	id		int	not null auto_increment,
	event_id	int	not null,
	foreign key (event_id) references events(id),
	primary key (id)
);

So to quickly summarize what these tables are: players and events are self explanatory. The event_players table lets us know what players were in the game when an event happened. The table points is one of several sorts of events.

Now lets say I wanted to create a controller that output a list of all the players with the points they were in the game for. (Lets also assume that there are many other events in the event_players table, so you can't just count their appearances there).

My question regards the Models and Controllers needed for this system, and moving past Active Record and the scaffold...

Example Output:

Player 1	15
Player 2	12
Player 3	4
Player 4	1
Player 5	0

What are the issues at hand, here? How does one cope with that slightly more complex table relationship? I own the Agile Webdev with Rails book, but have not yet come across the info I'm looking for in regards to this. Can anyone direct me to a specific chapter?

[Luke Redpath]

Now lets say I wanted to create a controller that output a list of all the players with the points they were in the game for.

Sorry I don't quite understand this sentence. Could you possibly rephrase it?

That aside I'm not quite sure what is so complex about your relationships...there certainly doesn't seem to be anything that AR cannot handle. It seems to be...

class Player < ActiveRecord::Base
  has_and_belongs_to_many :events
end

class Event < ActiveRecord::Base
  has_and_belongs_to_many :players
  has_many :points
end

class Point
  belongs_to :event
end

[MiiJaySung]

Certainly, there are limitation to ActiveRecord's ORM system like all ORM tools. However generally with Rails you rarely seem to hit them because rails is designed round the 80/20 rule that most web applications are CRUD orientated by design.

One thing I found ActiveRecord failed on is when I needed to write a complex search system for a lettings site. In this case the search system was defined by being able to search on any number of attributes that properties had. What made this a PITA was the fact that this didn't fall in place with the way finder's worked in Rails because there was a arbitary number of attributes that would be used as criteria. What complicated this and made it hard to use finders was the fact that the agency dealt with other types of properties (sales is another example) which had very different attributes from lettings. The STI compuled with the need for paging and need to make joins to a postcode / place database made it impossible to use the ORM tools Rails has.

In these situations I normally write my own finder methods or modules/classes. Ideally when doing this, the best way forward is to basically make these finders to do direct SQL quieries. In this case the search was complex soi sorta needed to make a hash that worked like a Query Object before serializing to SQL and then building the resultset into a collection of objects. From here because you've made your result set into objects you should find you can continue to do things the rails way.