SQL - adding fields to query to sorty by

Question

I'm working with a third party software package that is on it's own database. We are using it for the user management back bone on our application. We have an API to retrieve data and access info.

Due to the nature of information changing daily, we can only use the user_id as a pseudo FK in our application, not storing info like their username or name. The user information can change (like person name...don't ask).

What I need to do is sort and filter (paging results) one of my queries by the person's name, not the user_id we have. I'm able to get an array of the user info before hand. Would my best bet be creating a temporary table that adds an additional field, and then sorts by that?

Using MySQL for the database.

Answer 1

You could combine the data into an array of objects, then sort the array.

Answer 2

You could adapt the stored procedure on this page here to suit your needs the stored procedure is a multi purpose one and is very dynamic, but you could alter it to suit your needs for filtering the person table.

http://weblogs.asp.net/pwilson/archive/2003/10/10/31456.aspx

Answer 3

Yes, but you should consider specifically where you will make the temporary table. If you do it in your web application then your web server is stuck allocating memory for your entire table, which may be horrible for performance. On the other hand, it may be easier to just load all your objects and sort them as suggested by eschneider.

Answer 4

If you have the user_id as a parameter, you can create a user defined function which retrieves the username for you within the stored procedure.

Answer 5

Database is on different servers. For all purposes, we access it via an API and the data is then turned into an array.

For now, I've implemented the solution using LINQ to filter and out the array of objects.

Thanks for the tips and helping me go in the right direction.