Count sql failing with unknown table

Rails
1

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:


2:

    3: <% for sector in @sectors -%>
    4:
  • <%= sector.name %> ( <%= sector.jobs.count %> )

    • 5: <% end -%>
    6:

7:

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;

2

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

2005/12/12, Tim S. [email protected]:

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
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

–

Eduardo Leao Garcia
[email protected]

3

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