HABTM count table

Rails
1

Hello,

I have a question about HABTM and counting records.

I have these models

class Sort < ActiveRecord::Base
has_and_belongs_to_many :reports
end

class Report < ActiveRecord::Base
has_and_belongs_to_many :region
has_and_belongs_to_many :subjects
has_and_belongs_to_many :sorts
end

And i would like to get a count like
@sort.reports.count

The problem is get this query:
SELECT COUNT(*) FROM reports WHERE (reports_sorts.sort_id = 6003 )

He uses the wrong table “reports” instead of the join table
“reports_sorts”. But it is in the where clause.

Thanks in advance,

Martijn

2

The problem is get this query:
SELECT COUNT(*) FROM reports WHERE (reports_sorts.sort_id = 6003 )

SELECT COUNT(*) FROM reports WHERE (reports.report_id =
reports_sorts.report_id AND reports_sorts.sort_id = 6003)

3

Anthony G. schreef:

I think your example you mean

SELECT COUNT(*)
FROM reports
LEFT JOIN reports_sorts ON reports.id = reports_sorts.report_id
WHERE (reports_sorts.sort_id = 6003)

But the problem is, the activerecord generated query is wrong. The table
reports should be reports_sorts. But why is it wrong?

4

Martijn van Leeuwen wrote:

Anthony G. schreef:

I think your example you mean

SELECT COUNT(*)
FROM reports
LEFT JOIN reports_sorts ON reports.id = reports_sorts.report_id
WHERE (reports_sorts.sort_id = 6003)

No, left join does just that - joins the contents of second table onto
the ends of the results of the first by an unifying join table, you
wouldn’t need that for COUNT.

_tony

5

Anthony G. schreef:

FROM reports

But how can i get the count of sort_id from table reports, it should be
reports_sorts. And of my knowing activerecord normally should do that
automatically.

@sort = Sort.find(id)
@sort.reports.count

But where is the magic?

6

class Sort < ActiveRecord::Base
has_and_belongs_to_many :reports
end

class Report < ActiveRecord::Base
has_and_belongs_to_many :region
has_and_belongs_to_many :subjects
has_and_belongs_to_many :sorts
end

Which version of Rails are you using ?
Theres a known bug in v1.0 regrading models with more that one
has_and_belongs_to_many relationships.
That could be the cause.

_Tony

7

Martijn van Leeuwen wrote:

Anthony G. schreef:

Working with 1.0 yes, this would be a nice starting point. The problem
isn’t that though.

Have you patched to fix the bug ?

If not reduce your class Report

class Report < ActiveRecord::Base
has_and_belongs_to_many :sorts
end

and start from there.

Information on the count method is here:
http://www.railsmanual.org/class/ActiveRecord::Base#meth_count

it seems to indicate you can use a joins parameter

_tony

8

Anthony G. schreef:

Working with 1.0 yes, this would be a nice starting point. The problem
isn’t that though, and a workaround will be easy. But i want my code as
clear as possible. Thanks for the help.

9

Information on the count method is here:
http://www.railsmanual.org/class/ActiveRecord::Base#meth_count

it seems to indicate you can use a joins parameter

_tony

The join feature worked. The count should work without it, but i can
live with this.

@sort.reports.count(nil,“LEFT JOIN reports_sorts ON
reports_sorts.report_id = reports.id”)