Rails: Okay, that's just stupid

[elfs]

I have long asserted that if you can't handle SQL or Javascript, you shouldn't be programming rails. And I don't mean a little bit; you ought to be able to eat, sleep, and breathe Javascript, HTML, CSS, SQL, and Ruby before you begin to sell yourself as a Rails programmer.

I am not a Rails programmer. Even so, I ought to be able to do simple things. Yet today I was stymied and went for my sledgehammer, the SQL statement.

Here's the layout: Stories exist. Stories belong to serials. Stories may optionally belong to arcs. Arcs theoretically belong to serials, but there is the "no arc" setting which, unfortunately, is global across the Journal Entries and my Other Stories archive, since there are plenty of standalone Journal Entries and most of the Other Stories stories are also standalone rather than complex. (That's a joke. Zoner will get it.)

So I wanted to be able to find all arcs that belong to a series. That's a simple statement in SQL: select distinct arcs.id, arcs.title, arcs.blurb from arcs, stories where stories.serial_id = 1 and arcs.id = stories.arc_id order by stories.pubdate. You see, arcs and serials only have a relationship through stories, not with each other.

There seems to be no clear way to do this in rails. It should be something like Arcs.find(...), but that didn't work at all.

This is on top of my discovering that yes, you can define your own pluralization rules, but you cannot override the ones inside Rails. "Series" will always be unpluralizable. If you try that, it works great until you trip over a call that dives beneath your application to the appserver layer, at which point Rails's internal pluralization rules take over and you're screwed.

Comments

[en-ki.livejournal.com]

You might want to be doing a join & group rather than a Cartesian product & distinct here:

select arcs.id, arcs.title, arcs.blurb, max(stories.pubdate) as pubdate from arcs join stories on arcs.id = stories.arc_id group by arcs.id order by pubdate;

(modulo syntax quirks and errors on my part) gets you the id, title, and blurb of the arc, ordered by the latest story in each arc.

In Rails, this is something like

Arcs.find(:all, :include => :stories)

(There are additional arguments to do the group-by and order-by which practically amount to custom SQL.)

[elfs.livejournal.com]

Thank you! That does seem to be working. I'm not sure about the stories.pubdate (it would be a min() operator, though), but it's doing what I expect it to do.

[en-ki.livejournal.com]

Glad to hear it. BTW, the rule of thumb I've heard is that using "distinct" means either your tables need normalization or your query needs "group by".

[cadetstar.livejournal.com]

I'd have to play with the language, but something like this *should* work:

Arc.find(:all, :include => :stories, :conditions => {story.series => @series}, :distinct => true)

Also, Rails is picky that models be defined as singular...it goes all weird if it thinks the model is plural and you have to override a lot of the built in methods. I usually get by this by adding another word to the end (so I'd scaffold series as seriesinst, "series instance", instead).

I'm going backwards from how you suggest and learning Rails, CSS and Java through Rails rather than the other way around, though I'm doing it with extremely simple programs at this point.

-Michael

[zonereyrie.livejournal.com]

Motoko Kusanagi says hi.