I have code that has worked for years, then I updated to the latest OctoberCMS and it broke!

Can you see anything wrong with this?

        $modules = Module::orderBy('parent_id', 'ASC')
            ->orderBy('order', 'ASC')
            ->with(array('module_items' => function($query) {
                $query->orderBy('position', 'ASC');
            },'module_items.content'))
            ->where(array(
                'course_id' => $courseId
            ))
            ->get();
        return $modules;

The error is SQLSTATE[42S22]: Column not found: 1054 Unknown column 'delphinium_roots_module_items.module_module_id' in 'where clause' (SQL: select * from delphinium_roots_module_items where delphinium_roots_module_items.module_module_id in (734203, 734204, 734205, 734206, 734207, 734208, 734209, 734210, 734211, 734212, 734213, 734214, 734215, 734216, 734217, 734218, 734219) order by position asc)

module_module_id is not a column and best I can tell we aren't trying to access anything like that. The column is named module_id, I don't know where the extra module_ is coming from.

Here is the model

namespace Delphinium\Roots\Models;

use Model;
/**
 * Description of ModuleItem
 *
 * @author Delphinium
 */
class ModuleItem extends Model {
    use \October\Rain\Database\Traits\Validation;

    public $incrementing = false;
    protected $primaryKey = 'module_item_id';
    protected $fillable = array('*');//as of right now, we will only create Modules with data coming from the API, so we can make all of the attributes fillable

    public $table = 'delphinium_roots_module_items';

    public $belongsTo = [
        'module' => ['Delphinium\Roots\Models\Module', 
        'foreignKey' => 'module_id',
        'delete'=>'true']
    ];

    public $rules = [
        'module_item_id'=>'required',
        'module_id' => 'required'
    ];

    public $hasMany = [
        'content' => ['Delphinium\Roots\Models\Content', 'foreignKey' => 'content_id']
    ];
}

Last updated 7 years ago

Try changing it to this

public $belongsTo = [
    'module' => ['Delphinium\Roots\Models\Module', 
    'key' => 'module_id',
    'delete'=>'true']
];

foreignKey is no longer in use, hasn't been for a while. It's probably because on the Module model you have changed the primaryKey to module_id. The foreign key is generated as relationship_primary_key (module_module_id).

hmmmm....

That did not seem to have any effect, I get the same error.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'delphinium_roots_module_items.module_module_id' in 'where clause' (SQL: select * from `delphinium_roots_module_items` where `delphinium_roots_module_items`.`module_module_id` in (734203, 734204, 734205, 734206, 734207, 734208, 734209, 734210, 734211, 734212, 734213, 734214, 734215, 734216, 734217, 734218, 734219) order by `position` asc)

namespace Delphinium\Roots\Models;

use Model;
/**
 * Description of ModuleItem
 *
 * @author Delphinium
 */
class ModuleItem extends Model {
    use \October\Rain\Database\Traits\Validation;

    public $incrementing = false;
    protected $primaryKey = 'module_item_id';
    protected $fillable = array('*');//as of right now, we will only create Modules with data coming from the API, so we can make all of the attributes fillable

    public $table = 'delphinium_roots_module_items';

    public $belongsTo = [
        'module' => ['Delphinium\Roots\Models\Module', 
        'key' => 'module_id',
        'delete'=>'true']
    ];

    public $rules = [
        'module_item_id'=>'required',
        'module_id' => 'required'
    ];

    public $hasMany = [
        'content' => ['Delphinium\Roots\Models\Content', 'foreignKey' => 'content_id']
    ];
}

Any other suggestions?

Last updated 7 years ago

I commented this line out but nothing changed again

//protected $primaryKey = 'module_item_id';

Same error when I changed the line to

protected $primaryKey = 'id';

Last updated 7 years ago

I figured it out. I had to change foreignKey in the module model as well

Thanks for your help!

Last updated 7 years ago

So, I no longer get the error, but the relationship between module and module items is broken.

Here are the two models

namespace Delphinium\Roots\Models;

use Model;

class Module extends Model
{
    use \October\Rain\Database\Traits\Validation;

    public $table = 'delphinium_roots_modules';

    protected $primaryKey = 'module_id';
    public $incrementing = false;//since we are using our own custom primary key (and it's not auto-incrementing)
    //we need to set this incrementing property to false
    //TODO: decide which items will be "fillable" and which ones will not
    protected $fillable = array('module_id','course_id','name', 'position', 'unlock_at', 'email',
        'require_sequential_progress', 'publish_final_grade', 'prerequisite_module_ids', 'items_count',
        'published','state', 'items', 'module_id');

    //Validation 
    public $rules = [
        'module_id'=>'required'
    ];

    public $hasMany = [
            'module_items' => ['Delphinium\Roots\Models\ModuleItem', 
            'key' => 'module_item_id',
            'delete'=>'true']
    ];
}

