SQL definition of the query

There can be all the properties of the used database used in the SQL definition of an query with several modifications.

• tables have to be named
• clause ORDER BY cannot be used - query is inserted into another query for ordering and paging needs

Classes used in the query can be referenced directly (DataRow a ID of the given class). This notation is, however, not portable to another ObjectGears system instances and not user friendly. Therefore, use the second notation:

select * from {{: class.__class_code__ :}} tab
select * from {{: query.__query_code__ :}} query
select * from {{: model.__model_code__.class.__class_code__ :}} tab

The first example uses class from the same model the query belongs to The second example uses class query from the same model. The third example enables to use class from another model.

The used class or query needs to be explicitly named (in the example names tab and query are used), otherwise an error is returned when attempting to save the query.

You can also use in the query below stated commands. They are inserting current ID of the object. Therefore, you do not need to use particular ID of one instance which would cause issues with portability. Do not use commands for assembling names of used tables but e.g. for returning ID as part of data.

{{: model.__model_code__.id :}}
{{: class.__class_code__.id :}}
{{: query.__query_code__.id :}}

{{: class.__kod__.column.__kod_sloupce__.id :}}
{{: query.__kod__.column.__kod_sloupce__.id :}}

select id, {{: class.task.id :}} parentId
from {{: class.task :}} u

Query parametrization

You can use commands in the query, that are replaced with current value at the time of the query execution. Query can be in this way adapted to current context. Commands are included in double braces, without colons.

Following examples read records from the class "action", where value in the column "adminid" or "adminname" equals current logged in user. The command result value is text. Therefore, it has to be enclosed in apostrophes.

select * from {{: class.action :}} a where adminid = {{ LoginUser.Id }}
select * from {{: class.action :}} a where adminname = '{{ LoginUser.FullName }}'

Command	Description
LoginUser.Id	Command inserts ID of current logged in user.
LoginUser.FullName	Command inserts full name of current logged in user.
LoginUser.Account	Command inserts account of current logged in user.
LoginUser.Code	Command inserts code of current logged in user.
LoginUser.Email	Command inserts  email of current logged in user.

Return value of the command LoginUser.Id is an integer, while other commands LoginUser... return text. Therefore, it is necessary to encapsulate these commands into apostrophes. Otherwise SQL command execution leads to an error.

You can use a query with parameters on web or for web services. However, you cannot create a database view for it by means of function Creation of API view. The reason for that is information about current user that is missing in the database.

Query with WITH

You can use also WITH clause in the query. You have to insert this "subquery" into another auxliary query and the refer to this query.

This means that you cannot use a recursive call in the query. If you need that, use stored procedure as a data source for query. 

The reason for this behaviour is encapsulating your query with another query that takes care of sorting, paging and correct column names, that is added by ObjectGears system. You can use clause WITH, only if you use also clause /*ENDWITH*/.

Example of use:

WITH ArticleTopic(first_article_id, article_id, parent_article_id, topic_id, Level)
AS
(
  ...
)
/*ENDWITH*/
select ...
from ...

Insert the clause /*ENDWITH*/ immediately after WITH and before SELECT.