# HABTM count table

**URL:** <https://www.ruby-forum.com/t/habtm-count-table/74664>\
**Category:** Rails\
**Created:** [February 28, 2006, 12:49pm UTC](https://www.ruby-forum.com/t/habtm-count-table/74664 "2006-02-28T12:49:20Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Martijn\_van\_Leeuwen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/b5a626/32.png) [@Martijn\_van\_Leeuwen](https://www.ruby-forum.com/u/Martijn_van_Leeuwen)\
**Post date:** [February 28, 2006, 12:49pm UTC](https://www.ruby-forum.com/t/habtm-count-table/74664/1 "2006-02-28T12:49:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Martijn\_van\_Leeuwen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/b5a626/32.png) [@Martijn\_van\_Leeuwen](https://www.ruby-forum.com/u/Martijn_van_Leeuwen)\
**Post date:** [February 28, 2006, 12:58pm UTC](https://www.ruby-forum.com/t/habtm-count-table/74664/2 "2006-02-28T12:58:31Z")

</div>

> 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)

---

<div class="post-metadata">

**Author:** ![Martijn\_van\_Leeuwen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/b5a626/32.png) [@Martijn\_van\_Leeuwen](https://www.ruby-forum.com/u/Martijn_van_Leeuwen)\
**Post date:** [February 28, 2006, 1:41pm UTC](https://www.ruby-forum.com/t/habtm-count-table/74664/3 "2006-02-28T13:41:10Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Martijn\_van\_Leeuwen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/b5a626/32.png) [@Martijn\_van\_Leeuwen](https://www.ruby-forum.com/u/Martijn_van_Leeuwen)\
**Post date:** [February 28, 2006, 1:54pm UTC](https://www.ruby-forum.com/t/habtm-count-table/74664/4 "2006-02-28T13:54:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Martijn\_van\_Leeuwen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/b5a626/32.png) [@Martijn\_van\_Leeuwen](https://www.ruby-forum.com/u/Martijn_van_Leeuwen)\
**Post date:** [February 28, 2006, 1:59pm UTC](https://www.ruby-forum.com/t/habtm-count-table/74664/5 "2006-02-28T13:59:27Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Martijn\_van\_Leeuwen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/b5a626/32.png) [@Martijn\_van\_Leeuwen](https://www.ruby-forum.com/u/Martijn_van_Leeuwen)\
**Post date:** [February 28, 2006, 2:02pm UTC](https://www.ruby-forum.com/t/habtm-count-table/74664/6 "2006-02-28T14:02:27Z")

</div>

> 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

---

<div class="post-metadata">

**Author:** ![Martijn\_van\_Leeuwen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/b5a626/32.png) [@Martijn\_van\_Leeuwen](https://www.ruby-forum.com/u/Martijn_van_Leeuwen)\
**Post date:** [February 28, 2006, 2:39pm UTC](https://www.ruby-forum.com/t/habtm-count-table/74664/7 "2006-02-28T14:39:55Z")

</div>

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](http://www.railsmanual.org/class/ActiveRecord::Base#meth_count)

it seems to indicate you can use a joins parameter

\_tony

---

<div class="post-metadata">

**Author:** ![Martijn\_van\_Leeuwen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/b5a626/32.png) [@Martijn\_van\_Leeuwen](https://www.ruby-forum.com/u/Martijn_van_Leeuwen)\
**Post date:** [February 28, 2006, 2:14pm UTC](https://www.ruby-forum.com/t/habtm-count-table/74664/8 "2006-02-28T14:14:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Martijn\_van\_Leeuwen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/b5a626/32.png) [@Martijn\_van\_Leeuwen](https://www.ruby-forum.com/u/Martijn_van_Leeuwen)\
**Post date:** [February 28, 2006, 3:03pm UTC](https://www.ruby-forum.com/t/habtm-count-table/74664/9 "2006-02-28T15:03:49Z")

</div>

> Information on the count method is here:  
> [http://www.railsmanual.org/class/ActiveRecord::Base#meth\_count](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”)