acts_as_reportable: duplicate records returned when two models used with :include

[gluis]

hi all,

i'm getting some strangeness w/ acts_as_reportable. short question: is
there a way to ensure a query is only run once? see below for why i
ask: (btw, although this is a longish question, i only put in relevant
info)

assume 3 models
Event, has_many :members, :through => :registrations AND
has_many :registrations
Registration, belongs_to :event AND belongs_to :member
Member, has_many :events, :through => :registrations AND
has_many :registrations

STRANGENESS 1:

running this query:
Member.report_table(:all, :only => [:first], :include => {:events =>
{:only => [:id]},:registrations => {:only => [:event_id, :id]}})

returns duplicate records for any member registered for more than one
event. *and* it returns mixed up id columns for events on the
duplicates (i.e. the first two rows have the correct data):

sample output of this query (pls excuse the formatting):
+--------------------------------------------------------------------------------
+
| first | registrations.id | registrations.event_id | events.id |
+--------------------------------------------------------------------------------
+
| Jo | 11 | 1
| 1 |
| Jo | 13 | 2
| 1 |
| Jo | 11 | 1
| 2 |
| Jo | 13 | 2
| 2 |
| Juan | |
| |
+--------------------------------------------------------------------------------
+

STRANGENESS 2:

if you run the same query on a different model, the correct results
are returned!:

Event.report_table(:all, :only => [:id], :include => {:members =>
{:only => [:first]},:registrations => {:only => [:event_id, :id]}})

+------------------------------------------------------------------------------------
+
| id | members.first | registrations.event_id | registrations.id |
+------------------------------------------------------------------------------------
+
| 1 | Jo | 1
| 11 |
| 2 | Jo | 2
| 13 |
+-----------------------------------------------------------------------------------
+

all member names are unique, and members cannot register > once for
the same event.

is this a known issue w/ acts_as_reportable? or am i composing the
query improperly? why the different results for two models who have
similar characteristics? (i.e. both Member and Event have has_many
<each other> :through => :registrations; both have the same
relationship type to Registration, i.e. Registration
belongs_to :member, :event)

STRANGENESS 3:

if i run the query with only one model included (i.e. :include =>
{:events} OR {:registrations}), i get the proper results.
also, running the query w/ :conditions, e.g. a specific name, returns
the same duplicate record output, except only that name appears (as it
should).

is there a work around? is it acts_as_reportable, or ruport? i haven't
seen mention of this anywhere. is it has_many through? is habtm better
for ruport? is there something in my Member model? it's a pretty
vanilla model, w/ two mixins besides acts_as_reportable: Authlogic and
a custom module to facilitate custom searches in SQL. As the Event
model has the same custom module, i removed Authlogic integration for
yucks, but the same strangeness occurs without it.

any help, hints, URLs, etc. are much appreciated,

louis

[Andrew France]

gluis wrote:
> assume 3 models
> Event, has_many :members, :through => :registrations AND
> has_many :registrations
> Registration, belongs_to :event AND belongs_to :member
> Member, has_many :events, :through => :registrations AND
> has_many :registrations
>
> STRANGENESS 1:
>
> running this query:
> Member.report_table(:all, :only => [:first], :include => {:events =>
> {:only => [:id]},:registrations => {:only => [:event_id, :id]}})
>
> returns duplicate records for any member registered for more than one
> event. *and* it returns mixed up id columns for events on the
> duplicates (i.e. the first two rows have the correct data):

I think the behaviour is correct because you are including 'events'
twice via the HABTM :through. You would probably want a nested include:
:include => {:registrations => {:only => [:event_id, :id], :include =>

{:events => {:only => :id}}}}

Acts As Reportable has no SQL knowledge it just walks the ActiveRecord
object graph so it doesn't know that the two separate associations go to
the same table. If you look at the generated SQL it probably has two
joins to the same table.

I don't know why the behaviour would be different in your second
example, but it is data-dependent.

> STRANGENESS 3:
>
> if i run the query with only one model included (i.e. :include =>
> {:events} OR {:registrations}), i get the proper results.
> also, running the query w/ :conditions, e.g. a specific name, returns
> the same duplicate record output, except only that name appears (as it
> should).

Not even sure what the question is with this one :). I don't know what
version of AAR you're using, I'm guessing the gem from RubyForge. You
might find it useful to install the latest from GitHub either as
vendor'd source or using the GitHub gems service. If you do, please let
me know if it solves any of your problems or if it introduces any new ones!

http://github.com/ruport/acts_as_reportable/tree/master

Hope that helps,
Andrew

[gluis]

thanks, andrew. i would have replied sooner, but i was offline all
weekend. i'll test this today.

ftr, i'm using ar 2.3.2

the third strangeness was just pointing out that if i only include one
model, it works as expected.but if i do the equivalent of a 'where'
clause by using :conditions, that the same duplication of records
happens. e.g. :include => :events...., :conditions => [':event_title
= ?', 'something']


l

[gluis]

well, this works (nesting). thanks! one note, i get some warnings
before the results, which might be of interest to you as the
maintainer:

/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/
associations/association_proxy.rb:221: warning: default `to_a' will be
obsolete
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/
associations/association_proxy.rb:221: warning: default `to_a' will be
obsolete
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/
associations/association_proxy.rb:221: warning: default `to_a' will be
obsolete

(i'm using 1.8.7)

i'm using the aar version you recommended in another post (which fixed
another issue i had) as a plugin. it says 1.1.1 in the source, but the
gem from rubyforge also says 1.1.1. it's the one from github.

thanks for the pointer/help!

louis

On May 23, 4:35 am, Andrew France <andrew+li...@avito.co.uk> wrote: