Related model and FK error

DaxCax · May 2012

Hello,

I'm getting an error when I try to relate two models (tour_events_model and courses_model). This is the error:

A Database Error Occurred

Error Number: 1054

Unknown column 'tm_Course.tour_event_id' in 'where clause'

SELECT tm_Course.*, courseName, city, TourEvent_id, eventName, Division_id, divisionName, RoundType_id, roundType, tm_Course.published FROM (tm_Course) LEFT JOIN tm_TourEvent ON tm_Course.TourEvent_id = tm_TourEvent.id LEFT JOIN tm_Division ON tm_Course.Division_id = tm_Division.id LEFT JOIN tm_RoundType ON tm_Course.RoundType_id = tm_RoundType.id WHERE `tm_Course`.`tour_event_id` = '9'

Filename: core/MY_Model.php

Line Number: 261

Problem is that there is no table.column like tm_Course.tour_event_id, instead I have tm_Course.TourEvent_id. Where do I change this, if I not want (other applications uses this db) to change column names in the database?

This is the code that triggers it:


        // ******************* NEW RELATED CATEGORY FIELD BEGIN *******************
        $related = array('tour_events' => 'courses_model');
        // ******************* NEW RELATED CATEGORY FIELD END ******************* 

        $fields = parent::form_fields($values, $related);

Thanks for any input!

Lance · May 2012

Do you have anything set for $foreign_keys on your model? Try setting your models $key_field to the correct key.

DaxCax · May 2012

This is set in the courses_model:


    public $foreign_keys = array(
        'TourEvent_id' => 'tour_events_model',
        'Division_id' => 'divisions_model',
        'RoundType_id' => 'round_types_model'
    );

DaxCax · May 2012

If it helps, this the mentioned tables:



CREATE TABLE IF NOT EXISTS `tm_TourEvent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `eventName` varchar(45) NOT NULL,
  `startDate` date NOT NULL,
  `endDate` date NOT NULL,
  `Country_id` int(11) NOT NULL,
  `published` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  KEY `fk_TourEvent_Country1` (`Country_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;


CREATE TABLE IF NOT EXISTS `tm_Course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseName` varchar(45) NOT NULL,
  `city` varchar(45) DEFAULT NULL,
  `TourEvent_id` int(11) NOT NULL,
  `Division_id` int(11) NOT NULL,
  `RoundType_id` int(11) NOT NULL,
  `published` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  KEY `fk_Course_Division` (`Division_id`),
  KEY `fk_Course_TourEvent` (`TourEvent_id`),
  KEY `fk_Course_RoundType1` (`RoundType_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=54 ;

Thanks again!

Lance · May 2012

Hmm that actually looks 'hard' coded in the MY_model::form_fields() around line 1744


$field_values = (!empty($values['id'])) ? array_keys($CI->$lookup_model->find_all_array_assoc($CI->$related_model->short_name(TRUE, TRUE).'_id', array($this->short_name(TRUE, TRUE).'_id' => $values[$key_field]))) : array();

It's appending _id to the class name. Even if you set the class name specifically that first TRUE in short_name() will lowercase it.. You maybe be able to overrwist the short_name method in your model, could potentially cause some problems though.

I've not actually used $related before, do you have to? You could just not send the related array to the parent form_fields and just make them 'manually' in your own models form_fields().

DaxCax · May 2012

Well maybe not, but trying to get a grip on FuelCMSs builtin tools. It should work, and it's practial to use those standard tools.

DaxCax · June 2012

Thank you for the earlier answers and patiance.

Regarding the above problem. Is it poosible to have a courses->list_items() listed under the tour_events->form_fields()? And is it possible using simple modules?

How and where would I call that function?

As the db is designed, the tm_TourEvent table doesn't refer to any of courses table, but in the tm_Course table always has reference to tm_TourEvent.id. This is ok by db-design and therefore I wouldn't need an extra cross reference "courses_to_events" table.

So question is if I in this case can show all the course belonging to one event?

DaxCax · June 2012

I found that there are similar needs in another topic:

http://www.getfuelcms.com/forums/discussion/709/extend-admin-module-edit-page

I tried to convert Lance example to my needs, but didn't got to work just yet. I'll post my quiestions in there.

Lance · June 2012

That example is quite involved...

What's your end goal for the display of all the courses? If you just want them in a select list or even a combo list that should be pretty straight forward. The blog (posts) module does this with categories and authors.

If you want to display the courses as a table or some other non standard way that will be more like the example you found where you create a custom field and iterate over the data to build a table.

The list_items method on models really only give you an opportunity to alter the query (extra joins for eg) or reformat the field data (turning a db date into readable format for eg).

DaxCax · June 2012

Yes, I want them to show up in a table below the "TourEvents" form. If it would be possible just like they are listed in a simple module. An "Add" button on top and "Edit" buttons on each course row would be necessary.
When I click "Save" in the "TourEvents" form, no data about course need to get saved, so the courses table are mostly informative in that sense.

I trying to use the example from the other thread, but got stuck as I was using simple modules and couldn't get it right calling the models. Now I'm trying to convert the simple modules and models to an advanced module.

Howdy, Stranger!

Categories

Related model and FK error

Comments