Rails - Feb 2006 - HABTM count table

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

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

-- 
Posted via http://www.ruby-forum.com/.

Anthony Green schreef:
>>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)
>
>  
>
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?

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

-- 
Posted via http://www.ruby-forum.com/.

Anthony Green schreef:
>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
>
>  
>
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?

> 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

-- 
Posted via http://www.ruby-forum.com/.

Anthony Green schreef:
>>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
>
>  
>
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.

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::Base#meth_count

it seems to indicate you can use a joins parameter

_tony

-- 
Posted via http://www.ruby-forum.com/.

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