|
|
Post by fnllc on Jun 4, 2017 11:46:02 GMT
I'm trying to order the "object" field numerically. In property bag, all attributes are varchar. The following query is not ordering the records at all. I do not receive any error message either:
@track = Track.find(:all, :order => ["CAST(object as INTEGER)"])
Any idea on how to make it work?
|
|
|
|
Post by Vladimir Musulainen on Jun 4, 2017 12:11:57 GMT
Hi flllc,
what reason is for ordering by object field? In creation order?
|
|
|
|
Post by fnllc on Jun 4, 2017 17:19:13 GMT
I have an app that records GPS tracks. I'm trying to implement Rhoconnect and when the GPS track data is synched from backend to app, the track points are not saved in order. I verified that the backend app provides the point in correct order to Rhoconnect server. Since the points are not saved in order by the app, I need to sort them before mapping them.
|
|
|
|
Post by Vladimir Musulainen on Jun 4, 2017 19:05:54 GMT
I suggest to add the "createdAt" field with date time. It is more clear then using the object field.
|
|
|
|
Post by fnllc on Jun 4, 2017 19:53:26 GMT
created_at is not working, because it is the same exact time stamp for all points. This is probably due to using a DB transaction.
|
|
|
|
Post by Vladimir Musulainen on Jun 4, 2017 21:03:36 GMT
I mean that value for created_at should be set when app creates the next instance of GPS track data. It should not be default value for the field in the database.
pseudo code:
track_record = TrackRecord.new
track_record.created_at = Time.new
track_record.save_to_database
|
|
|
|
Post by jontara on Jun 5, 2017 4:03:55 GMT
FWIW, object values as assigned by Rhom are FLOATING-point numbers - represented as text.
If you cast to INTEGER, you will have duplicates.
Hint: you can assign object yourself! I've done it sometimes for predefined data that is loaded on first run.
|
|
|
|
Post by jontara on Jun 6, 2017 16:58:58 GMT
From observation, Rhom creates object values that look like a floating-point number with a single number behind the decimal place. The one digit behind the decimal place might often be .0 but sometimes will (consistently) be something else.
I don't know when the number behind the decimal place might be different. I'll bet it has something to do with RhoConnect, which I do not use.
You should not make the assumption that you can safely cast object to an integer. You have to consider object to be an opaque text field.
No, a :time value will not give you unique values, as it is only to the second. And while SQLite also supports floating-point time values (to the mSec) that won't guarantee unique values either.
You should add your own attribute with an incrementing value. You can do the incrementing yourself with a bit of code in the model, or you can use an auto-incrementing attribute if you aren't afraid to reach under the hood and use some SQL to add the attribute. I just increment the value myself. You query for the highest value and add one. It would be good to put an index on the column.
SQLite has a lot of power that is not exposed by Rhom. But you are free to use it. I seldom if ever use anything but direct SQL queries in Rhodes. And I define my own views and triggers.
Actually, I take it back... when I need an incrementing attribute, I write a trigger, and it is just added automatically when a new record is created. I suppose it would be a bit better to use an auto-incrementing attribute.
|
|
|
|
Post by fnllc on Jun 6, 2017 22:04:25 GMT
Jon,
thanks for the tips. I certainly can create my own field. My point is the casting for ordering is not working. To make it work, I needed to start the counter at 1,000,000 for example. If I start at 1 and go up it still won't sort correctly, because numbers in text will be sorted:
1,
11,
111,
2,
...
|
|
|
|
Post by jontara on Jun 11, 2017 19:52:29 GMT
Jon, thanks for the tips. I certainly can create my own field. My point is the casting for ordering is not working. To make it work, I needed to start the counter at 1,000,000 for example. If I start at 1 and go up it still won't sort correctly, because numbers in text will be sorted: 1, 11, 111, 2, ... As a practical matter, you should be able to just order by object directly. (Again, only by observation) object is always an 18-character value, example: 266410488585422.06. The monks will have completed moving the rings on the Towers of Hanoi before that value rolls over to the next decade, making it a 19-character value!  And, so, as a practical matter, you can just sort by object. Caveats: I don't use Rhoconnect. I don't use Property Bag, as it's horribly inefficient. And, so, I don't know if object might be created differently in cases that I do not use. Can you use a Fixed Schema instead? Then you can just use integers. Yes, it can be a bit of a pain because the you have to potentially deal with migrations. Property Bag is convenient when starting to write an app, but it turns what is really quite an efficient and elegant relational database into a sluggish noSQL scheme. If you need to be able to add some arbitrary attributes to records, and you don't need to search on them, consider putting some JSON in one or more attributes. |
|
