Show/Hide Toolbars

TMS Aurelius Documentation

Specifies a SQL expression to be added the WHERE clause of the final SQL used to retrieve entities or the items of a many-valued association.

 

Level: Class or Field/Property Attribute

 

Description

Use Where attribute to define additional filter (SQL expression) to the final SQL used to retrieve a specified entity, a list of entities or items of a many-valued association.

 

Just like in the query SQL condition, be aware that the SQL clause will be just injected in the SQL statement, you must be sure it will work. You can also use property names between curly brackets. Write the name of the property inside curly brackets and Aurelius will translate it into the proper alias.fieldname format according to the context. For example, "{Deleted} = 'F'".

 

if you apply the attribute to a field/property, it must be a many-valued association, and the WHERE clause will only apply for that list.

 

If you apply the attribute to a class, it will apply to any situation where entities of that class are retrieved. When you find a single entity, or when you query entities of that class, the filter will be applied. Even if the entity is an association (many-to-one) of a parent entity, the filter will be applied. For example, suppose a TInvoice class has a Customer property of type TCustomer. If TCustomer entity has a [Where] attribute it will be applied when retrieving the Customer of that TInvoice instance. Even if the associated customer exists in the database, if it's filtered out by the WHERE clause, the TInvoice.Customer property will come as nil value.

 

You can add multiple Where attributes in same class or property. They will all be combined with the AND operator.

 

Constructor

constructor Create(const ASqlClause: string);

 

Parameters

 

ASqlClause

The SQL expression that will be added to the WHERE clause to filter the entity or many-valued association.

 

 

Usage

 

TCustomer entities will not be retrieved if the Deleted field is equal to T.

 

  [Entity, Automapping]
  [Where('{Deleted} <> ''T''')]
  TCustomer = class
  private
    FId: integer;
    FName: string;

 

 

The FNewCustomers list will only bring TCustomer objects if the Status field is equal to New. Note that the Where clause above of the TCustomer entity will still apply, meaning the Status must be "New" and Deleted must not be "T".

 

TParent = class
private
  [ManyValuedAssociation([], CascadeTypeAll)]
  [Where('{Status} = ''New''')]
  FNewCustomers: TList<TCustomer>;