DB error in wp582: Column ‘term_order’ in order clause is ambiguous

Hi,
I tested on a new install with WP 5.8.2 and the plugin. I don’t see that error.

It could be that the term_order field does not exist. Could you check the database table and if there is a column for term_order?
Edit: hmm, I tested after deleting that column, but then the error is that the column does not exist…

Okay, disregard that ??

It seems to be using term_order while it should be using t.term_order. Now it is confused which table it should use.

• This reply was modified 3 years ago by Marcel Pol.
• This reply was modified 3 years ago by Marcel Pol.

Both _terms and _term_relationships currently have a term_order column.
As the error states, Column ‘term_order’ in order clause is ambiguous. That means you should namespace that clause:

ORDER BY term_order

Tell and write which term_order should be considered: t.term_order or pll_tr.term_order

That said, ‘pll_tr’ seems to come from polylang plugin, which is currently in use. Would you confirm?

Though, I can see from wp docs that term_id is native on _term_relationships, but it’s not on _terms. Is it added by your plugin?

The only database change should be:
$wpdb->query("ALTER TABLE $wpdb->terms ADD term_order INT( 4 ) NULL DEFAULT '0'");
The wp_terms table gets an extra term_order column.

There was some commented out code that is brought back and it should be the right solution. Could you try the current git version at:
https://gitlab.com/toomanybicycles/custom-taxonomy-order-ne

It does not work. I took a look to your code: again, there are 2 tables with term_order column. _terms is the one you’re altering, _term_relationships already has term_order by design. In your queries, you must specify which term_order you’re using for the order query. Or, if possible, you could refactor your code to use the native _term_relationships.term_order, if applicable.

I’ll write also to the polylang dev, since it could be a polylang issue.

Ok, I already took a look to some more logs: I definitely believe you should refactor your code a bit: since term_order is in fact a native _term_relationships column, a lot of plugins could use it in their queries (like it seems polylang is doing), without namespacing it.

But it’s your plugin that alter the default database by adding a column to _terms. Thus, I think it should be your responsibility to carefully choose a name for that column that will not cause issues to others. Just rename that column namespaced, something different from any wordpress native column: something like ctne_term_order, and then update your code. That will fix the issue and prevent it from coming back if some other plugin will do queries using _term_relationships’s term_order column.

• This reply was modified 3 years ago by stratboy.

Tricky situation…

Using orderby tr.term_order is only valid when the term_relationships is joined. Doing that from this plugin might be quite hairy ?? and cause unintened behaviour for other users.

Renaming the term_order column in wp_terms is an option that I would need to think about.

I checked the polylang plugin, but didn’t see it do anything with term_order. Could it be there is a different plugin or theme adding that join and orderby?

I checked the polylang plugin, but didn’t see it do anything with term_order. Could it be there is a different plugin or theme adding that join and orderby?

I currently cannot verify this thing because my local version of the site does not suffer of this problem (don’t know why). And on the production version I cannot disable all the plugins to see. But what I can say is:

– if I disable yours, the problem is gone

– the query IS made by polylang, you can find query pieces on translated-post.php and translated-term.php files. Just search for pll_tr string, join_clause() funcion, rows 100 and 160.

• This reply was modified 3 years ago by stratboy.

Did you took a closer look at this issue?

Hi,
I am wanting to take a look, but I really do need to be able to reproduce this. I already looked at it again, but I am not able to reproduce it. If I cannot reproduce it, I have plainly no idea where to even start looking for a possible fix and I am just wasting my time (I just did).
I don’t think the fix is straightforwardly to change the prefix of the database column in this plugin. In 8 years and 40,000 active installs, this issue hasn’t come up before. I would really want be able to reproduce the problem to have a good idea of what the fix could or should be.

Can you tell me which plugins and which theme you use? For the theme, please use the slug. For the plugins, I would be happy to see the list from active_plugins from the wp_options table. If it is privacy sensitive, feel free to email it to [email protected]
If there are commercial plugins, please include a zip file. For debugging purposes this should be no problem in regards to commercial licensing (it should all be GPL anyway).
I assumed product_categories was from WooCommerce, but that uses the taxonomy product_cat.