Forum: Ruby on Rails HABTM count table

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
234baba7870c09d73a2805438a75d36d?d=identicon&s=25 Martijn van Leeuwen (Guest)
on 2006-02-28 12:49
(Received via mailing list)
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
F59329dc91cba06600ff65c85fd3e93c?d=identicon&s=25 Anthony Green (acgreen)
on 2006-02-28 12:58
> 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)
234baba7870c09d73a2805438a75d36d?d=identicon&s=25 Martijn van Leeuwen (Guest)
on 2006-02-28 13:41
(Received via mailing list)
Anthony Green 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?
F59329dc91cba06600ff65c85fd3e93c?d=identicon&s=25 Anthony Green (acgreen)
on 2006-02-28 13:54
Martijn van Leeuwen wrote:
> Anthony Green 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
234baba7870c09d73a2805438a75d36d?d=identicon&s=25 Martijn van Leeuwen (Guest)
on 2006-02-28 13:59
(Received via mailing list)
Anthony Green 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?
F59329dc91cba06600ff65c85fd3e93c?d=identicon&s=25 Anthony Green (acgreen)
on 2006-02-28 14:02
> 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
234baba7870c09d73a2805438a75d36d?d=identicon&s=25 Martijn van Leeuwen (Guest)
on 2006-02-28 14:14
(Received via mailing list)
Anthony Green 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.
F59329dc91cba06600ff65c85fd3e93c?d=identicon&s=25 Anthony Green (acgreen)
on 2006-02-28 14:39
Martijn van Leeuwen wrote:
> Anthony Green 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::Bas...

it seems to indicate you can use a joins parameter

_tony
234baba7870c09d73a2805438a75d36d?d=identicon&s=25 Martijn van Leeuwen (Guest)
on 2006-02-28 15:03
(Received via mailing list)
>Information on the count method is here:
>http://www.railsmanual.org/class/ActiveRecord::Bas...
>
>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")
This topic is locked and can not be replied to.