Let's say I want to report on Posts, but only where the User who submitted the post has country == "united states".
Wondering if reportable method could accept a :joins param so I can do something like the following:
class Post < ActiveRecord::Base
belongs_to :user
reportable :usa_posts, :joins => :user, :conditions => ["users.country LIKE 'united states'"]
EDIT:
It appears the report methods generated by reportable can be chained, e.g.:
Post.joins(:user).where("users.country LIKE 'united states'").weekly_users_report(:limit => 7)
It appears that this generates the correct SQL. The only problem is that reportable doesn't qualify the date fields with table names, so I get the error "PGError: ERROR: column reference "created_at" is ambiguous, COUNT("posts"."id") AS count_id, date_trunc('day', created_at...".
If reportable generated date_trunc('day', "posts"."created_at") instead of date_trunc('day', created_at) then I believe the query would work properly. The only problem would be the caching code -- I assume it would not be able to tell that conditions were added via chaining, and would improperly cache the same result no matter what country I specified in my where clause. A temporary workaround could be to use the :live_data option to disable cache when chaining.
EDIT (AGAIN):
Possible to hack around the ambiguous column name error and cache issues with something like the following:
Post.joins("JOIN (SELECT id,country FROM users) AS users ON users.id = posts.user_id").
weekly_users_report(:conditions => ["country LIKE 'united states'"], :limit => 7)
This eliminates the users.created_at column that caused the error and puts the conditions where reportable can see them resulting in properly cached results.
(Every time I look at the code I find another workaround or issue, so forgive my many updates!)
Let's say I want to report on Posts, but only where the User who submitted the post has country == "united states".
Wondering if reportable method could accept a :joins param so I can do something like the following:
EDIT:
It appears the report methods generated by reportable can be chained, e.g.:
It appears that this generates the correct SQL. The only problem is that reportable doesn't qualify the date fields with table names, so I get the error "PGError: ERROR: column reference "created_at" is ambiguous, COUNT("posts"."id") AS count_id, date_trunc('day', created_at...".
If reportable generated date_trunc('day', "posts"."created_at") instead of date_trunc('day', created_at) then I believe the query would work properly. The only problem would be the caching code -- I assume it would not be able to tell that conditions were added via chaining, and would improperly cache the same result no matter what country I specified in my where clause. A temporary workaround could be to use the :live_data option to disable cache when chaining.
EDIT (AGAIN):
Possible to hack around the ambiguous column name error and cache issues with something like the following:
This eliminates the users.created_at column that caused the error and puts the conditions where reportable can see them resulting in properly cached results.
(Every time I look at the code I find another workaround or issue, so forgive my many updates!)