[odb-users] ORDER BY clause in ODB query language

[Markus Klemm]

Is there a way to get a query with an ORDER BY clause with the typed checked, static, non-native ODB query language?
The examples in the manual is using the database system-native query language e.g. a concatenated string. That also makes it hard to use the ORDER BY clause because after the odb macro for the column, I need to concatenate the DESC/ASC clause but can’t due type mismatch.

Regards/ Mit freundlichen Grüßen

Markus Klemm

[Boris Kolpackov]

Hi Markus,

Post by Markus Klemm
Is there a way to get a query with an ORDER BY clause with the typed
checked, static, non-native ODB query language? The examples in the
manual is using the database system-native query language e.g. a
concatenated string.

Currently you have to spell "ORDER BY" as a string, though the column
can be specified as a C++ name:

db->query<person> ((query::first == "John") + "ORDER BY" + query::age);

We do plan to provide some syntactic sugar for this, though I am not
sure how much static type checking we will be able to do. For example,
should we assume if the C++ type provides operator<, then we can do
ORDER BY?

Post by Markus Klemm
That also makes it hard to use the ORDER BY clause because after
the odb macro for the column, I need to concatenate the DESC/ASC
clause but can’t due type mismatch.

This should work:

db->query<person> ((...) + "ORDER BY" + query::age + "ASC");

BTW, "the odb macro for the column" is not a macro, it is a proper
C++ variable name.

Boris

[Markus Klemm]

I was close to start a new thread, but it basicly the same topic:
My point in the previous question was, that I can't solve this use case below, without choosing the column by a string, rather than by a odb::query_column,yet?!

I didn't minimized it completly because @Boris was wondering about the use cases. So obvouisly I'm not a fan of the one suggested future solution of using operator<, because ordering criteria changes/ depens on the column. This is the real usecase when using the framework Wt (e.g. part of the user supplied model for a table view).

Raw Sourcecode below, gist here: https://gist.github.com/Superlokkus/a994d42534beca9ed5c6
void sort(int column, Wt::SortOrder order = Wt::AscendingOrder){
odb::query<directory> first_part("ORDER BY"), middle_part,end_part;
//odb::query_column<directory> middle_type; and decltype(odb::query<directory>::added) middle_type; were my next guesses
switch (column){
default:
middle_part = odb::query<directory>::added;/*Error: no operator "=" matches these operands
operand types are : odb::query<directory, odb::mssql::query_base> = const odb::mssql::query_column<boost::posix_time::ptime, odb::mssql::id_datetime>*/
break;
}
switch (order){
case Wt::DescendingOrder:
end_part = odb::query<directory>("DESC");
break;
default:
end_part = odb::query<directory>("ASC");
break;
}
odb::query<directory> final_query(first_part + middle_part + end_part);
}

Best Regards

Markus Klemm

Post by Boris Kolpackov
Hi Markus,

Post by Markus Klemm
Is there a way to get a query with an ORDER BY clause with the typed
checked, static, non-native ODB query language? The examples in the
manual is using the database system-native query language e.g. a
concatenated string.

Currently you have to spell "ORDER BY" as a string, though the column
db->query<person> ((query::first == "John") + "ORDER BY" + query::age);
We do plan to provide some syntactic sugar for this, though I am not
sure how much static type checking we will be able to do. For example,
should we assume if the C++ type provides operator<, then we can do
ORDER BY?

Post by Markus Klemm
That also makes it hard to use the ORDER BY clause because after
the odb macro for the column, I need to concatenate the DESC/ASC
clause but can’t due type mismatch.

db->query<person> ((...) + "ORDER BY" + query::age + "ASC");
BTW, "the odb macro for the column" is not a macro, it is a proper
C++ variable name.
Boris

[Boris Kolpackov]

Hi Markus,

Raw Sourcecode below [...]

Wouldn't something like this work:

using dir_query = odb::query<directory>;

dir_query q ("ORDER BY");

