I’m aware the trait \October\Rain\Database\Traits\SortableRelation is not available in OctoberCMS v2, so, How can I detect if the user is using v2 or v3 and disable or enable the Sortable Relation function?
If I leave the use \October\Rain\Database\Traits\SortableRelation in the model, the version 2 will trigger and error in the backend
Thanks
Hi! I’m using v 2.2.34
When I add use \October\Rain\Database\Traits\SortableRelation in the model I get this message in the backend when I click on the create button
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pivot_table.sort_order' in 'order clause' (SQL: select * from polillastudio_pluginname_users order by pivot_table.sort_order asc)
pivot_table is the table I’m using to join the users with the roles (as the documentation)
In the Users and the Roles model is declared the ManyToMany relation
Users model
public $belongsToMany = [
'roles' => [
'PolillaStudio\Plugin\Models\Role',
'table' => 'pivot_table',
'pivotSortable' => 'sort_order',
]
];
In the Roles Model
public $belongsToMany = [
'users' => [
'PolillaStudio\Plugin\Models\User',
'table' => 'pivot_table',
'pivotSortable' => 'sort_order',
]
];
Don’t forget to add the sort_order column to the database:
Schema::table('users', function ($table) {
$table->integer('sort_order')->default(0);
});
The sort_order field exists in the table
But I’m thinking something about MySQL, because if I use directly in the MySQL terminal the query displayed in the error message
select * from polillastudio_pluginname_users order by pivot_table.sort_order asc
I get the same error in the database engine: Column not found: 1054 Unknown column...
Trying a different approach I provoque an intentional error, changing the pivotSortable name in the User model, for example to sort_order_x and I got a different error:
Column NOT found: 1054 Unknown column 'pivot_table.sort_order_x' IN 'order clause'
(SQL:SELECT `polillastudio_pluginname_role`.`id`
FROM `polillastudio_pluginname_role`
INNER JOIN `pivot_table`
ON `polillastudio_pluginname_role`.`id` = `pivot_table`.`role_id`
WHERE `pivot_table`.`user_id` is null
ORDER BY `pivot_table`.`sort_order_x` asc)"
So, if I use a “wrong name” the query is builded with an Inner Join (and this query works in the MySQL Engine), but if I use the right name for the sort_field, the query is builded in a simple way but this query do not works in MySQL (or may be not in my MySQL version)
The error suggests this is false: Column not found: 1054 Unknown column 'pivot_table.sort_order'
¡Hello!
I start a fresh Demo Plugin in order to clean all the business logic and keep it as simple as I can for the test
The models of the plugin will be Invoices and Items with a Many to Many relationship using a pivot table. using October V2.2
ITEMS
= Model =
<?php namespace Polilla\Demo\Models;
use Model;
class Item extends Model
{
use \October\Rain\Database\Traits\Validation;
public $table = 'polilla_demo_items';
protected $dates = [
'created_at',
'updated_at'
];
public $belongsToMany = [
'invoices' => [
\Polilla\Demo\Models\Invoice::class,
'table' => 'polilla_demo_invoice_item',
]
];
}
= Table =
class CreateItemsTable extends Migration
{
public function up()
{
Schema::create('polilla_demo_items', function (Blueprint $table) {
$table->increments('id');
$table->text('item');
$table->timestamps();
});
}
public function down()
{
Schema::dropIfExists('polilla_demo_items');
}
}
= Columns YAML =
columns:
id:
label: ID
searchable: true
item:
label: Item
= Fields YAML =
fields:
id:
label: ID
disabled: true
item:
label: Item Name
type: text
Invoice
= Model =
<?php namespace Polilla\Demo\Models;
use Model;
class Invoice extends Model
{
use \October\Rain\Database\Traits\Validation;
use \October\Rain\Database\Traits\SortableRelation;
public $table = 'polilla_demo_invoices';
/**
* @var array dates attributes that should be mutated to dates
*/
protected $dates = [
'created_at',
'updated_at'
];
public $belongsToMany = [
'items' => [
\Polilla\Demo\Models\Item::class,
'table' => 'polilla_demo_invoice_item',
'pivotSortable' => 'sort_order',
]
];
}
= Table =
class CreateInvoicesTable extends Migration
{
public function up()
{
Schema::create('polilla_demo_invoices', function (Blueprint $table) {
$table->increments('id');
$table->text('invoice');
$table->timestamps();
});
}
public function down()
{
Schema::dropIfExists('polilla_demo_invoices');
}
}
= Fields YAML =
fields:
id:
label: ID
disabled: true
invoice:
label: Invoice
type: text
span: full
items:
label: Items in the invoice
type: relation
nameFrom: item
= Columns YAML =
columns:
id:
label: ID
searchable: true
invoice:
label: Invoice
items:
relation: items
select: item
Pivot Table
class CreateItemsInvoiceTable extends Migration
{
public function up()
{
Schema::create('polilla_demo_invoice_item', function(Blueprint $table)
{
$table->integer('invoice_id')->unsigned();
$table->integer('item_id')->unsigned();
$table->primary(['invoice_id', 'item_id']);
$table->integer('sort_order')->default(0);
});
}
public function down()
{
Schema::dropIfExists('polilla_demo_invoice_item');
}
}
In the backend, When go to Invoice > Create invoice I get this error
SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘polilla_demo_invoice_item.sort_order’ in ‘order clause’ (SQL: select * from
polilla_demo_itemsorder bypolilla_demo_invoice_item.sort_orderasc)
Testing
If you don’t specify the pivot table in the model, October will reach the relation in a table named invoice_item, even expected I think this is a potential problem for compatibility with other plugins, but it works. So, I create a table named invoice_item
<?php namespace Polilla\Demo\Updates;
use Schema;
use October\Rain\Database\Schema\Blueprint;
use October\Rain\Database\Updates\Migration;
/**
* CreateItemsTable Migration
*/
class CreateInvoiceItem extends Migration
{
public function up()
{
Schema::create('invoice_item', function(Blueprint $table)
{
$table->integer('invoice_id')->unsigned();
$table->integer('item_id')->unsigned();
$table->primary(['invoice_id', 'item_id']);
$table->integer('sort_order')->default(0);
});
}
public function down()
{
Schema::dropIfExists('invoice_item');
}
That’s great! now I can create an Invoice in the backend form and the relationship is displayed as a checkbox group. I can add Items to the Invoice too and these relations are saved in the pivot table invoice_item with the right sort_order value
The problem with this scenario is that the relation manager is not displayed as expected in the Invoices view. It displays as a simple checkbox list and not as a drag and drop to modify the sort_order.
This is the Invoices Controller. It have the RelationController implemented
<?php namespace Polilla\Demo\Controllers;
use BackendMenu;
use Backend\Classes\Controller;
/**
* Invoices Backend Controller
*/
class Invoices extends Controller
{
public $implement = [
\Backend\Behaviors\FormController::class,
\Backend\Behaviors\ListController::class,
\Backend\Behaviors\RelationController::class,
];
/**
* @var string formConfig file
*/
public $formConfig = 'config_form.yaml';
/**
* @var string listConfig file
*/
public $listConfig = 'config_list.yaml';
public $relationConfig = 'config_relation.yaml';
/**
* __construct the controller
*/
public function __construct()
{
parent::__construct();
BackendMenu::setContext('Polilla.Demo', 'demo', 'invoices');
}
}
This is the config_relation.yaml
items:
label: Invoice Items
list: $/polilla/demo/models/item/columns.yaml
form: $/polilla/demo/models/item/fields.yaml
structure:
showReorder: true
showTree: false
Conclusions:
Using the belongsToMany relation without overriding the name of the join table + SortableRelation trait
<?php namespace Polilla\Demo\Models;
use Model;
/**
* Invoices Model
*/
class Invoice extends Model
{
use \October\Rain\Database\Traits\Validation;
use \October\Rain\Database\Traits\SortableRelation;
/**
* @var array dates attributes that should be mutated to dates
*/
protected $dates = [
'created_at',
'updated_at'
];
public $belongsToMany = [
'items' => [
\Polilla\Demo\Models\Item::class,
'pivotSortable' => 'sort_order',
]
];
}
In October V2.2 the relation manager do not have drag and drop and displays as a group of checkboxes
In October v3 The relation manager displays drag and drop interface but it doesn’t keep the desired order. You can move up and down the items but always returns to the original position.
Tested in:
Overriding the join table name for many to many relation + use SortableRelation trait
<?php namespace Polilla\Demo\Models;
use Model;
/**
* Invoices Model
*/
class Invoice extends Model
{
use \October\Rain\Database\Traits\Validation;
use \October\Rain\Database\Traits\SortableRelation;
/**
* @var array dates attributes that should be mutated to dates
*/
protected $dates = [
'created_at',
'updated_at'
];
public $belongsToMany = [
'items' => [
\Polilla\Demo\Models\Item::class,
'table' => 'polilla_demo_invoice_item',
'pivotSortable' => 'sort_order',
]
];
}
In October v2.2 You get the Mysql Error described above
In October v3 everything goes fine excepts in Safari
Tested in:
I published the demo plugin code in my GitHub if you what to check it.
I hope to be clear (English is not my native language) and contribute in something, may be I’m implementing the relation wrong.
Have a nice day and greetings from Mexico
I have stumbled upon this quite a few times and I think the problem is with the RelationController not being able to initialize the relation or the FormController not picking up on your relation configuration, because they are not loaded or don’t exist in the create context.
I am not a 100% sure what I’ve exactly found, but if I remember correctly, you should be able to work around the problem by either eager loading the relation with the $with[] attribute on the model, initialize the relation in the create function of your controller (and maybe add empty models), move the relation field into the update context or check that your relations are really doing what you think they do.
I use relations with different levels quite often, and sometimes they seem to work, but they don’t really. Try to seed some models, and load the related records from different directions (parent, child, subchild) and you might find a configuration issue. $belongsToMany relations are always a paint point, especially of you are not closely follow the namings convenctions for keys and pivot tables (alphabetical order, singular etc.). Try to use all the options and explicitly specify the key names in the relation configuration.
I’m sorry, I don’t have enough time to look into your code, but I am pretty sure that you might find something. Or there might be a bug only we are bothered with 
But big thanks for your comprehensive report and the repo!
Thank you indeed for the detailed report.
A table name should always be specified, so we can exclude these tests.
There are two modes for this, and the behavior is different for v2. In the form field definition, you can set useController property. It is set to true by default in v3, but set to false by default in v2.
items:
type: relation
useController: false
With the above configuration, there is a bug that exists in both v2 and v3 that we will fix soon.**
items:
type: relation
useController: true
With the above configuration, everything should work. The Safari issue is fixed in the latest build of v3.
There is one more thing to be aware of: sorting only works in update context. In the create context there is no deferred binding support to apply the sort_order column as a sorted column – we are unable to fix this issue at the moment.
** If you want to apply the patch manually:
Open backend/formwidgets/Relation.php
Find the makeRenderFormField method
Locate the following line of code
$relationObject->addDefinedConstraintsToQuery($query);
In v3, replace it with the following:
$relationObject->addDefinedConstraintsToQuery($query);
if (in_array($relationType, ['belongsToMany', 'morphedByMany', 'morphToMany'])) {
$query->getQuery()->reorder();
}
In v2, replace it with the following:
$relationObject->addDefinedConstraintsToQuery($query);
if (in_array($relationType, ['belongsToMany', 'morphedByMany', 'morphToMany'])) {
$query->getQuery()->orders = [];
}
Thanks a lot 
Adding
items:
type: relation
useController: true
To the fields definition fix all and the plug in is compatible with October v2 and v3
Have a great day