ObjectGears Help

           
Forum   ObjectGears (Version: 1.6.0.0)

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 :}}

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 cannot use directly sql clause WITH in a query. However, you can insert this "subquery" into another auxiliary query and call this one in a repeated way.

You also cannot use recursive call in query. If you need it, use a stored procedure as data source.

This website is using cookies files to provide services and analyse visits. You agree with that by using this website.     Further information