Dynamic Sort on column with Alias name in query does not work

[Philip_J]

Wappler Version : 3.0.3
Operating System : Windows 10

Expected behavior

This issue seems to be only for queries that have at least 2 tables JOINED in the query and use at least 1 Alias

When you apply dynamic sorting on a table using a DB query, you expect it to work on all columns, even ones that you give an 'Alias' to in the query builder.

Actual behavior

When dynamic sorting is set up, it will only sort on the columns that DO NOT have an alias set up in the query builder.

Any column that you have given an Alias too will not work and will fail with following (extract) message:

order by \"call_types\".\"callName\" desc limit $1 - column call_types.callName does not exist\n at Parser.parseErrorMessage

Where call_types is the table name and "callName" is the Alias

How to reproduce

• Create DB query that has at least 2 tables

• Give an Alias to half of the columns

• Create a table in app connect and apply sortable headers/dynamic sorting as per normal

• Test the sorting on the different columns. The ones that use an Alias will NOT work and will cause an error

here is a snapshot of a sample query:

In this example, the sortable headers work fine for item # 3, work_group_id but all ones with an Alias will fail. If this query did not have a join, it would also work, even with the Alias

[elpod]

I have the same issue on 3.21, anyway around this without having to change my column names in the database

[Philip_J]

It’s still a bug and no way around it that I’ve found (apart from not using an Alias)

I know this is flagged as Medium, but any chance it can get. Look at soon for a fix @patrick?

[UKRiggers]

I am still getting this issue. Any resolution?
My error where

{
    "code": "42S22",
    "file": "C:\\wamp\\www\\daviesandgray.co.uk\\dmxConnectLib\\lib\\db\\Connection.php",
    "line": 112,
    "message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'img.num_imgs' in 'order clause'",
    "trace": "#0 C:\\wamp\\www\\daviesandgray.co.uk\\dmxConnectLib\\lib\\db\\Connection.php(112): PDO->prepare('SELECT `tbl_job...')\n#1 C:\\wamp\\www\\daviesandgray.co.uk\\dmxConnectLib\\modules\\dbconnector.php(197): lib\\db\\Connection->execute('SELECT `tbl_job...', Array)\n#2 C:\\wamp\\www\\daviesandgray.co.uk\\dmxConnectLib\\lib\\App.php(195): modules\\dbconnector->paged(Object(stdClass), 'sa_q_live_repor...')\n#3 C:\\wamp\\www\\daviesandgray.co.uk\\dmxConnectLib\\lib\\App.php(159): lib\\App->execSteps(Object(stdClass))\n#4 C:\\wamp\\www\\daviesandgray.co.uk\\dmxConnectLib\\lib\\App.php(127): lib\\App->execSteps(Array)\n#5 C:\\wamp\\www\\daviesandgray.co.uk\\dmxConnectLib\\lib\\App.php(116): lib\\App->exec(Object(stdClass), false)\n#6 C:\\wamp\\www\\daviesandgray.co.uk\\dmxConnectLib\\lib\\App.php(94): lib\\App->exec(Object(stdClass))\n#7 C:\\wamp\\www\\daviesandgray.co.uk\\dmxConnect\\api\\Reports\\sa_q_live_reports.php(8): lib\\App->define(Object(stdClass))\n#8 {main}"
}

[sbecks]

This looks to still be an issue. I get an error when using an alias, but it sorts OK when using just the column name. I’m using Node JS.

Error: ER_BAD_FIELD_ERROR: Unknown column 'community.community_active' in 'order clause'\n at Query.Sequence._packetToError

I have some functions on the page that don’t work using the column name as ‘active’ in the query. For example this:

Make {{active==1?'Inactive':'Active'}}

will only ever return ‘Make Active’ whereas this:

Make {{community_active==1?'Inactive':'Active'}}

toggles Active/Inactive as expected. I assume using active is conflicting somewhere.

Would be really useful to know if the sorting by alias is fixable, I have the same column name and behaviours in a range of tables. If it’s not I can change the column names in the DB and related queries, but it would be preferable to be able to use an alias!

[Philip_J]

Yeah i think this one never got fixed… @patrick ?

[TwitchBlade]

I’m suddenly getting this as well in v5.8.2 Odd thing is, it’s not doing it in my dev environment but on production. I’m wondering if I haven’t uploaded an updated wappler file that handles the sorting. Not sure which one it would be though.

[m_gabor]

Hi @patrick

Just ran in to this, look like still not fixed, standard bootstrap 5 table with generator and query manager sorting. The generator set the sorting to the alias of course but gives a server 500 error as the column does not exist. Let me know if you need more info/screenshots etc.

Gabor

[Philip_J]

I haven’t looked into this in a while, I probably just had to turn sorting off etc.

But if you need it, something I think would work (and also if you want to use pagination with a custom query) is to write the query as a View in your DB. Then you can access it as Wappler as a simple query without changes.

I use Postgres so not sure if others have Views.

I figured this would work with any complex query where you’d want to take advtage of some Wapplers built in features such as the aforementioned Pagination.

[ivan.io]

I just came across this bug. Considering the date of the first report, it's unbelievable that this bug still persists...

[patrick]

Seems it was fixed back in 2001, but it looks like it was only for PHP and the NodeJS still has the issue.

Sortable Headers - not working on joined COUNT - Wappler General / Bugs - Wappler Community

Here an update for NodeJS: dbconnector.zip (3.5 KB)
unzip to lib/modules.

[Teodor]

Fixed in Wappler 6.7.3