namespace Delphinium\Roots\Models;

use Model;
/**
 * Description of ModuleItem
 *
 * @author Delphinium
 */
class ModuleItem extends Model {
    use \October\Rain\Database\Traits\Validation;

    public $incrementing = false;
    protected $primaryKey = 'module_item_id';
    protected $fillable = array('*');//as of right now, we will only create Modules with data coming from the API, so we can make all of the attributes fillable

    public $table = 'delphinium_roots_module_items';

    public $belongsTo = [
        'module' => ['Delphinium\Roots\Models\Module', 
        'key' => 'module_id',
        'delete'=>'true']
    ];

    public $rules = [
        'module_item_id'=>'required',
        'module_id' => 'required'
    ];

    public $hasMany = [
        'content' => ['Delphinium\Roots\Models\Content', 'foreignKey' => 'content_id']
    ];
}

Any idea why they don't connect anymore?

Last updated 7 years ago

This is the code where I request the modules from the database

            $modules = Module::orderBy('parent_id', 'ASC')
                ->orderBy('order', 'ASC')
                ->with(array('module_items' => function($query) {
                    $query->orderBy('position', 'ASC');
                },'module_items.content'))
                ->where(array(
                    'course_id' => $courseId
                ))
                ->get();

                return $modules;

and this is what it returns

Collection {#1107 ▼
  #items: array:17 [▼
    0 => Module {#1060 ▼
      +table: "delphinium_roots_modules"
      #primaryKey: "module_id"
      +incrementing: false
      #fillable: array:14 [▶]
      +rules: array:1 [▶]
      +hasMany: array:1 [▶]
      +implement: null
      +attributes: array:18 [▶]
      #jsonable: []
      #dates: []
      +duplicateCache: true
      #connection: "mysql"
      #keyType: "int"
      #with: []
      #withCount: []
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #original: array:18 [▶]
      #changes: []
      #casts: []
      #dateFormat: null
      #appends: []
      #dispatchesEvents: []
      #observables: []
      #relations: array:1 [▼
        "module_items" => Collection {#1058 ▼
          #items: []
        }
      ]
      #touches: []
      +timestamps: true
      #hidden: []
      #visible: []
      #guarded: array:1 [▶]
      +hasOne: []
      +belongsTo: []
      +belongsToMany: []
      +morphTo: []
      +morphOne: []
      +morphMany: []
      +morphToMany: []
      +morphedByMany: []
      +attachOne: []
      +attachMany: []
      +hasManyThrough: []
      #emitterSingleEventCollection: []
      #emitterEventCollection: array:1 [▶]
      #emitterEventSorted: []
      #extensionData: array:3 [▶]
      +sessionKey: null
      #deferredBindingCache: null
      #validationErrors: null
    }
    1 => Module {#1062 ▶}
    2 => Module {#1065 ▶}
    3 => Module {#1067 ▶}
    4 => Module {#1069 ▶}
    5 => Module {#1071 ▶}
    6 => Module {#1073 ▶}
    7 => Module {#1075 ▶}
    8 => Module {#1077 ▶}
    9 => Module {#1079 ▶}
    10 => Module {#1081 ▶}
    11 => Module {#1083 ▶}
    12 => Module {#1085 ▶}
    13 => Module {#1087 ▶}
    14 => Module {#1089 ▶}
    15 => Module {#1091 ▶}
    16 => Module {#1093 ▶}
  ]
}

Notice that the module items are empty in the relations node. It can't seem to find the module items in the relationship. Any help would be greatly appreciated! Thanks!

Here is the content model

namespace Delphinium\Roots\Models;

use Model;
/**
 * Description of Content
 *
 * @author Delphinium
 */
class Content extends Model
{
    use \October\Rain\Database\Traits\Validation;

    public $table = 'delphinium_roots_content';
    public $incrementing = false;
    protected $primaryKey = 'content_id';
    protected $fillable = array('*');//as of right now, we will only create Modules with data coming from the API, so we can make all of the attributes fillable
    /*
     * Validation
     */

    public $rules = [
        'content_id'=>'required',
        'content_type' => 'required'
    ];

}

Here is some feedback I got from the IRC

as a guideline: Name all your primary keys id. 
then you don't need to define $primaryKey either 
as a guideline, name all your belongTo relationships as relationname_id 
as a field in the table of the model 
so if you have a $belongsTo = ['magic_bike' => ['Some\Magic\Bike']] 
you need to have a field magic_bike_id in your model table that owns that $belongsTo

if it comes from an external api it might be easier to keep their keys, but then you have to account for all the naming defaults october uses. so you need to define primaryKey, and in relations key and otherKey a lot 
so if you have a belongs to, you need to define key and otherKey because your primary keys are different 

StackOverflow: Building a eloquent query belongsTo for octobercms https://stackoverflow.com/questions/45718841/building-a-eloquent-query-belongsto-for-octobercms