query()
select()
where()
запросы к БД с помощью select()
Технические вопросы
7.x
Класс Select() - помогите написать условие Where для сложного запроса
Лариса
12 февраля 2014 17:47

Помогите, пожалуйста, написать условие Where для запроса, используя класс Select().
Запрос (обратите внимание на комментарии в тексте запроса):

SELECT
        a.Id AS AccountId, a.Name AS AccountName, a.Code AS AccountCode, a.CreatedOn AS AccountCreatedOn
        , a.CompanyActivity
        , a.OwnerId, o.Name AS OwnerName
        , o.OrganizationChartId AS BranchId, b.CustomDepartmentName AS BranchName
FROM Account a
        LEFT JOIN Contact o ON o.Id = a.OwnerId
                LEFT JOIN AccountOrganizationChart b ON b.Id = o.OrganizationChartId
WHERE
        (
        EXISTS (SELECT ac.Id FROM Activity ac WHERE ac.AccountId = a.Id)
        OR EXISTS (SELECT cam.Id FROM Campaign cam INNER JOIN CampaignTarget ct ON ct.CampaignId = cam.Id WHERE ct.AccountId = a.Id)
        OR EXISTS (SELECT d.Id FROM Document d WHERE d.AccountId = a.Id AND d.TypeId = '{EAAEF18E-6E19-41B3-B808-473A78016220}')
        )
        /* УСЛОВИЯ in МОГУТ ОТСУТСТВОВАТЬ (В ЗАВИСИМОСТИ ОТ ВЫБОРА ПОЛЬЗОВАТЕЛЯ) */
        AND o.OrganizationChartId IN ('что-то')
        AND a.OwnerId IN ('что-то')
ORDER BY a.Name

 

пока что удалось сделать следующее:

Terrasoft.Core.DB.Select mainQuery = new Terrasoft.Core.DB
    .Select(userConnection)
        .Column("a", "Id").As("AccountId").Column("a", "Name").As("AccountName")
        .Column("a", "Code").As("AccountCode").Column("a", "CreatedOn").As("AccountCreatedOn")
        .Column("a", "CompanyActivity").As("CompanyActivity")
        .Column("a", "OwnerId")
        .Column(new QueryColumnExpression() { ExpressionType = QueryColumnExpressionType.SqlText, SqlText = "isnull([o].[Name],'не указан>')" }).As("OwnerName")
        .Column("o", "OrganizationChartId").As("BranchId")
        .Column(new QueryColumnExpression() { ExpressionType = QueryColumnExpressionType.SqlText, SqlText = "isnull([b].[CustomDepartmentName],'не указан>')" }).As("BranchName")

    .From("Account").As("a")
        .LeftOuterJoin("Contact").As("o").On("o", "Id").IsEqual("a", "OwnerId")
            .LeftOuterJoin("AccountOrganizationChart").As("b").On("b", "Id").IsEqual("o", "OrganizationChartId")

    .Where()
        .Exists(
            new Terrasoft.Core.DB.Select(userConnection).Column("ac","Id").From("Activity").As("ac").Where("ac","AccountId").IsEqual("a","Id")
        ).Or()
        .Exists(
            cQuery /*Terrasoft.Core.DB.Select подзапрос, формируется отдельно по условию*/
        ).Or()
        .Exists(
            new Terrasoft.Core.DB.Select(userConnection).Column("d", "Id").From("Document").As("d").Where("d", "AccountId").IsEqual("a", "Id")
            .And("d", "TypeId").IsEqual(new QueryColumnExpression() { ExpressionType = QueryColumnExpressionType.SqlText, SqlText = "'EAAEF18E-6E19-41B3-B808-473A78016220'" })
        )

    .OrderByAsc("a", "Name")
    as Select;

 

Не получается поместить все три exists в блок, чтобы потом добавить условия in.
И еще вопрос: можно ли как-то динамически формировать блок условий (т.к. они у меня зависят от настроек пользователя), а потом "подсовывать" его select'у ?

Версия 7.1.0.172

Нравится

17 комментариев
Гущин Николай
12 февраля 2014 19:41

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

Андрей Каспаревич
13 февраля 2014 14:34

Здравствуйте, Лариса!
Для комбинирования условий используются операторы OpenBlock и CloseBlock. Например:

