SELECT Expression for column derived from other column(s)


#1

[Android CBL 2.0]
Suppose I have a document property “InStock”, and another “CostPrice”.
I have found that to create a new column in the query, “TotalCost”, simply create two property expressions and multiply them:
Expression inStockExpression = Expression.property(“InStock”);
Expression costPriceExpression = Expression.property(“CostPrice”);
Then simply return new Expression from inStockExpression.multiply(costPriceExpression)
This works nice for all arithmetic operations between the two existing NUMBER properties.
Ok. So what’s the question?
Two questions.

  1. If property #1 is a date, like “DatePurchased” and property #2 is another date, “DateSold”, and I want a new derived column, an Integer, for the number of days between “DateSold” and “DatePurchased”, how do I do that? (I want a number based on computing two dates.)
    1B. If I simply want to add an Integer, say 30 days, to “DatePurchased”, and the new derived column should be a DATE, how do I do that?

  2. How do I manually evaluate property expressions? Like the inStockExpression above. I am looking for something like Expression.intValue(Expression). Having the answer to this will allow me accomplish date manipulations manually, to solve question 1 and 1b, etc.

This has stumped me quite a bit, and I feel like the answer is glaring me in the face and am too dumb to see it.
Thank you much!
-nat


#2

DateTime functions at the query level are something that was cut out due to time constraints. They are not currently planned for a specific release but if this is something that is important to you then let it be known. That will help get things prioritized (cc @priya.rajagopal )


#3

Ok. I guess @priya.rajagopal now has the official request. However, if I get the answer to #2 in my post, I can ‘manually’ solve the problem.

Thanks.
:grinning::grinning::grinning:Happy Birthday!:grinning::grinning::grinning:
nat


#4

In case I wasn’t clear… (in #2)
If I have: SelectResult.expression(Expression.property(“AnyProperty”)),
how can I get that property value in code. Suppose I know it’s an int, how do I get the intValue of the property? Something like Expression.intValue(SelectResult.expression(Expression.property(“AnyProperty”)))
Thanks
nat


#5

That is not something that is possible to do. It’s hard to explain because everything gets so meta, but if you’ve had any experience with writing a LINQ provider you would find yourself in a very similar situation in that the values that you deal with at this level are not actual values, but potential values. You won’t know what it is until it is materialized.

That being said, in your first snippet you already have the “potential value” since those are untyped. They won’t gain an actual type until they are materialized. Until you get the result set you are limited to expression based functions in what you can do with the potential value. It’s not a complete set of N1QL functionality, but a large swath of it. I suppose I need to ask “what do you want to do with that int value?” to know the proper answer to your question though.


#6

I am trying to workaround the fact that I don’t have DateTime functions. (This is why this question #2, followed my DateTime questions.
In my follow up question I just used int value (as an example) to simplify the question. But I really want to use it for dates. So if I can get a date from Expression._date_Value(SelectResult.expression(Expression.property(“DateTimeProperty”))), I can then do any DateTime manipulations, be it between two date columns, or a date and integer column (int representing num of days) and then use a new Expression, containing the sum of the others (whether a date or int) as the actual SelectResult.expression for the column.

Thanks;
nat


#7

SelectResult.Expression is already a value, so putting a further value function on it is redundant. To be a bit more clear you can either use the provided functions and operate on the value before it is materialized, or you have to materialize the value and then operate on it post query. I’m not quite clear on what you mean by this:

How do you plan to do that if you achieved what you wanted to try?


#8

If I could evaluate:
Expression.property(“DateTimeProperty_1”) into say, date_1, and
Expression.property(“DateTimeProperty_2”) into date_2,
I would use plain Java to do anything needed between these dates, then with the new date or int (depending what I need for that column), say date_3, I would use SelectResult.expression(Expression.date(date_3)) for the actual query column.


#9

You can’t put these into normal Java variables the way you are thinking. Remember that the entire expression tree must be evaluated before you have an actual concrete value. Any Java based semantics necessarily come after the result has been materialized because the query is carried out at the LiteCore level with C instructions. Java code has no context there to execute. That is why in order to do what you are doing, the date time functions need to be implemented so that LiteCore knows how to deal with them before materialization.

I suggest another workaround: store timestamps instead of dates. Those are just integers anyway, right? You can operate on them as normal numbers and it isn’t very hard to convert them into actual datetimes.


#10

Storing DateTime as numbers (Longs in Java) sounds good, but then the materialized post query result would be a number and not a date!

Can I accomplish what I want POST Query, with 2 date columns, using “$1$” , “$2$”, to create a new column?


#11

You can’t create a column out of other columns, but you can certainly extract the dates out of the result set and operate on them however you wish. The result set entries have the same read interface as a document so if you selected those two properties you could get the dateTime out with result.getDate(0) / result.getDate(1)


#12

Can I also do ‘result.getDate(“ColumnName”)’ ?
Sounds like a good solution.
-nat
[ps. I am in the Eastern usa time zone. fwiw.]


#13

@borrrden

To answer my follow-up question: Yes!
So yes, your solution of result.getDate(int index) as well as result.getDate(“Column_Name”) comes pretty close to what I wanted to do. (The only drawback being, that I need to select the columns I need for the calculations, even if I don’t need them in the query.)
But all in all, a good solution!
Thanks.
-nat


#14

Right, you can also select by column name as you’ve found out. I just put in the index based API arbitrarily because sometimes the column name is unknown (but it’s always known for a simple property lookup).