Forum: Ruby on Rails count sql failing with unknown 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.
Tim S. (Guest)
on 2005-12-12 15:36
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;
=?ISO-8859-1?Q?Eduardo_Le=E3o_Garcia?= (Guest)
on 2005-12-12 16:08
(Received via mailing list)
How is your model defined? Maybe there is something there...

2005/12/12, Tim S. <removed_email_address@domain.invalid>:
>
> MySQL expert but should there be more references to the join between
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>   `location_id` mediumint(9) NOT NULL default '0',
>
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails
>



--
======================
Eduardo Leao Garcia
removed_email_address@domain.invalid
======================
Tim S. (Guest)
on 2005-12-12 16:16
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
This topic is locked and can not be replied to.