Select select =
        new Select(UserConnection)
                .Column("Id")
                .Column("SysSchemaId")
                .Column("Name")
                .Column("SysSchemaManagerName")
                .Column("SysSchemaFolderId")
                .Column("MetaDataModifiedOn")
        .From("VwSysSchemaInSolution")
        .Where("SysSolutionId").IsEqual(new QueryParameter("solutionId", userConnection.Solution.Id))
                .And("SysSchemaId").In(schemas)
               .And("SysSchemaStateInSolution").IsNotEqual(Column.Const((int)StoringObjectState.Deleted))
                .And().OpenBlock("LockedById").IsNull()
                        .Or("LockedById").IsEqual(newQueryParameter("currentUserId", userConnection.CurrentUser.Id))
                .CloseBlock()
                as Select;

Тут больше информации.

По поводу добавления условий Вы можете просто к mainQuery как к переменной дальше добавлять еще условия после проверки настроек пользователя.

Зверев Александр
13 февраля 2014 15:08

"Андрей Каспаревич" написал:
Тут больше информации.

Предвижу ответ "у меня ссылка не открывается."

Лариса
13 февраля 2014 16:22

"Андрей Каспаревич" написал:Тут больше информации.

спасибо, эту тему я видела

"Андрей Каспаревич" написал:Для комбинирования условий используются операторы OpenBlock и CloseBlock.

если я делаю так:

.Where()
.OpenBlock()
        .Exists(
            new Terrasoft.Core.DB.Select(userConnection).Column("ac","Id").From("Activity").As("ac").Where("ac","AccountId").IsEqual("a","Id")
        ).Or()
        .Exists(
            cQuery /*Terrasoft.Core.DB.Select подзапрос, формируется отдельно по условию*/
        ).Or()
        .Exists(
            new Terrasoft.Core.DB.Select(userConnection).Column("d", "Id").From("Document").As("d").Where("d", "AccountId").IsEqual("a", "Id")
            .And("d", "TypeId").IsEqual(new QueryColumnExpression() { ExpressionType = QueryColumnExpressionType.SqlText, SqlText = "'EAAEF18E-6E19-41B3-B808-473A78016220'" })
        )
.CloseBlock()

то страница публикуется, но при выполнении выдает такую ошибку:
Сервер обнаружил ошибку при обработке запроса. Сообщение об исключении: "Содержать вложенные условия может только условие с типом блок или пустой блок".

Бондарь Наталия
14 февраля 2014 10:39

Лариса, посмотрите профайлером какой запрос создается при выполнении кода.

Лариса
14 февраля 2014 12:58

"Бондарь Наталия" написал:

Лариса, посмотрите профайлером какой запрос создается при выполнении кода.

Наталия, до выполнения запроса дело не доходит, т.к. профайлер ничего не показывает

Бондарь Наталия
14 февраля 2014 18:41

Лариса, немного упростила Ваш запрос, чтобы проверить в базовой версии.
Такой вариант:

Terrasoft.Core.DB.Select mainQuery = new Terrasoft.Core.DB
    .Select(Page.UserConnection)
        .Column("a", "Id").As("AccountId").Column("a", "Name").As("AccountName")
        .Column("a", "Code").As("AccountCode").Column("a", "CreatedOn").As("AccountCreatedOn")
        .Column("a", "OwnerId")   
 
    .From("Account").As("a")
        .LeftOuterJoin("Contact").As("o").On("o", "Id").IsEqual("a", "OwnerId")
    .Where()
        .OpenBlock("a","Id")
        .Exists(
            new Terrasoft.Core.DB.Select(Page.UserConnection).Column("ac","Id").From("Activity").As("ac").Where("ac","AccountId").IsEqual("a","Id")
        ).Or()
       /* .Exists(
            cQuery /*Terrasoft.Core.DB.Select подзапрос, формируется отдельно по условию*/
        /*).Or()*/
        .Exists(
            new Terrasoft.Core.DB.Select(Page.UserConnection).Column("d", "Id").From("Document").As("d").Where("d", "AccountId").IsEqual("a", "Id")
            .And("d", "TypeId").IsEqual(new QueryColumnExpression() { ExpressionType = QueryColumnExpressionType.SqlText, SqlText = "'EAAEF18E-6E19-41B3-B808-473A78016220'" })
        )
.CloseBlock()
 
    .OrderByAsc("a", "Name")
    as Select;
	mainQuery.Execute();

Возвращает результат:

