Сложные фильтры, язык EQL
Язык EQL (ELMA Query Language) — язык запросов к данным, хранящимся в системе ELMA, который расширяет возможности создания сложных выборок данных, т.е. позволяет создать фильтр даже с такими критериями, которые не заложены в базовом функционале настроек фильтра.
EQL-запрос — это структурированный запрос к данным системы ELMA, позволяющий создавать сложные выборки данных и создаваемый при помощи языка EQL.
В системе у всех фильтров формируется текстовое поле Query, в который можно записать условие выборки. Чтобы проверить условие выборки на корректность и увидеть сразу результат, в версии 3.9+ Вы можете выполнить его в специальном разделе в Администрировании — Система — Фильтрация объектов. Для этого Вам необходимо выбрать тип объекта, выбрать EQL-поиск и написать условие выборки:
Рис. 1. Раздел «Фильтрация объектов»
В данном разделе Вы можете смоделировать правильный EQL-запрос прежде, чем использовать его в сценариях или модулях.
Примечания:
- При сравнении строк учитывается регистр в зависимости от настроек базы данных.
- Название свойств, объектов, функций регистрозависимо.
- Ключевые слова регистронезависимые, например, and, or, empty, not, parent, root, from, select, where, count.
- Операторы регистронезависимые, например, in, is.
- При сравнении дробных чисел необходимо указывать точку между целой и дробной частью.
Синтаксис
1. Операторы сравнения.
1.1. Оператор "равенство" =
Оператор равенство рекомендуется использовать для проверки на точное равенство первого операнда второму. Данный оператор НЕ рекомендуется использовать для дат, так как точное совпадение даты и времени маловероятно. Для сравнения дат рекомендуется использовать операторы неравенства и операторы in, is.
Пример поиска в задачах:
Executor = CurrentUser() — поиск задач, исполнитель которых равен текущему пользователю.
1.2. Оператор "неравенство" <>
Проверка неравенства значений. При сравнении строк учитывается регистр.
Пример поиска в задачах:
Executor <> CurrentUser() — поиск задач, исполнитель которых неравен текущему пользователю.
1.3. Оператор "меньше" <
Оператор строгого неравенства значений, когда значение левого операнда меньше значения правого операнда.
Пример поиска в контрагентах:
AnnualIncome < 2500000 — поиск контрагентов, годовой доход которых меньше 2500000 рублей.
1.4. Оператор "больше" >
Оператор строгого неравенства значений, когда значение левого операнда больше значения правого операнда.
Пример поиска в контрагентах:
AnnualIncome > 2500000 — поиск контрагентов, годовой доход которых больше 2500000 рублей.
1.5. Оператор "меньше, либо равно" <=
Оператор нестрогого неравенства, когда значение левого операнда меньше, либо равно значению правого операнда.
Пример поиска в задачах:
CreationDate <= DateTime(2016, 10, 14) — поиск задач, дата создания которых меньше, либо равна 14.10.2016.
1.6. Оператор "больше, либо равно" >=
Оператор нестрогого неравенства, когда значение левого операнда больше, либо равно значению правого операнда.
Пример поиска в задачах:
CreationDate >= DateTime(2016, 10, 14) — поиск задач, дата создания которых больше, либо равна 14.10.2016.
1.7. Оператор IN
Данный оператор устанавливает соответствие значения указанного поля с каждым из элементов, указанных в скобках после него. Также данный оператор используется при выполнении подзапросов, примеры которых будут ниже.
Пример поиска в задачах:
Priority in (’High’,’Medium’) — поиск задач, приоритет которых является высоким или средним.
Примечание: Если поле является объектом, то значения поля необходимо передавать в качестве идентификаторов, например, Contacts in (1, 2, 3). Если поле является простым типом, то значения можно передавать в виде конкретных значений, например, Name in (’Имя1’, ’Имя2’).
1.8. Оператор IS
Данный оператор используется только в сочетании NULL или EMPTY:
- IS NULL используется для объектов с единичным выбором.
- IS EMPTY используется для объектов с множественным выбором.
Пример поиска в контрагентах:
- Industry IS NULL — поиск контрагентов с незаполненным полем Отрасль;
- Contacts IS EMPTY — поиск контрагентов c пустым списком контактов.
1.9. Оператор LIKE
Данный оператор используется для сравнения только строковых переменных. Оператор сравнивает указанное строковое поле с маской (шаблоном) и, если условие выполнится, объект попадет в условие выборки. Оператор LIKE рекомендуется использовать с маской (шаблоном), в противном случае данный оператор будет равносилен равенству.
Маска (шаблон):
- % — обозначает любое количество произвольных символов.
Пример поиска в задачах:
Subject like ’%Тема%’ — поиск задач, тема которых содержит слово "Тема" и вокруг данного слова могут быть любые символы. - _ — обозначает один произвольный символ в строке. Может использоваться несколько раз для обозначения, например, двух и более символов.
Пример поиска в задачах:
Subject like ’_ема%’ — поиск задач, в теме которых первый символ может быть произвольным, затем используется слово "ема", а следующие символы могут быть произвольными.
Аналогично можно использовать несколького символов, например, Subject like ’__ма%’. - ‘ — обозначает кавычки. Яркий пример: в названии задачи используются кавычки и необходимо найти необходимую задачу(и) с использованием кавычек в названии. Для использования кавычек в запросе они должны быть продублированы, то есть это эквивалентно экранированию кавычек.
Пример поиска в задачах:
Subject like ’%’’ООО контрагент’’%’ — поиск задач, в теме которых используется строка ’ООО контрагент’.
2. Логические операторы
2.1. AND — логическое И.
При выполнении запроса должны быть выполнены оба подусловия, которые соединяет данный логический оператор.
Пример поиска в задачах:
Subject like ’%Тема%’ AND Executor = CurrentUser() — поиск задач, в теме которых используется слово "Тема" и исполнителем этих задач является текущий пользователь.
2.2. OR — логическое ИЛИ.
При выполнении запроса должно выполнится одно из подусловий, которые соединяет данный логический оператор.
Пример поиска в задачах:
Subject like ’%Тема%’ OR Subject like ’%задача%’ — поиск задач, в теме которых используется слово "Тема" или "задача".
2.3. NOT — логическое НЕ. Не рекомендуется использовать, если затрагиваемый объём данных более 1000 строк.
При выполнении запроса не должно выполнятся подусловие.
Пример поиска в задачах:
NOT Subject like ’%Тема%’ — поиск задач, в теме которых НЕТ слова "тема".
3. Управление приоритетом
Для обозначения приоритета среди выражений в сложных запросах используются круглые скобки.
Пример поиска в контрагентах:
(Name LIKE ’%ООО%’ OR Name LIKE ’%ЗАО%’) AND (Region = 6) — поиск контрагентов, у которых регион с идентификатором 6 и в названии которых содержатся слова "ООО" или "ЗАО".
Вместе с оператором OR использованы круглые скобки, для того, чтобы обозначить его приоритет перед оператором AND.
4. Подзапросы
Подзапрос — это EQL-запрос, вложенный в другой EQL-запрос (являющийся составной частью другого EQL-запроса). Сложность запроса ограничена по уровню вложенности операторов в 1000 уровней. Подзапросы необходимо заключать в круглые скобки.
Подзапросы позволяют фильтровать корневые объекты по свойствам связанных объектов.
Подзапросы могут строится одним из двух правил:
FROM entity SELECT property WHERE expression
SELECT property FROM entity WHERE expression,
- где entity — сущность, с которой работает подзапрос;
- property — поле из текущей сущности;
- expression — выражение для фильтрации записей сущности, так же может представлять из себя вложенный запрос.
Пример поиска в контактах:
Contractor in (from Sale select Contractor where Contractor = PARENT.Contractor and SaleStatus in (Enum(’Postponed’))) and Contractor in (Responsible = CurrentUser()) — поиск контактов, с отложенными сделками для текущего пользователя.
Существует упрощенный вариант записи подзапросов.
Пример поиска в контрагентах:
Contacts in (Surname = ’Иванов’) — поиск контрагентов, у которых есть контакты с фамилией Иванов.
PARENT — обращение к свойствам родительского запроса.
Пример поиска в контактах:
Contractor in (from Sale select Contractor where Contractor = PARENT.Contractor) — поиск контактов, у которых контрагент является контрагентом в сделках.
ROOT — обращение к свойствам корневого запроса.
Пример поиска в задачах:
Contractor in (FROM Contact SELECT Contractor WHERE Name = PARENT.Subject AND Contractor in (FROM Sale SELECT Contractor WHERE Name = ROOT.Subject)) — поиск задач, у которых название сделки совпадает с названием задачи
Важное замечание: если название свойства совпадает с зарезервированным словом (регистр не важен), тогда данное свойство необходимо писать в квадратных скобках. [Parent] = 1.
5. COUNT() — функция для построения сложных подзапросов, результатом которой является целое число.
Правила построения функции:
5.1. COUNT(property), где property — поле типа список.
Пример поиска в контрагентах:
COUNT(Contacts) > 0 — поиск контрагентов, у которых есть контакты.
5.2. COUNT(query), где query — подзапрос, построенный последующему правилу:
FROM entity WHERE expression, где
- entity — сущность, с которой работает подзапрос;
- expression — выражение для фильтрации записей сущности, так же может представлять из себя вложенный запрос.
Пример поиска в контрагентах:
COUNT(FROM Contact WHERE NOT Skype IS NULL AND Contractor = PARENT.Id) >= 1 — поиск контрагентов, количество контактов которых не менее 1, при условии, что у поле Skype контактов заполнено и контрагент контакта совпадает с искомым.
5.3. COUNT(function), где function — QueryInFunction функция, возвращающая в качестве результата список.
Пример поиска:
COUNT(GroupUsers(1)) — количество пользователей группы с идентификатором 1.
6. Функции
Функции бывают двух видов:
- QueryFunctionResult — функция для получения конкретного значения;
- QueryInFunction — функция получения множества значений, используется после операции in.
6.1. CurrentUser() — (QueryFunctionResult) функция получения текущего пользователя. Преобразует в запросе пользователя в идентификатор пользователя, то есть в целое число.
Пример поиска в задачах:
Executor = CurrentUser() — поиск задач, исполнитель которых – текущий пользователь.
6.2. Enum(’название значение перечисления’) — (QueryFunctionResult) функция получения перечисления.
Пример поиска в задачах:
Priority = Enum(’Low’) — поиск задач, приоритет которых "низкий".
Перечисление можно получить более простым способом: просто передать значение в кавычках.
Пример поиска в задачах:
Priority = ’Low’ — поиск задач, приоритет которых "низкий".
6.3. DateTime(год, месяц, день, [час], [минута], [секунда], [миллисекунда]) — (QueryFunctionResult) функция получения даты. В квадратных скобках указаны необязательные параметры.
Пример поиска в задачах:
CreationDate <= (DateTime(2016, 10, 17, 13, 30)) — поиск задач, в которых дата создания меньше, либо равна 17 октября 2016 года 13 часов 30 минут.
В данной функции можно использовать следующие параметры:
- Now’ — для получения текущей даты с точностью до секунды.
Пример: CreationDate <= DateTime(’Now’); - 2) ’Today’ — для получения текущей даты (только дата без времени).
Пример: CreationDate <= DateTime(’Today’); - ’MinValue’ — для получения минимальной даты;
- ’MaxValue’ — для получения максимальной даты.
6.4. RelativeDateTime(’относительная дата начала периода’, ’относительная дата конца периода’) — (QueryInFunction) получение интервала времени между относительными датами. Один из параметров функции может быть пустым. Если первый параметр будет пустым, тогда ограничение будет действовать только до второго параметра, если второй параметр пустой, тогда интервал будет взят от значения первого параметр. Подробнее про строковое выражение относительной даты можно узнать в справке.
Пример поиска в задачах:
CreationDate in RelativeDateTime(’-1м’, ’-1м’) — поиск задач, дата создания которых была в предыдущем месяце.
6.5. Guid(’уникальный идентификатор в строковом представлении’) — (QueryFunctionResult) функция получения уникального идентификатора.
Пример поиска в задачах:
Uid = Guid(’EE90CE1D-0591-466B-87FF-06C4D4282EB1’) — поиск задач, уникальный идентификатор которых равен EE90CE1D-0591-466B-87FF-06C4D4282EB1.
Задать Guid можено более простым способом: просто передать значение в кавычках.
Пример поиска в задачах:
Uid = ’EE90CE1D-0591-466B-87FF-06C4D4282EB1’.
6.6. TypeReference(’имя класса объекта’) — (QueryFunctionResult) ссылка на тип объекта. Имя класса объекта можно узнать в карточке объекта в Дизайнере.
Пример поиска в задачах:
TypeUid = TypeReference(’SMARTTask’) — поиск задач, тип которых является "SMART-задачей".
Задать тип объекта можно более простым способом: просто передать значение в кавычках.
Пример поиска в задачах:
TypeUid = ’SMARTTask’.
Стоит отметить, что задать тип задачи можно и при помощи Guid:
Пример поиска в задачах:
TypeUid = ’7EC44AA8-CB49-4CEF-882A-D4752608B663’.
TypeUid = Guid(’7EC44AA8-CB49-4CEF-882A-D4752608B663’).
6.7. SubTypes(’имя класса объекта’) — (QueryInFunction) ссылки на типы объектов и их наследников. Имя класса объекта можно узнать в карточке объекта в Дизайнере.
Пример поиска в задачах:
TypeUid in SubTypes(’Task’) — поиск задач, тип которых Задача и их наследники, например, SMARTTask, KpiTask.
6.8. Reference(идентификатор типа объекта, идентификатор объекта) — (QueryFunctionResult) значение объекта. В качестве первого параметра может выступать Guid или имя класса объекта. Различные способы указания класса объекта или гуида описаны в пункте 6.6 TypeReference.
Пример поиска в вопросах:
RefObject = Reference(’298b2c71-619f-463c-95b2-8e029085680d’, 204) — поиск вопросов, которые заданы в типе объекта ’298b2c71-619f-463c-95b2-8e029085680d’ (задача) с идентификатором этого объекта 204. Аналогичной записью будет запись: RefObject = Reference(’Task’, 204).
6.9. DropDownItem (’значение выпадающего списка’) — (QueryFunctionResult) значение выпадающего списка. В качестве параметра необходимо указать одно из значений выпадающего списка, указанных при создании свойства.
Важное замечание: значение выпадающего списка должно быть идентично значению в базе данных! Например, если в базе данных значение указано с ключом, то в поиске необходимо передавать не только значение, но и ключ.
Пример поиска в объекте:
Spisok = DropDownItem(’значение1’) — поиск объектов, значение свойства Spisok которых является "значение1".
Задать значение выпадающего списка можно более простым способом: просто передавать значение в кавычках.
Пример поиска в объекте:
Spisok = ’значение1’.
6.10. WorkTime(часов, минут) — (QueryFunctionResult) интервал рабочего времени.
Пример поиска в объекте:
Interval > WorkTime(1, 0) — поиск объектов, в которых указан интервал более 1 часа.
6.11. CurrentUserGroups() — (QueryInFunction) возвращает группы текущего пользователя.
6.12. GetChiefByUser(идентификатор пользователя) — (QueryInFunction) поиск всех руководителей пользователя согласно оргструктуре.
Пример поиска в контрагентах:
Responsible in GetChiefByUser(1) — поиск всех контрагентов, ответственными за которых являются вышестоящие руководители пользователя с Id=1.
6.13. GetUserSubordinate(идентификатор пользователя) — (QueryInFunction) поиск всех подчиненных пользователей согласно оргструктуре.
Пример поиска в контрагентах:
Responsible in GetUserSubordinate(101) — поиск всех контрагентов, ответственными за которых являются подчиненные пользователя с Id=101.
6.14. GroupUsers(идентификатор группы) — (QueryInFunction) поиск всех участников группы пользователей.
Пример поиска в контрагентах:
Responsible in GroupUsers(109) — поиск всех контрагентов, ответственными за которых являются участники группы с Id=109.
6.15. UserGroups(идентификатор пользователя) — (QueryInFunction) поиск всех групп пользователей, участником которых является пользователь, Id которого указан в функции.
Пример поиска в контрагентах:
Id in UserGroups(102) — поиск всех групп пользователей, участником которых является пользователь с Id=102.
6.16. UserOrganisationItems(идентификатор пользователя) — (QueryInFunction) поиск пользователей согласно должностям оргструктуры.
Пример поиска в контрагентах:
CreationAuthor in (FROM User SELECT Id WHERE OrganizationItems in UserOrganisationItems(1)) — поиск всех контрагентов, автором создания которых является пользователь определенной должности (с Id=1).
7. Параметры (использование параметров доступно в веб-приложении, начиная с версии 4.1.0)
Параметры — переменные, значения которых определяются в ходе выполнения сценария/процесса. Использование параметров повышает гибкость EQL-запросов, позволяя использовать один запрос для получения результатов изменяемой переменной.
Параметры указываются в EQL запросе после символа @. Именем параметра может быть любая строка, содержащая латинский или русские символы, цифры, а также знак "_". Следует отметить, что имя параметра должно начинаться с буквы.
@ParameterName, где ParameterName — имя параметра.
Для корректного использования параметра необходимо:
- Сформировать EQL-запрос с использованием параметра и допустимых функций и/или операторов.
- Сопоставить имя параметра и значение, которое он должен принимать. В качестве значения могут выступать переменные, свойства контекста, постоянные значения и пр.
Пример использования:
filter.Query = "Contact in (@Contact)"; — EQL запрос с использованием параметра. Поиск контактов, записанных в параметре Contact.
filter.QueryParameters.Set("@Contact", context.Kontakty); — указание на переменную, значение которой будет записано в параметр Contact.
Вы можете использовать списковые параметры (переменные с типом связи Список), например:
"Contacts in @[Contacts]" — поиск любого контакта из выбранных в списке контактов параметра Contacts.
Пример использования в веб-приложении:
Name = @name and Contacts in @[Contacts], где @name и @Сontacts — параметры запроса.
После ввода параметров в поле запроса под ним отобразятся поля для ввода значений параметров.
Пример использования EQL в PublicAPI:
- Создать фильтр-билдер и передать в Query необходимый EQL-запрос.
Пример поиска в контрагентах:
PublicAPI.CRM.Contractor.Filter().Query("Id in (1, 2)").Find(); — поиск контрагентов, идентификаторы которых являются 1 или 2. - Передать EQL в фильтр.
Пример поиска в контрагентах:
PublicAPI.CRM.Contractor.Find("Id in (1, 2)"); — поиск контрагентов, идентификаторы которых являются 1 или 2.