Bug in pivot sorting

[vakopian]

• OctoberCMS Build: 454
• PHP Version: 7.2
• Database Engine: MySQL 5.7
• Plugins Installed: none

Description:

Two models called Product and Category that are in a many-to-many relationship with a pivot table. The pivot table has a column relation_sort_order, which should be used to sort the products within a category. The belongsToMany in Category is set like this:

    public $belongsToMany = [
        'products' => [
            'Acme\Plugin\Models\Product',
            'table' => 'acme_plugin_category_product',
            'pivot' => ['relation_sort_order'],
            'order' => ['relation_sort_order']
        ]
    ];

When trying to create a new category, the following error is received:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'relation_sort_order' in 
'order clause' (SQL: ...)

where it's trying to run the following sql:

select `acme_plugin_products`.*
from `acme_plugin_products`
where (acme_plugin_products.id in (select `slave_id`
                                    from `deferred_bindings`
                                    where `master_field` = products
                                      and `master_type` = Acme\Plugin\Models\Category
                                      and `session_key` = 17Ik8vNIs7tBP5fhNTcTlY3IEv4JpyS3W6gzobGS
                                      and `is_bind` = 1))
  and acme_plugin_products.id not in (select `slave_id`
                                       from `deferred_bindings`
                                       where `master_field` = products
                                         and `master_type` = Acme\Plugin\Models\Category
                                         and `session_key` = 17Ik8vNIs7tBP5fhNTcTlY3IEv4JpyS3W6gzobGS
                                         and `is_bind` = 0
                                         and id
                                           > ifnull((select max(id) from deferred_bindings where slave_id = acme_plugin_products.id
                                         and master_field = products
                                         and master_type = Acme\Plugin\Models\Category
                                         and session_key = 17Ik8vNIs7tBP5fhNTcTlY3IEv4JpyS3W6gzobGS
                                         and is_bind = 1 )
                                           , 0))
order by `relation_sort_order` asc, `product_name` desc

So the order clause is clearly added to the wrong query.

Steps To Reproduce:

• Create two simple models Product and Category.
• Create a pivot table:

class CreateCategoryProductTable extends Migration
{
    public function up()
    {
        Schema::create('acme_plugin_category_product', function(Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->integer('category_id')->unsigned();
            $table->integer('product_id')->unsigned();
            $table->integer('relation_sort_order')->unsigned()->default(0);

            $table->foreign('category_id')->references('id')->on('acme_plugin_categories');
            $table->foreign('product_id')->references('id')->on('acme_plugin_products');
        });
    }

    public function down()
    {
        Schema::dropIfExists('acme_plugin_category_product');
    }
}

• Configure the RelationController in Category model:

    public $implement = [
        'Backend.Behaviors.FormController',
        'Backend.Behaviors.ListController',
        'Backend.Behaviors.RelationController'
    ];

• Configure belongsToMany in Category model:

    public $belongsToMany = [
        'products' => [
            'Acme\Plugin\Models\Product',
            'table' => 'acme_plugin_category_product',
            'pivot' => ['relation_sort_order'],
            'order' => ['relation_sort_order']
        ]
    ];

• Create the Categories and Products controllers, and add the RelationController to Categories, with standard

    public $relationConfig = 'config_relation.yaml';

where config_relation.yaml is also standard:

# ===================================
#  Relation Behavior Config
# ===================================

products:
  label: Product
  view:
    toolbarButtons: add|remove
    list: $/acme/plugin/models/product/columns.yaml
  manage:
    list: $/acme/plugin/models/product/columns.yaml
    form: $/acme/plugin/models/product/fields.yaml

[vakopian]

https://www.bountysource.com/issues/74048786-bug-in-pivot-sorting

[Cleanse]

How are you making the call on the pivot? I use October and just built a plugin with pivot sorting.

public $belongsToMany = [
        'matches' => [
            'Cleanse\Event\Models\Match',
            'table' => 'cleanse_event_broadcast_match',
            'pivot' => ['lineup'],
            'order' => 'pivot_lineup asc'
        ]
    ];

Maybe try changing your line to: 'order' => ['pivot_relation_sort_order'].

[w20k]

Might be a fix you're looking for #4432

[vakopian]

@Cleanse , using pivot_relation_sort_order doesn't work, it just complains about Unknown column 'pivot_relation_sort_order' in 'order clause'. Would you mind sharing a working code example where pivot sorting works?

@w20k please note that the example above does not use the Relation widget, which your pull request is fixing. It uses the Backend\Behaviors\RelationController behavior.

[Cleanse]

I'll try and get you an example out tomorrow. My project does not use the Backend, so that could be why. I'll test a backend example similar to your setup.

The project I created that has working pivot sorting: https://github.com/Cleanse/events/blob/master/models/Match.php

[github-actions]

This issue will be closed and archived in 3 days, as there has been no activity in the last 30 days. If this issue is still relevant or you would like to see action on it, please respond and we will get the ball rolling.

[LarryBarker]

