Вопрос

Класс 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

Спасибо.

Ответить

Показать все комментарии