0

I have a view that displays a list of data from a table (I'm targeting Joomla! 3.5+). However, I fabricated a column that stitches data together after some manipulation. I do this in the getItems() method after the data has been pulled from the database.

The column and manipulated data display properly in my view, but I need to be able to sort the column.

Typically ordering is established in the getListQuery() method. Unfortunately I don't have a column in the table that corresponds to the fabricated column which is built after each query.

I might be able to order by multiple columns, but I'm using lookup tables which complicates things. I guess I can write my own sort (the data is text) and apply it in getItems(). Is there's a better way to accomplish my goal?

Improve this question
1
  • This question (and accepted answer) wil be low value to researchers because it is Unclear, vague, and lacks context. jquery is a javascript library, not a collection of Joomla query methods. We don't see any actual code or database table schemas, so offering support that will ultimately help others is impossible.
    – mickmackusa
    Feb 21, 2019 at 11:09

1 Answer 1

Reset to default
0

With SQL you can usually put your "fabrication" into the order by clause as a column. Not sure if the Joomla functions will mangle this but worth a try. For example this works:

Select concat(a,b) from c the inner join d on c.id=d.foreign_id order by concat(a,b)

Improve this answer
8
  • The problem is that I need to pull data from 3 reference tables and then perform some logic to determine what needs to be stitched together and in what order. How can I do that without writing a MySQL function? Perhaps that's a better approach. Would it be better to do that then to sort it in PHP?
    – nimble
    Jul 24, 2016 at 14:54
  • Hi, can you post what you are actually doing to the data? You have to balance a number of problems. Accurate paging is only going to work performantly with using mysql functions. The other option I can think of is to load the data with ajax and use some sort of infinite scrolling, so you don't need to know the number of records.
    – jdog
    Jul 24, 2016 at 20:34
  • I have an address that's been broken down into individual elements that I need to reconstruct. It pulls directional data, street types, etc. from reference tables that don't change. So, I have foreign keys along with some other data. Perhaps the easiest and most effective way to do this would be to add an additional field to the record that contains the reconstructed address string. I could then add an observer or do something else to let me reconstruct the string before the data is saved. What do you think?
    – nimble
    Jul 26, 2016 at 1:28
  • just put the joins into the query and use concat(). You are overthinking this
    – jdog
    Jul 26, 2016 at 22:59
  • I appreciate your help but I just don't see how to do it in a query. Perhaps my SQL skills just aren't strong enough. How do I do something like: if (b != -1) then concat(a, b) else concat(a, c). Then concat that result something else?
    – nimble
    Jul 27, 2016 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.