@vakopian What exactly are you trying to do here? I think the issue is due to the fact that you are not defining a pivotModel on your relationship. If you could explain your desired end result, and possibly provide a detailed example, I may be able to point you in the right direction.

[adamo]

I'm having the same problem, using the sorting work but I cannot attach (add button) any model in the relation widget

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'relation_sort_order' in 'order clause' (SQL: select depcore_inspiracjekludi_products.* from depcore_inspiracjekludi_productswhere published = 1 anddepcore_inspiracjekludi_products.idnot in (137, 2, 150, 149) order byrelation_sort_orderasc,product_number desc)" on line 664 of /var/www/public/inspiracje/vendor/laravel/framework/src/Illuminate/Database/Connection.php

The order is displayed as it should in the back and in frontend.

[ghost]

I'm having the same problem, using the sorting work but I cannot attach (add button) any model in the relation widget

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'relation_sort_order' in 'order clause' (SQL: select depcore_inspiracjekludi_products.* from depcore_inspiracjekludi_productswhere published = 1 anddepcore_inspiracjekludi_products.idnot in (137, 2, 150, 149) order byrelation_sort_orderasc,product_number desc)" on line 664 of /var/www/public/inspiracje/vendor/laravel/framework/src/Illuminate/Database/Connection.php

The order is displayed as it should in the back and in frontend.

You're having exactly the same issue as me, hope someone finds a workaround it gets sorted soon

[adamo]

I've found some workaround fot displaying the relation window in my case the add
In the controller I override the method

public function onRelationButtonAdd()
    {
        SeriesModel::extend(function($model) {
            $model->belongsToMany['products']['order'] = array('product_number');
        });

        return $this->asExtension('RelationController')->onRelationButtonLink();
    }

I'm using a column from the relation not the pivot table.
I haven't found out how to do the same for the search in the relation window.

When trying to search I'm getting similar SQL error

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'relation_sort_order' in 'order clause' (SQL: select depcore_inspiracjekludi_products.* from depcore_inspiracjekludi_productswhere published = 1 and (((lower(depcore_inspiracjekludi_products.product_number) LIKE %48%) or (lower(depcore_inspiracjekludi_products.name) LIKE %48%) or (lower(depcore_inspiracjekludi_products.description) LIKE %48%))) anddepcore_inspiracjekludi_products.idnot in (137, 1, 150, 149, 11, 12, 148, 151, 2) order byrelation_sort_orderasc,product_numberdesc)" on line 664 of /var/www/public/inspiracje/vendor/laravel/framework/src/Illuminate/Database/Connection.php

[daftspunk]

Confirmed this issue has been fixed by 5f8a545

@bennothommo this is yours to close and claim the bounty!

[daftspunk]

Replication instructions:

1. Install the test plugin

2. Add the order sorting to the roles_pivot definition inside file plugins\october\test\models\User.php

    'roles_pivot' => [
        'October\Test\Models\Role',
        'table' => 'october_test_users_roles',
        'pivot' => ['clearance_level', 'is_executive'],
        'order' => 'clearance_level',
        'timestamps' => true
    ],
   

3. Navigate to Users > Click a User > Click Pivot Data Tab

4. Click the Add Role button

Without code in 5f8a545 an exception is thrown. With the code, the nefarious sort order appears to be removed and no error occurs

[bennothommo]

Thanks heaps @daftspunk :D

[bennothommo]

@vakopian Would you mind testing the fix provided above, and releasing the bounty if this has sorted out the issue for you?

[ghost]

Sorry if this is the wrong place to ask but how do we apply these fixes I manually went into the backend files and changed them to match the ones in the above commit.

Once I had done that I now can't view the list within the backend at all way before because I get a chance to click the add button within the relation controller.

Error: "Call to undefined method Backend\Widgets\Lists::getSortColumn()"

Am I on the wrong build number (458) or is applying this fix not as simple as copy pasting the correct cod from the commit?

[bennothommo]

@woshj The getSortColumn method in Backend\Widgets\Lists was only made public in build 460, which is an edge update. You will need to enable edgeUpdates in your config/cms.php file to retrieve that version.

[ghost]

@bennothommo Thanks mate! Now I can sort the columns although you still can't use the Add Button as mentioned by OP, adamo and myself

When trying to create a new category, the following error is received:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'relation_sort_order' in 
'order clause' (SQL: ...)

Unless I still have something wrong with my setup but it all seems okay to me and the sorting fix has been applied so I think it's working correctly

[vakopian]

Unfortunately this bug is still not fixed in the latest build 466. I just tried my test project and got the same error as @woshj.

This bug was a major blocker for the project I was attempting with octobercms and I spent many hours trying to fix it myself in the core, without success. This endeavor has proved to me that the enormous complexity in this framework is just not worth the effort (a bug like this should have been relatively easy to fix, but turned out to be next to impossible to debug and isolate). And thus, sadly, I have since abandoned the framework.

[LukeTowers]

@vakopian I would encourage you to give it another try, I've tested 24f87ae and it solves the problem with the add button too. The framework is not that complicated, but like any framework you have to invest time into understanding how everything fits together in order to solve complex issues with edge cases.