How to order a on sub table fields

Wappler General How To
#1

Hi,
sorry to ask what I am sure is a stupid question but I have googled and searched to death :frowning:

I have a users table which contains usual username/password/id.
I have a sub table UsersData - this contains their name/contact details.

I create a list of users, and I want to order by the name, which is in the sub table. I am aware from reading around that you cannot order by the sub table data in the query builder as its a seperate query (or along those lines).

So, how do I order the results returned in the app/server connect/repeater? I just can’t figure it out or find suitable reference answers.

its php/windows/latest wappler.

thanks

#2

Hi James,

Would an Inner Join accomplish what you are trying to do from the query builder?

I tried to mimic what you have setup. A Users table with a Data subtable. Users has username and password, Data has name and contact. A quick mock up so passwords are not encrypted or anything like that.

I added the sorting option to sort by the name field in the Data subtable. Results is sorting by the name as seen in the screenshot query results. Can change to only include the columns you need instead of all like I am doing here.

See the screenshots for an example

image
image1020×600 20.4 KB

image
image1020×600 23.1 KB

2 Likes
#3

HI, yeah an inner join would work but just felt that made the sub table a bit lame, and i did read there was another way to sort so rather than re-code my pages and use an inner join was hoping there was a simpler solution, as when i coded up the pages i didn’t realise the sort would not work on sub tables :frowning:

thanks for the reply, the inner join will work if that is the only approach available

#4

Yes, join is the solution.
To be honest, that’s a design issue, the user data is not really “sub data” in your case, it is related data.
Sub tables are really more suited to parent / child situations where the parent potentially has multiple children. In your case the data is a one to one relation so a join is more suitable.

#5

thanks everyone, will alter now to a join, its not quite one to one as a user can have multplie phone/email etc, but its quite rare, thanks