Rails - Dec 2005 - count sql failing with unknown table....

Hey Rails People,

I''m doing my first Rails app and I''m having a newbie problem
with the
count method....

Mysql::Error: #42S02Unknown table ''jobs_sectors'' in where
clause: SELECT
COUNT(*) FROM jobs WHERE (jobs_sectors.sector_id = 1 )

Extracted source (around line #4):

1: <div class="sectors">
2: 	<ul>
3: 		<% for sector in @sectors -%>
4: 		<li><%= sector.name %> ( <%= sector.jobs.count %>
)</li>
5: 		<% end -%>
6: 	</ul>
7: </div>

My test application has jobs with multiple sectors, see below for DDL. 
The query generated by sector.jobs.count seems a bit funny.  I''m no 
MySQL expert but should there be more references to the join between 
jobs and jobs_sectors?

Thanks for you time,
Tim

DDL as promised.....

CREATE TABLE `jobs_sectors` (
  `job_id` mediumint(9) NOT NULL default ''0'',
  `sector_id` mediumint(9) NOT NULL default ''0''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `jobs` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(254) NOT NULL default '''',
  `description` text NOT NULL,
  `start_sallary` float NOT NULL default ''0'',
  `end_sallery` float NOT NULL default ''0'',
  `created_at` datetime NOT NULL default ''0000-00-00
00:00:00'',
  `valid_on` datetime default NULL,
  `invalid_on` datetime default NULL,
  `location_id` mediumint(9) NOT NULL default ''0'',
  `user_id` mediumint(9) NOT NULL default ''0'',
  `type_id` mediumint(9) NOT NULL default ''0'',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `sectors` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '''',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


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

How is your model defined? Maybe there is something there...

2005/12/12, Tim Squires
<rails-YVboj2/28W59pMjJd8zWoA@public.gmane.org>:
>
> Hey Rails People,
>
> I''m doing my first Rails app and I''m having a newbie
problem with the
> count method....
>
> Mysql::Error: #42S02Unknown table ''jobs_sectors'' in where
clause: SELECT
> COUNT(*) FROM jobs WHERE (jobs_sectors.sector_id = 1 )
>
> Extracted source (around line #4):
>
> 1: <div class="sectors">
> 2:      <ul>
> 3:              <% for sector in @sectors -%>
> 4:              <li><%= sector.name %> ( <%=
sector.jobs.count %> )</li>
> 5:              <% end -%>
> 6:      </ul>
> 7: </div>
>
> My test application has jobs with multiple sectors, see below for DDL.
> The query generated by sector.jobs.count seems a bit funny.  I''m
no
> MySQL expert but should there be more references to the join between
> jobs and jobs_sectors?
>
> Thanks for you time,
> Tim
>
> DDL as promised.....
>
> CREATE TABLE `jobs_sectors` (
>   `job_id` mediumint(9) NOT NULL default ''0'',
>   `sector_id` mediumint(9) NOT NULL default ''0''
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> CREATE TABLE `jobs` (
>   `id` int(10) unsigned NOT NULL auto_increment,
>   `title` varchar(254) NOT NULL default '''',
>   `description` text NOT NULL,
>   `start_sallary` float NOT NULL default ''0'',
>   `end_sallery` float NOT NULL default ''0'',
>   `created_at` datetime NOT NULL default ''0000-00-00
00:00:00'',
>   `valid_on` datetime default NULL,
>   `invalid_on` datetime default NULL,
>   `location_id` mediumint(9) NOT NULL default ''0'',
>   `user_id` mediumint(9) NOT NULL default ''0'',
>   `type_id` mediumint(9) NOT NULL default ''0'',
>   PRIMARY KEY  (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
> CREATE TABLE `sectors` (
>   `id` int(10) unsigned NOT NULL auto_increment,
>   `name` varchar(50) NOT NULL default '''',
>   PRIMARY KEY  (`id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>


--
=====================Eduardo Leao Garcia
edulg72-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
=====================

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Garcia?= wrote:
> How is your model defined? Maybe there is something there...
Here''s the models..

class Job < ActiveRecord::Base
        has_and_belongs_to_many :sectors
        belongs_to :area
        belongs_to :user
        belongs_to :type
end

class Sector < ActiveRecord::Base
  has_and_belongs_to_many :jobs
end


not much from the default.

My versions are
# ruby -v
ruby 1.8.4 (2005-10-29) [i486-linux]

# rails -v
Rails 0.14.4

# gem -v
0.8.11
# mysql --version
mysql  Ver 14.7 Distrib 4.1.15, for pc-linux-gnu (i386) using readline 
4.3


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