QxSqlQuery: User defined columns

[gianks]

Hi,
when building queries with QxSqlQuery how can I use custom/on-the-fly columns?
QxOrm generated prepared statement SQL is wrong when not using just real column names, as in:

qx::QxSqlQuery query;
query.where("start_timestamp + duration")
          .isLessThanOrEqualTo(relative_start);

This leads to the following error:

Unknown column 'duration_1_0' in 'where clause'
[QxOrm] dump sql query bound values : 
  - :start_timestamp :

It seems QxOrm fails to bind the argument start_timestamp and fails to use the whole statement as a single column.

[QxOrm]

Hello,

You can define your own SQL columns aliases, this is documented here :
https://www.qxorm.com/qxorm_en/manual.html#manual_3850

Example :

list_blog lstBlogComplexRelation3;
QStringList relations;
relations << "<blog_alias> { blog_text }";
relations << "author_id <author_alias> { name, birthdate }";
relations << "list_comment <list_comment_alias> { comment_text } -> blog_id <blog_alias_2> -> * <..._my_alias_suffix>";
QSqlError daoError = qx::dao::fetch_all_with_relation(relations, lstBlogComplexRelation3);
qx::dump(lstBlogComplexRelation3);

Then, in your WHERE condition, just use your custom alias.

[gianks]

Hi, sorry for the delay, I was and am a little puzzled by what you suggested, possibly because i am not using relationships here at all so i don't really see how to "declare" a column alias (which is not really a simple alias, but more an operation between two, or more, columns) just for a custom query.

Can you transpose your suggestion within the code I originally posted, using QxSqlQuery?
Thanks a lot.

[QxOrm]

Yes sorry, the aliases are just to force a table alias name for a relation table (for the JOINS), and you can then use your own alias in your SQL query.

But in your case, I think I don't understand yet what is your problem exactly.
You should provide more details :

• what is your C++ code ?
• what is the SQL generated by QxOrm library ?
• what is the expected SQL ?
• Why you don't use directly the column name ?

[gianks]

Hi, sorry for the confusion here.
I guess I will answer the last question first since I think we must start from the objective.

My goal is to use the RDBMS to perform filtering operations, hence the WHERE clause, to retrieve just some specific objects.
It looks like QxOrm works fine with that until all the filtering operations can be defined as a chain of "single real column" conditions.

If you look at my original piece of code, I am trying to use 2 columns ("start_timestamp + duration") in a arithmetic sum and compare the result in the where clause against an arbitrary value, in this case coming from a variable (relative_start).

The expected SQL PS shall look like this in this case:
SELECT * FROM anyOrmTable WHERE start_timestamp + duration <= ?

You don't need to have any alias declared nor to retrieve the on-the-fly column from the database, hence the * works just fine. The sum will be performed within the query executor and discarded after preparing the result set.

QxOrm shall "understand" that a given column (operand I'd say) might be more complex than a single column name and treat it internally as a whole (virtual? temporary?) column for the sake of binding the arguments instead of attempting to use the (first?) natural column appearing in the query creation ("start_timestamp + duration").
Actually here the binding operation shall not be done at all on the left operand as it's just a product of the sum, the right argument is the one that shall be bound, but the output I quoted originally seems in complete contrast with this idea.

Point is: the nice part of SQL is to be able to request the DB to do arbitrary/onetime math (not just JOINS) sometimes before returning any data for the sake of filtering/ordering, possibly disregarding the math itself (not part of the selected columns).

If I were to use such computations client side afterwards, I'd then use something like this:
SELECT *, start_timestamp + duration as end_timestamp FROM anyOrmTable WHERE end_timestamp <= ?
Clearly this might go in contrast with the idea of the ORM and effectively be useless until a way exists to access the extra columns, not persistently mapped to the destination class(es).

Let me know if I was unclear in any way, thanks.

[gianks]

Here you have a use case, the commented code does not work, the following does the same (more or less, not sure anymore, dev ongoing :) and works as expected!

//                        query = qx::QxSqlQuery();
//                        query.where("recording_id")
//                                .isEqualTo((qlonglong)hls_recording->getId())
//                                .and_("start_timestamp")
//                                .isLessThan(recording_ts_offset)
//                                .and_("start_timestamp + duration")
//                                .isGreaterThanOrEqualTo(recording_ts_offset)
//                                .orderAsc("RecordingIndex.start_timestamp");
                        query = qx::QxSqlQuery(
                                "SELECT * FROM RecordingIndex "
                                " WHERE recording_id = :rec_id "
                                " AND start_timestamp <= :rec_offset"
                                " AND start_timestamp + duration > :rec_offset"
                                " ORDER BY start_timestamp ASC");

[QxOrm]

Hello,
Ok I think I'm starting to understand :)

I think the way to do that is to use this method :
QxSqlQuery & QxSqlQuery::freeText(const QString & text, const QVariantList & values = QVariantList());

This method supports placeholders, and as you can see, you can pass the values in the second parameter : const QVariantList & values = QVariantList().
So in your case, it could be :

query = qx::QxSqlQuery();
query.where("recording_id")
        .isEqualTo((qlonglong)hls_recording->getId())
        .and_("start_timestamp")
        .isLessThan(recording_ts_offset)
        .freeText("start_timestamp + duration >= ?", (QVariantList() >> recording_ts_offset))
        .orderAsc("RecordingIndex.start_timestamp");

[gianks]

Hi thanks for the prompt answer, appreciated!

I guess this can be a workaround although it feels a little like an hack, in between the two previously proposed versions. Using the freeText method we leave the predictable/standard way of building queries leading to a sort of mix of techniques (PLAIN SQL vs QueryBuilder). Readability is slightly affected too as it requires more grammar.

I suggest to consider this as a feature request for a next version of QxOrm.
I do understand that the optimizations in place might be impacted by such changes so I thank you anyway for the proposed solution which hopefully won't be needed in a near future :).