Sort sequence for a list from an external db MySQL view

[peterk900]

This post (http://fabrikar.com/forums/index.php?threads/using-a-mysql-view-as-data-source-for-a-list.41371/) managed to create a list from a view in an external database by directly editing the fabrik_lists table (db_primary_key) to enter the PK - the Fabrik form throws an error and the key selection is not saved. Also in order to set up a pre-filter you need to edit the table - this time the params field. This works very well.

I'm trying the same technique to set up the Order by data for the list as this is not possible via the Fabrik forms. I've found the two fields that look like the right ones - order_by and order_dir . By inspecting the values in these fields for lists based on tables, it looks as though the value for order_by is a string containing the element id e.g "74". order_dir seems straightforward - either "ASC" or "DESC".

I tried the element id route but this gave an error - the SQL included the literal value. However if I insert the fully qualified field name e.g. ["vWItemsForColl2___ColDate"] it 'seems' to work. The only problem is that the ordering is erratic - so in fact it may not be working at all - simply just not giving an error. The other approach I tried was changing the MySQL view to include an ORDER BY clause - but this seems to have had no effect.

What values should I insert in the fabrik_lists table to get a list to display consistently in the correct sequence ? Thanks.

PS. Using the item id failed because I picked the wrong one ! Using the right one not only doesn't cause an error but the order details are now viewable in the Fabrik form. But the order of the list still isn't right. Any suggestions on what else I might have got wrong ?

 

[troester]

It's something strange with your mySQL view.
I've got a list connecting to a view in an other DB (on the same mySQL server localhost) working without problems.
I can replicate the "collation error" if I add "isview":"0" to the params but all is ok with "isview":"1" (or no isview param at all).
Can you doublecheck if your list params are ending with
...."allow_drop":"3","isview":"1"}
(3 can be any other access level value)

It's surely no real solution to edit Fabrik meta data tables.
My (relevant) settings

 

[peterk900]

It looks to me as though the problem is a date format one. My dates are displayed DD-MM-YYYY and this is how they are being sorted. 01-01-2018 then 10-03-2015 and then 31-12-2019.
Do I need a hidden date field automatically updated in line with the user displayed field ? I then use this field to sort on. If this is the right way to go, how do I update the hidden field automatically every time the user changes the visible field ?

 

[peterk900]

Thanks troester - yes I agree about meta data, out of program control, updating.

You are right - the value isview was 0 and when I change it to 1 I can save the sort order. So maybe that is the solution for all the other issues - once you correct the isview setting you can do everything without table tinkering. Brilliant.

This view is on the same server (same IP address) as J! but remote.

But I now think my sort sequence problem maybe unrelated as per my previous post.

 

[peterk900]

I went back to a failed view and tested again changing the listview to 1 via a meta-data edit. Making this change does allow the saving of the primary key which means that the view will display. Sorry - wrong - you have to set the primary key via a meta data edit - even with the listview change I am unable to save the PK via the Fabrik user interface. Sorry for the confusion.

 

[troester]

Really strange. No idea what's the difference in your setup or DB.

 

[peterk900]

I created two new lists from views and both worked as you said - all you need to do is alter the meta-data in the fabrik_lists table for the list in question changing isview from 0 to 1 . I must have either been doing something strange or the list may have been messed up by the other meta-data changes. Sorry for confusing the situation.

The sorting issue is also my fault. Before getting guidance from this post I included the date format function in the SQL to create the view. This renders the field in the view as a string - which is why the sorting sequence was messed up.

So I can now use external db views with confidence, get date sequenced lists to display perfectly and group by a date without an unwanted time messing up the grouping. Very encouraging. Thanks to everyone for their input.

 

[troester]

Did you really change "listview"?
I think it must be "isview".

 

[peterk900]

Yes I've corrected the post. Apologies.