PostgreSQL
exists
7.15
Sales_Creatio_enterprise_edition
Код формирования подзапроса с exists
Фёдоров Александр Сергеевич
9 апреля 2020 16:50

Коллеги, приветствую.

 

Помогите, пожалуйста, с формированием кода запроса.

Я формирую таблицу, в столбец (логического типа) которой подзапросом вывожу 1 или 0 в зависимости от наличия записи в другой таблице: в дополнение к Продаже вывожу столбец, в котором отмечаю была ли эта Продажа на стадии, например, Контрактование.

При этом нужно именно значение 1 или 0.

 

Нашёл 2 решения в виде sql-запроса:

1. select "O"."Id", "O"."Title"

, (select exists(select 1 from "OpportunityInStage" AS "OinS" where "OinS"."OpportunityId" = "O"."Id" and "OinS"."StageId" = 'fb563df2-5ae6-df11-971b-001d60e938c6')) AS "Contracting"

from "Opportunity" AS "O"

2. либо через count(*) делённый сам на себя

1. select "O"."Id", "O"."Title"

, coalesce(select count(*) / count(*) as "cnt" from "OpportunityInStage" AS "OinS" where "OinS"."OpportunityId" = "O"."Id" and "OinS"."StageId" = 'fb563df2-5ae6-df11-971b-001d60e938c6') AS "Contracting"

from "Opportunity" AS "O"

 

Я закодировал подзапрос с одним count таким образом

var stagePresentSubSelect = new Select(userConnection).Count("*").As("cnt")

    .From("OpportunityInStage", "OinS")

    .Where("OinS", "OpportunityId").IsEqual("O", "Id");

 

Подскажите, пожалуйста, как можно добавить в код exists (не в фильтр) или деление count на себя?

Нравится

1 комментарий
Зверев Александр
10 апреля 2020 18:04

У меня на MSSQL ни первый, ни второй запросы не заработали. Что за база у Вас?

Если переписать первый так, результат будет тот же?

select "O"."Id", "O"."Title",
 isnull((select 1 from "OpportunityInStage" AS "OinS" where "OinS"."OpportunityId" = "O"."Id" and "OinS"."StageId" = 'fb563df2-5ae6-df11-971b-001d60e938c6'),0) AS "Contracting"
from "Opportunity" AS "O"

А с IsNull мы уже работать сможем, примерно так:

.Column(Func.IsNull(Column.SubSelect(mySubSelect), Column.Const(0)))

А по второму примеру вообще не не понял, зачем делить само на себя, там же будет либо 1, либо ошибка деления на 0.

Показать все комментарии
7.x
exists
Terrasoft.EntitySchemaQuery
фильтры
Технические вопросы
Помогите запрограммировать запрос с exists (bpm 7.2)
Лариса
18 августа 2014 16:24

Добрый день!
В модуле на js требуется запрограммировать такой запрос:

SELECT a.id
FROM Contact a
WHERE a.id > :contactId
AND EXISTS (SELECT id FROM Contact b
        WHERE
        b.id = :contactId AND
        ( (b.DebtorLastName > '' AND a.DebtorLastName = b.DebtorLastName)
        OR (b.BirthDate IS NOT NULL AND a.BirthDate = b.BirthDate)
        OR (b.Phone > '' AND a.Phone = b.Phone)
        OR (b.MobilePhone > '' AND a.MobilePhone = b.MobilePhone)
        OR (b.MobilePhone2 > '' AND a.MobilePhone2 = b.MobilePhone2))
)

часть до exists понятна, а далее не получается...

var select = Ext.create('Terrasoft.EntitySchemaQuery', { rootSchemaName: 'Contact' });
select.addColumn('Id');
select.filters.add('IdFilter', Terrasoft.createColumnFilterWithParameter(Terrasoft.ComparisonType.NOT_EQUAL, 'Id', contactId));

версия BPM 7.2.0.1223

Нравится

6 комментариев
Гущин Николай
18 августа 2014 19:07

Спасибо за Ваше обращение. Мы зарегистрировали его в Службе технической поддержки и назначили специалиста, который сможет Вас компетентно проконсультировать по данному вопросу

Андрей Каспаревич
19 августа 2014 11:22

Здравствуйте, Лариса!
Вот небольшой пример:

var esq = Ext.create('Terrasoft.EntitySchemaQuery', {
                    rootSchemaName: "Contact"
                });
				esq.addColumn('Id');
                esq.addColumn('Name');
                esq.addColumn('Account');
				var f = esq.createExistsFilter('[Activity:Contact].Id');
				var subf = esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.NOT_EQUAL, 'Title', 'Test');
				f.subFilters.add('subtitleFilter', subf);
				esq.filters.add('existActivityFilter',f);

В результате формируется следующий запрос:

