As always, you guys have given me a lot to think about. For those of
you who've been asking here is an example of the kind of report I'd
want to generate. As some of the real queries would reveal more of my
internal db structure than I'd like to post on a public forum I'm
going to be using fictitious table and column names.
Right now the reporting flow in my application is:
1) User requests output of a report
2) Controller find an instance of the report using AR
3) The controller calls report.execute(parameters) <== NOTE: The
parameters are not the request params, but usually will be a subset of
them
4) The report executes and returns a set of columns and rows
5) The controller transforms the list of columns and rows into a
format that works with Google Visualizations
I'm planning on having different sub-classes of Report (most likely
using single-table inheritance.) Right now I've only got one Report
class type which has an attribute which stores ruby code and that
returns SQL when evaluated. This SQL-generating code is evaluated
using report.instance_eval() then the SQL is executed using
ActiveRecord::Base.connection(). Currently the parameters are
injected into the SQL when the SQL-generating code is evaluated. The
parameters aren't being escaped but will need to be before the system
goes live.
An example of this SQL-generating code might look something like
this. The production query will likely look different, but the output
should be similair:
"SELECT l.name, YEAR(t.created_on) AS trans_year, COUNT(*) FROM
transactions AS t INNER JOIN locations AS l ON t.location_id = l.id
WHERE t.created_on IS NOT NULL #{params.has_key?(:group1) ? sprintf
("AND l.code IN (%s)", params[:group1].values.map{ |value| "\"#{value}
\"" }.join(",")) : ""} #{params.has_key?(:start_date) ? sprintf("AND
t.created_on >= \"%s\"", params[:start_date]) : "" } GROUP BY l.name,
trans_year ORDER BY l.name, trans_year;"
In this circumstance is the Sequel gem the way to go? What sort of
impact will this have on the existing AR classes? The other option to
make Report an abstract subclass of AR::Base sounds possible. This is
new code that is being added to an existing application which already
has about 30 model classes.
Thanks again everyone. :)
On Aug 19, 12:24 pm, Lachie <[email protected]> wrote:
> On Wed, Aug 19, 2009 at 11:54 AM, Nathan de Vries<[email protected]> wrote:
>
> > On 19/08/2009, at 11:39 AM, Gabe Hollombe wrote:
> >> What about ActiveRecord::Base.sanitize_sql_for_conditions ?
>
> > That's a protected class method on ActiveRecord::Base, which I did say
> > he could use but would need to work around (e.g.
> > ActiveRecord::Base.send(:sanitize_sql_for_conditions, [])). Obviously
> > I don't know what style reports he's after, but in my experience with
> > ActiveRecord-based reporting systems isn't too positive. Personally, I
> > prefer to use Sequel in the context of reporting because I find myself
> > fighting the system a lot less.
>
> There *are* a lot of ways
>
> You could also
> include ActiveRecord::ConnectionAdapters::Quoting
> into your class to get some measure of quotingy goodness.
>
> Or make your report class an "abstract" subclass of AR::Base simply to
> get at the handy protecteds.
>
> However I do agree with Nathan that AR isn't a great solution for
> getting at raw SQL stuffs.
>
> However^2, if you're already using AR for models in a rails app and
> want the reports in the same app, I'd stick with it simply for the
> convenience of not having to have two ORMs in the one app.
>
> :lachiehttp://smartbomb.com.auhttp://www.flickr.com/photos/lachie/
>
>
>
>
>
> > Cheers,
>
> > Nathan
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Ruby
or Rails Oceania" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rails-oceania?hl=en
-~----------~----~----~----~------~----~------~--~---