switch (column)
{
case added:
q += dir_query::added;
...
}

switch (order)
{
case desc:
q += "DESC";
...
}

db.query (q);

Boris

[Markus Klemm]

Hi Boris,

I didn't use the += operator, because it causes compiler errors for at least the wstring members. Surprisingly the bit type works.
I updated the according gist ( https://gist.github.com/Superlokkus/a994d42534beca9ed5c6 ), added the erros to the relevant lines and also added the object definition.

But as always, already a big thank you, for your efforts and this still great library.

Regards

Markus Klemm

Post by Boris Kolpackov
Hi Markus,

Raw Sourcecode below [...]

using dir_query = odb::query<directory>;
dir_query q ("ORDER BY");
switch (column)
{
q += dir_query::added;
...
}
switch (order)
{
q += "DESC";
...
}
db.query (q);
Boris

[Boris Kolpackov]

Hi Markus,

Post by Markus Klemm
I didn't use the += operator, because it causes compiler errors

I've added the missing operator. Can you apply this patch and let
me know if there are any issues remaining:

http://scm.codesynthesis.com/?p=odb/libodb-mssql.git;a=commit;h=2d228c59fc6bd96944f91912c2b174cc63f56aab

Thanks,
Boris

[Markus Klemm]

I'd love to but:

in contrast to the 2.4.0 package, opening the project with visual studio 2013 express does not work anymore (output below), but because my shift just ends in a couple of minutes I applied the patch on the used package, e.g. used header, manually instead.

But I'm not sure if it's due that I just changed the header or if there is a problem, it didn't work. But I wanted to give a 'quick' response:

[Microsoft][SQL Server Native Client 11.0][SQL Server]Falsche Syntax in der Nõhe von ')'.
8180 (42000): [Microsoft][SQL Server Native Client 11.0][SQL Server]Anweisung(en
) konnte(n) nicht vorbereitet werden."

Translation: Wrong syntax around ')'... query could not be perpared. (I'm sorry for the german error message, microsoft ;-( )

Visual Studio 2013 express error:

C:\Users\klm\Downloads\libodb-mssql\odb\mssql\libodb-mssql-vc12.vcxproj : error : Unable to read the project file "libodb-mssql-vc12.vcxproj".
C:\Users\klm\Downloads\libodb-mssql\odb\mssql\libodb-mssql-vc12.vcxproj(173,3): The element <#text> beneath element <ItemGroup> is unrecognized.

Regards/ Mit freundlichen Grüßen

Markus Klemm

Gesendet via Mobiltelefon

Post by Boris Kolpackov
Hi Markus,

Post by Markus Klemm
I didn't use the += operator, because it causes compiler errors

I've added the missing operator. Can you apply this patch and let
http://scm.codesynthesis.com/?p=odb/libodb-mssql.git;a=commit;h=2d228c59fc6bd96944f91912c2b174cc63f56aab
Thanks,
Boris

[Boris Kolpackov]

Hi Markus,

Post by Markus Klemm
in contrast to the 2.4.0 package, opening the project with visual
studio 2013 express does not work anymore...

By applying the patch I meant applying the changes from this specific
commit to what you are currently using. Getting the whole library
from master is not a good idea for multiple reasons.

Post by Markus Klemm
Translation: Wrong syntax around ')'... query could not be perpared.

Ok, so your application now compiles fine but you get this error at
runtime. Can you enable statement tracing for the affected transaction
so that we can see the query that causes this error:

t.tracer (odb::stderr_tracer);

Boris

[Markus Klemm]

Alright I checked out tag 2.4.0 (which differs from the downloadable 2.4.0 package btw) and cherrypicked your commit.

It broke where I want to filter the files by the directories (1:n bidirect.) and still want a particular ordering of the files.

const std::vector<decltype(directory::directory_id)> &directories
odb::query<file> filter_by_directory_query = odb::query<file>::directory->directory_id.in_range(directories.cbegin(), directories.cend());
odb::query<file> order_by_query("ORDER BY" + odb::query<file>::read_time + "ASC");
odb::query<file> final_query = filter_by_directory_query + order_by_query;

I'm sorry in advance because I guess I did something wrong/unelegant again.

Resulting SQL Query:
SELECT [files].[file_id], [files].[file_name], [files].[directory_id], [files].[
fully_uploaded], [files].[partially_uploaded], [files].[read_time], [files].[upl
oader_log] FROM [files] LEFT JOIN [directories] AS [directory_id] ON [directory_
id].[directory_id]=[files].[directory_id] WHERE [directory_id].[directory_id] IN
() ORDER BY [files].[read_time] ASC

Other querys looked fine:
SELECT [directories].[directory_id], [directories].[full_path], [directories].[f
ile_type], [directories].[active], [directories].[added], [directories].[added_b
y] FROM [directories] ORDER BY [directories].[full_path] ASC
SELECT [file_types].[type_id], [file_types].[type_description], [file_types].[md
db_importer_flag], [file_types].[search_string], [file_types].[odbc_connection_t
emplate] FROM [file_types] WHERE [file_types].[type_id]=?
SELECT [directories].[directory_id], [directories].[full_path], [directories].[f
ile_type], [directories].[active], [directories].[added], [directories].[added_b
y] FROM [directories] ORDER BY [directories].[added] ASC

SELECT [files].[file_id], [files].[file_name], [files].[directory_id], [files].[
fully_uploaded], [files].[partially_uploaded], [files].[read_time], [files].[upl
oader_log] FROM [files] WHERE [files].[file_id]=?

Regards

Markus Klemm

[Markus Klemm]

I'm sorry this bug is not causes by your fix, it judt slipped my tests before.

Mit freundlichen Grüßen

Markus Klemm

Gesendet via Mobiltelefon

Post by Markus Klemm
Alright I checked out tag 2.4.0 (which differs from the downloadable 2.4.0 package btw) and cherrypicked your commit.
It broke where I want to filter the files by the directories (1:n bidirect.) and still want a particular ordering of the files.
const std::vector<decltype(directory::directory_id)> &directories
odb::query<file> filter_by_directory_query = odb::query<file>::directory->directory_id.in_range(directories.cbegin(), directories.cend());
odb::query<file> order_by_query("ORDER BY" + odb::query<file>::read_time + "ASC");
odb::query<file> final_query = filter_by_directory_query + order_by_query;
I'm sorry in advance because I guess I did something wrong/unelegant again.
SELECT [files].[file_id], [files].[file_name], [files].[directory_id], [files].[
fully_uploaded], [files].[partially_uploaded], [files].[read_time], [files].[upl
oader_log] FROM [files] LEFT JOIN [directories] AS [directory_id] ON [directory_
id].[directory_id]=[files].[directory_id] WHERE [directory_id].[directory_id] IN
() ORDER BY [files].[read_time] ASC
SELECT [directories].[directory_id], [directories].[full_path], [directories].[f
ile_type], [directories].[active], [directories].[added], [directories].[added_b
y] FROM [directories] ORDER BY [directories].[full_path] ASC
SELECT [file_types].[type_id], [file_types].[type_description], [file_types].[md
db_importer_flag], [file_types].[search_string], [file_types].[odbc_connection_t
emplate] FROM [file_types] WHERE [file_types].[type_id]=?
SELECT [directories].[directory_id], [directories].[full_path], [directories].[f
ile_type], [directories].[active], [directories].[added], [directories].[added_b
y] FROM [directories] ORDER BY [directories].[added] ASC
SELECT [files].[file_id], [files].[file_name], [files].[directory_id], [files].[
fully_uploaded], [files].[partially_uploaded], [files].[read_time], [files].[upl
oader_log] FROM [files] WHERE [files].[file_id]=?
Regards
Markus Klemm