SELECT
	[a].[Id] [AccountId],
	[a].[Name] [AccountName],
	[a].[Code] [AccountCode],
	[a].[CreatedOn] [AccountCreatedOn],
	[a].[OwnerId]
FROM
	[dbo].[Account] [a]
	LEFT OUTER JOIN [dbo].[Contact] [o] ON ([o].[Id] = [a].[OwnerId])
WHERE
	(EXISTS (
SELECT
	[ac].[Id]
FROM
	[dbo].[Activity] [ac]
WHERE
	[ac].[AccountId] = [a].[Id])
	OR EXISTS (
SELECT
	[d].[Id]
FROM
	[dbo].[Document] [d]
WHERE
	[d].[AccountId] = [a].[Id]
	AND [d].[TypeId] = 'EAAEF18E-6E19-41B3-B808-473A78016220'))
ORDER BY
	[a].[Name] ASC

Чтобы Ваш код работал укажите поле внутри скобок блока OpenBlock

Лариса
17 февраля 2014 11:59

Спасибо, Наталия!!! Работает.
А всего лишь нужно было проявить немного фантазии :smile:

Глобин Олег
18 февраля 2017 02:31

Добрый день, не могу разобраться с Where In, надо сделать Update Opportunity, есть массив Id, что то вроде

string[] ids = ["id1", "id2", "id3"]

update Opportunity set UsrValue = 1 where Id in ids

Мотков Илья
19 февраля 2017 07:07

Олег, попробуйте построить запрос основываясь на примерах:

- http://www.community.terrasoft.ru/forum/topic/22739#comment-64711
- http://www.community.terrasoft.ru/ideas/24943#comment-66257

Глобин Олег
19 февраля 2017 21:45

Видимо я не правильно выразился, вы привели примеры простого update это у меня итак получается, интересует конкретно where in условие, вот единственный пример из документации

var select = new Select(userConnection)
        .Column("Name")
        .From("Contact")
        .Where(Column.Parameter(new DateTime(), "DateTime")).In("CreatedOn", "ModifiedOn");

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

string[] ids = ["id1", "id2", "id3"]
Update update = new Update(UserConnection, "Opportunity")
        .Set("Column", Column.Parameter("1"))
        .Where("Id").In(ids)

получаю запрос вида

update Opportunity set column = '1' where 'Id' in ([1], [2], [3])

Мотков Илья
20 февраля 2017 00:27

"Глобин Олег" написал:

Видимо я не правильно выразился, вы привели примеры простого update это у меня итак получается, интересует конкретно where in условие, вот единственный пример из документации

var select = new Select(userConnection)

        .Column("Name")

        .From("Contact")

        .Where(Column.Parameter(new DateTime(), "DateTime")).In("CreatedOn", "ModifiedOn");

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

string[] ids = ["id1", "id2", "id3"]

Update update = new Update(UserConnection, "Opportunity")

        .Set("Column", Column.Parameter("1"))

        .Where("Id").In(ids)

получаю запрос вида

update Opportunity set column = '1' where 'Id' in ([1], [2], [3])

Получается Вы разборались да?

Глобин Олег
20 февраля 2017 19:01

получается что запрос который я написал выше пытается подставить в IN значения как названия колонок что видно по квадратным скобкам...

Зверев Александр
20 февраля 2017 19:45

Так нужно массив не строк, а специального типа:

	var indx = 0;
	var emailsQueryParameters = new QueryParameter[((List<string>)RecepientsEmailsForDelete).Count];
	foreach(var email in (List<string>)RecepientsEmailsForDelete){
		emailsQueryParameters[indx] = new QueryParameter(email);
		indx++;
	}
	new Delete(UserConnection).From("ActivityParticipant")
					.Where("ActivityId").IsEqual(Column.Parameter(Entity.PrimaryColumnValue)).And("ParticipantId").
						In(new Select(UserConnection).Column("Id").From("Contact").Where("Email").In(emailsQueryParameters)).Execute();
Сурмачевский Евгений Александрович
4 апреля 2017 13:23

Добрый день.
Подскажите как я могу написать select запрос с применением агрегирующих функций типа SUM, AVG и т.д.???

Зверев Александр
4 апреля 2017 14:37

Вот так с COUNT, для других аналогично.

Сурмачевский Евгений Александрович
4 апреля 2017 14:43

Спасибо.

Показать все комментарии
Подписаться на запросы к БД с помощью select()