Sortable Headers - not working on joined COUNT

[sophos707]

Hello!

I’ve got a Bootstrap table that works fine for the most part.
No matter what column I put it, it’s sortable - works great.

The goal is to be able to use sortable headers all all columns.
However, it won’t sort these two columns: “Last Played” and “# of Plays”.

Both of those columns are from an INNER JOIN and are aggregated values.

The error that it throws is “SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘DatesofSongs.SongDateCount’ in ‘order clause’”.
Peculiar.
I’m not sure how to fix that or why when I try to sort by that value it throws an error.

Any ideas? I’ve gone round and round and cannot seem to fix it.

[Alexander_Paladines]

Just not add “COUNT” OR “MAX” in Aggregate column in Database Query Builder.
Remove this:

In your html repeat table just write using Alias:
SongDateCount.count()

And the same in header table:
dmx-on:click="query1.set('sort','SongDateCount.count()');query1.set('dir',query1.data.dir == 'desc' ? 'asc' : 'desc')

[sophos707]

That returns no data in the columns.
Additional problem is that by not “counting” or finding “max” in the database query it returns thousands of results instead of 150.

It’s a database of songs.
One table contains all the song data.
The joined table contains a record of every date that every song was played.

Trying to count so we can show the last time they played it and how many total plays they’ve had.

[Alexander_Paladines]

Don’t use the join in Database Query.
If your database is properly using Foreing Keys, in database query builder you can use subtable instead of JOIN.
In that way you can add subtable directly, not adding nothing in “Aggregate column” and add an “Alias”.
Now in your subtable add the desire column to be counted and in “Aggregate column” select COUNT and an Alias.
In html now you will see using data binding in cell table the new value, probably something like this: SongDateCount[0].newAlias

[scalaris]

Try tabulator

Tabulator - Interactive JavaScript Tables

[sophos707]

Okay, so I’ve been trying to read about Wappler’s subtables.

Is that how Wappler prefers to handle those kind of sorts?
I was under the impression that having the DB do the aggregation was better than sending all the data back and forth.

But, I’m probably misunderstanding Wappler and subtables.

{EDIT: I guess I do realize that Wappler makes a different table on my MySQL but I’m still confused}

I’ve read here: Creating Sub Tables with Database Manager

I’m not sure how to apply a subtable to my situation.

My DB structure.
Every worship song has a Song_ID.
Every time the band plays that song it’s entered into the “WorshipSongDates” table. The foreign key connects on the WorshipSongDates - SongID

So, do I make a subtable underneath WorshipSongDates or underneath WorshipSongs?
And what do I do with it?

I’m sorry. It’s been a long week, maybe I’m tired. LOL!

[Alexander_Paladines]

Hi,
I have recreate your two table to show how to make subtables/nested tables to works.
This are your tables:

As you can see there is a Foreign Key in table WorkshipSongDates point to WorkshipSongs table:
SongID->Song_ID
Main table is WorkshiptSong

Now in Wappler Database Query Builder when you add a table you will see the two tables, when table name have not an underscore to difference from main table and subtables that have foreign key, Wappler will show with a slash as a separator character “/”, in this case “/ Dates”:

The next is to add the main table: WorkshipSongs, and you will see now the nested table as Dates (WorkshipSongDates)

Now you can add the desired columns into the builder even the nested table, it get automatically an Alias with same name of the nested table.

At this point you can make double clic in the nested table and it will open a new pop-up windows of the nested table, in this point you can add in this nested table in “Aggregate Column” the COUNT event, without forget to add an “Alias”

Finally in the UI you can add inside your tableRepeat the “Alias” of the nested table that have a COUNT, in this case songCount, now in the binding area your will see this:

Dates[0].songCount

This will not send all data in query, just the COUNT of the nested table.

I hope this helps you.

[sophos707]

Thank you!

Maybe here’s where I’m confused.
I’m not making these new - I’ve already created these tables (years ago; I’m rewriting the website in Wappler).
There are already a foreign key from WorshipSongDates (SongID) to the WorshipSong (Song_ID) table.

How do I make it a subtable in Wappler if it already exists?

[Alexander_Paladines]

Hi @sophos707, is indiferent if table is old or new. My test was in nodeJS server, maybe if you are in PHP something is missing. I stopped working on PHP so I don’t know how is the state of much of functions that I use in nodeJS. Maybe @George can help here.

[patrick]

There seems to be a bug with sorting on an alias column in combination with joins.

Please test the following updated files:

SqlBuilder.zip (3.2 KB) unzip to dmxConnectLib/lib/db

dbconnector.zip (2.5 KB) unzip to dmxConnectLib/modules

[sophos707]

Thank you, @patrick.
The updated files work perfectly - I can sort as intended off of the joined COUNT query.

Will that be in the next update, presumably?
What I’m asking is if the next update comes out will I need to re-upload those two files again or no?

[patrick]

It will be included in the next update.

[Teodor]

Fixed in Wappler 5.8.2