Datamapper query error. Can't understand what I am doing wrong

[eluser]prestondocks[/eluser]
Hi All,

I am getting the following error when running a query using datamapper

A Database Error Occurred
Error Number: 1054

Unknown column 'category_model_categorys_posts._id' in 'where clause'

SELECT `posts`.* FROM (`posts`) LEFT OUTER JOIN `categorys_posts` category_model_categorys_posts ON `posts`.`id` = `category_model_categorys_posts`.`_id` WHERE `category_model_categorys_posts`.`_id` IS NULL

I have a post_model and a category_model

Here is my category_model

Code:

class Category_model extends DataMapper
{
    var $table = "categorys";
    var $has_many = array('post');
    var $validation = array(
                        'category'=>array(
                            'label'=>'Category',
                            'rules'=>array('required','min_legnth'=>3,'max_legnth'=>50),
                        ),
                        'active'=>array(
                            'label'=>'Active',
                            'rules'=>array('required','valid_match'=>array(1,0)),
                        ),
    );

and here is my post_model and the function that is generating the error

Code:

class Post_model extends DataMapper
{

    var $table = "posts";
    var $has_one = array('user','category');
    var $has_many = array('comment');
    var $validation = array(
                        'title'=>array(
                            'label'=>'Title',
                            'rules'=>array('required','max_legnth'=>120,'min_legnth'=>5),
                        ),
                        'body'=>array(
                            'label'=>'Post Body',
                            'rules'=>array('required','min_legnth'=>5),
                        ),
                        'status'=>array(
                            'label'=>'Post Status',
                            'rules'=>array('required','valid_match'=>array(1,2,3)),
                        ),
    );

function get_by_category($category)
    {
        $c = new Category_model();
        $c->get_where('category',$category);

        $p = new Post_model();
        $p->where_related($c);
        $p->get();
        return $p;
    }

My database has a posts table, categorys table and categorys_posts table.

I am using the tip version of datamapper

Any help much appreciated.

Thanks
Simon

[eluser]prestondocks[/eluser]
I am starting to think that maybe, Datamapper is not reading the $table property in each of my model classes. Does anyone have any thoughts on this idea?

[eluser]techgnome[/eluser]
What does the table structure for categorys_posts look like?

-tg

[eluser]prestondocks[/eluser]
Good morning Techgnome

Table structure for categorys_posts is as follows

id (int 10)
category_id (int 10)
post_id (int 10)

I was wondering if my field names should reflect the singular of the table names or my model class names

Thanks
Simon

[eluser]WanWizard[/eluser]
First of all, you're violating the rule that models should have the singular name of the table, and the other way around.

The fact that it tries to join on the column '_id' means that, most likely because of the previous statement, it can't find the foreign key in the relationship table.
Foreign keys (and almost everything else) are named in relation to the model, so in this case the FK should be category_model_id.