SELECT
	[Contact].[Id] [Id],
	[Contact].[Name] [Name],
	[Contact].[AccountId] [AccountId],
	[Account].[Name] [Account.Name],
	[Contact].[PhotoId] [PhotoId],
	[Photo].[Name] [Photo.Name]
FROM
	[dbo].[Contact] [Contact]
	LEFT OUTER JOIN [dbo].[Account] [Account] ON ([Account].[Id] = [Contact].[AccountId])
	LEFT OUTER JOIN [dbo].[SysImage] [Photo] ON ([Photo].[Id] = [Contact].[PhotoId])
WHERE
	EXISTS (
SELECT
	[SubActivity].[Id] [Id]
FROM
	[dbo].[Activity] [SubActivity]
WHERE
	[SubActivity].[ContactId] = [Contact].[Id]
	AND [SubActivity].[Title] <> 'Test')
Лариса
19 августа 2014 11:45

Андрей у Вас в примере условие exists строится так (такой пример я находила):

WHERE
        [SubActivity].[ContactId] = [Contact].[Id]

а мне связывать напрямую главную таблицу и таблицу подзапроса не нужно

        WHERE 
        b.id = :contactId --тут у меня параметр

также мне не понятно как стоить остальные условия

        ( (b.DebtorLastName <> '' AND a.DebtorLastName = b.DebtorLastName)
        OR (b.BirthDate IS NOT NULL AND a.BirthDate = b.BirthDate)
        OR (b.Phone <> '' AND a.Phone = b.Phone)
        OR (b.MobilePhone <> '' AND a.MobilePhone = b.MobilePhone)
        OR (b.MobilePhone2 <> '' AND a.MobilePhone2 = b.MobilePhone2))

сам запрос можно переписать, например, так:

select a.* from Contact a 
	inner join Contact b on a.Id <> b.Id
where b.id = 'BF66B334-91DC-4E44-8587-4A0620BBC536'
and (
	(a.DebtorLastName <> '' and a.DebtorLastName = b.DebtorLastName)
	or (a.BirthDate is not null and a.BirthDate = b.BirthDate)
	or (a.Phone <> '' and a.Phone = b.Phone)
	or (a.MobilePhone <> '' and a.MobilePhone = b.MobilePhone)
	or (a.MobilePhone2 <> '' and a.MobilePhone2 = b.MobilePhone2)
)

но с точки зрения bpm мне предоставленный вариант показался самым простым...

Андрей Каспаревич
19 августа 2014 15:49

Лариса, в таком случае можно с помощью отдельных запросов. Так будет проще, думаю.
В запросе ж сравниваются данные по одной записи по сути. Можно сначала получить запись по параметру:

esq.getEntity(contactId, function(result) {...

и в callback-функции выполнять следующий запрос с фильтрами из значений в полученной записи.
Надеюсь, понятно объяснил.

Лариса
19 августа 2014 16:40

Андрей, запрос выбирает дубли контакта по определенным условиям.
Насчет двух запросов идея понятна, но Вы не ответили по поводу того как сделать такой блок условий:

        WHERE 
        b.id = :contactId AND
        ( (b.DebtorLastName <> '' AND a.DebtorLastName = b.DebtorLastName)
        OR (b.BirthDate IS NOT NULL AND a.BirthDate = b.BirthDate)
        OR (b.Phone <> '' AND a.Phone = b.Phone)
        OR (b.MobilePhone <> '' AND a.MobilePhone = b.MobilePhone)
        OR (b.MobilePhone2 <> '' AND a.MobilePhone2 = b.MobilePhone2) )
Андрей Каспаревич
19 августа 2014 18:08

Лариса, значит не до конца идею поняли) Или я Вашу. После получения записи по параметру, то есть контакта b в Вашем запросе должно быть что-то вроде:

//...
esq.getEntity(contactId, function (response) {
    if (response.success) {
        var entity = response.entity;
        if (entity) {
            var filterGroup = Terrasoft.createFilterGroup();
            filtersGroup.logicalOperation = Terrasoft.LogicalOperatorType.OR;
            var mobilePhone = entity.get('MobilePhone');
            if (mobilePhone) {
                var mobileFilter = Terrasoft.createColumnFilterWithParameter(
                    Terrasoft.ComparisonType.EQUAL, 'MobilePhone', mobilePhone);
                filtersGroup.add('mobileFilter', mobileFilter);
            }
            //...добавление других фильтров в группу по такому же принципу
            var esqConnectedContact = Ext.create('Terrasoft.EntitySchemaQuery', {
                rootSchemaName: "Contact"
            });
            esqConnectedContact.addColumn('Id');
            esqConnectedContact.filters.add(filtersGroup);
            esqConnectedContact.getEntityCollection(function (result) {
                //callback
            }, this);
        }
    }
}, this);
//...
Показать все комментарии
Подписаться на exists