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?

  • 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 at 11:09
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)

  • 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 '16 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 '16 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 '16 at 1:28
  • just put the joins into the query and use concat(). You are overthinking this – jdog Jul 26 '16 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 '16 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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