Sort data from table alphabetically in Backendless/Dropsource


#1

Hey all,

I’m hoping someone can help me out here. My app has a page of locations but they are displayed in a random order. I want to display them alphabetically. I see the query parameter for sortBy in the API schema but I’m not sure how to execute that type of query by API call inside of Dropsource or if it’s even possible.

@markpiller I’m hoping you might have some insight on this as well.

Thanks!


#2

Hi Matt,

The sortBy property should be an array of strings. Each string is a name of a column you want the results to be sorted by. I think Dropsource supports arrays in the payloads, I haven’t checked.

See if you can figure out how set an array value (possibly by binding the property to a page variable declared as an array).

Regards,
Mark


#3

Thanks @markpiller. When I select the sortBy query type, I can’t bind it to any type of data. I was thinking I could create the string here but no options come up. My brain isn’t quite functioning at full capacity at this hour. I thought the query parameters/options were built to append to the URL payload so that’s what’s confusing to me. I read a previous post about a bubble project that needed to be sorted by the created date and they achieved it by using the query parameters. I think I might not have the coding knowledge for the steps to take on this one.


#4

Hey @markpiller just wanted to update you here. After some much needed sleep, I realized what you meant by the string payload. I remembered watching a video you did on autocomplete by using the Where clause and setting a page variable. So I set a page variable as a swift string type and then set that value prior to running the API request. The value was set to location_name%20asc. However, when selecting the sortBy query type, then the Page Variable, I still won’t allow that to be set. Is that a limitation of Dropsource not allowing that type of query? Not sure why it will allow a page variable for the where Clause but not the sortBy.

I’m looping in @wade as well to see if he has any insight.


#5

Hi Matt,

I haven’t tried this either but I reached out to engineering to find a bit of clarity on any limitations regarding our ability to formulate API Requests. I will follow up with what I hear.

I remember a conversation though before regarding sending an array of strings as a parameter value in an API Request and I feel like I was told we can’t support that at this time. I’m looking back in my notes to verify the context of that conversation. I will follow up with what I find.

Great question and thanks for bringing it up in forum.


#6

Hey Wade,

Thanks for the info. It’s interesting because it seems like the issue is specific to the sortBy query property. I can send a string in with the “where” property but not the sortBy. Seems like they would both follow the same syntax but I’m not the expert here. Thanks for looking into. Very much appreciated on my end.

Also, long time time no chat. I hope you are doing well my long, lost friend.


#7

Hey Wade, actually I just looked at the API docs and the definition for the sortBy query property is of type: Array. So I guess this comes down to whether you guys support an array on a query of this type. I really hope that you do because that would just make my day. @markpiller I understand now what you meant in your first response to this question.


#8

Hi @matt,

I just had a chance to look into it. I could not find a way to create an array. It looks Dropsource does not support it yet. See the workaround below you could use. With this approach you have full control on the Backendless side for any parameters passed into the Backendless API:

  1. Create an API service in Backendless (I am using Codeless for this example, but you can use Java or JavaScript as well)
  2. Add a method which will return your data. Add any arguments if needed.
  3. Select your service and method in the list of services. Select the LOGIC tab and click the EDIT button to edit the logic:
  4. Here’s a logic which will retrieve data from your data table with sorting. The arrows show which sections they come from:
  5. Click DEPLOY MODEL to publish your API Service:
  6. Switch back to the API Services tab. You can test your service logic by clicking the INVOKE button:
  7. Now the service is done and you can generate the Swagger API Doc. Click the button as shown and follow the instructions. From here on it should be familiar to you:

Hope this helps.

Mark


#9

Hey Mark,

This does help. I’m going to give it a test in a bit.

On another note, maybe I’m not thinking about this correctly but is the sortBy property really just a string appended to the end of the a URL? From what I can see when performing any api call with sortBy from a browser there isn’t any array of data being sent in the header. I know the API defines this as an array but since we are just simply querying the data couldn’t we change the definition of the sortBy to be of a string type?


#10

Hey @markpiller,

I’m testing this now and I’m running into a snag. When I invoke the method I’m receiving a 400 bad request error. Here it is “400 - Backendless encountered an error while handling the request. An internal trouble ticket with ID 01616D7D-E711-D11C-FF93-A913E1802300 has been created and we will be investigating the issue.” I have setup everything up in the same manner that you did. I don’t believe that I need any method parameters. Here is my setup and my table that I’m trying to retrieve data from. I might have missed something.



37%20AM


#11

Welp. I think I just solved it. Sorry for crying wolf there. The “create list action” need to be edited to only have one list creation. Thanks for the help!


#12

Nice troubleshooting and experimenting there.

@markpiller thanks for sharing such detailed instructions here!


#13

Thanks @wade

Hey @markpiller, Can I build on this API service if I wanted to retrieve these locations and display them by closest to the end user or is this something that is natively built into Backendless using the Geolocations?


#14

Hi @matt,

Yes, you can continue building on the API service to enhance it further. If your data objects have links to geo points in Backendless, you can use the distance operator in your where clause queries. See the documentation for more details:
https://backendless.com/docs/rest/data_search_by_distance.html

Regards,
Mark


#15

Just to add here… This is 1 of those questions I see lingering around here often enough and before Backendless, we didn’t have a good answer here. With Backendless able to send responses back built from “results by proximity”, it’s literally a whole reason in itself for certain use cases to integrate with Backendless for their data services needs.

Thanks for providing this type of filter method in your offering, Mark.


#16

@wade, I know this thread has technically been resolved but a couple of posts back you said that you remember not being able to support arrays in the API payload at this time. Do you know if you guys will be supporting this soon?

This is going to be a 2 part question for both you and @markpiller

I’ve been utilizing the codeless custom business logic for our locations but we also have events that I was wondering if you might could share the best way to sort them by date. In Backendless, your date field is a timestamp(in milliseconds) and I know it can be queried by the timestamp or by a string value. However, I want to return the results sorted by event date. Simple enough. I have that done. However, I can’t quite figure out how to convert that timestamp in Backendless to a readable date that I can then bind to a label in a dynamic view. I see there’s a way to convert a timestamp in Dropsource but after i set that action I can’t bind it to any label. Is there a way to convert the date to a string during the API request?


#17

I am not sure if there is a way to convert a timestamp to a readable data in Dropsource. I hope that @wade can clarify it. In case if it is not possible, your other option is to iterate of the objects in your business logic and replace the timestamps with string values (there is a codeless block for that).


#18

Thanks @markpiller. I do see a convert to string option in Dropsource. I might need to iterate the array to get the results. I would like the business logic to return the converted time stamp. However, I tried every combination of blocks to get that result and I couldn’t get it to work. I hate to ask “how can I make it work” but…how can I make that work in the codeless business logic?

Also, this question should probably be posed in the Backendless forum but if I change the schema by adding a new column into a data table, does that schema not update on the business logic? I added a new field in the one of my tables and when I set the business logic method to be an array of the type of that data table, then export the API doc, it doesn’t have that new field.


#19

Here’s an example of how to iterate over a query result and inject a property which is a stringified date:

As for the schema question, please post it to the Backendless support forum.

Regards,
Mark


#20

@markpiller as always, thank you very much. I greatly appreciate this. I will post in the Backendless forum with screenshots and an example of the issue I am seeing.