Помогите модифицировать селект...

[avenger]

Привет Всем!

Есть таблицы:

Код: Выделить всё

CREATE TABLE Module_News (
    Module_New_ID BIGINT NOT NULL,
    Project_FK BIGINT NOT NULL,
    User_FK BIGINT NOT NULL,
    Create_Date INTEGER NOT NULL,
    Modified_Date INTEGER NOT NULL,
    Title VARCHAR(250) NOT NULL,
    "Message" VARCHAR(5000) NOT NULL,
    Status SMALLINT DEFAULT 0 NOT NULL CONSTRAINT CC_Module_News_Status CHECK (Status in (0, 1, 2)),
    Rating SMALLINT DEFAULT 0 NOT NULL,
    Amount_Readings INTEGER DEFAULT 0 NOT NULL,
    Language VARCHAR(2) NOT NULL,
    IfActive SMALLINT NOT NULL CONSTRAINT CC_Module_News_IfActive CHECK (IfActive in (0, 1)),
    CONSTRAINT PK_Module_News PRIMARY KEY (Module_New_ID)
);

CREATE TABLE Projects (
    Project_ID BIGINT NOT NULL,
    Parent_ID BIGINT,
    Create_Date INTEGER NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Description VARCHAR(500) NOT NULL,
    Rating SMALLINT DEFAULT 0 NOT NULL,
    Amount_Readings INTEGER DEFAULT 0 NOT NULL,
    Language VARCHAR(2) NOT NULL,
    IfActive SMALLINT DEFAULT 1 NOT NULL CONSTRAINT CC_Projects_IfActive CHECK (IfActive in (0, 1)),
    CONSTRAINT PK_Projects PRIMARY KEY (Project_ID)
);

CREATE TABLE Project_Users (
    Project_PF BIGINT NOT NULL,
    User_PF BIGINT NOT NULL,
    Params VARCHAR(500),
    "Role" SMALLINT NOT NULL CONSTRAINT CC_Project_Users_Role CHECK ('Role' in (0, 10, 20, 100)),
    IfActive SMALLINT DEFAULT 1 NOT NULL CONSTRAINT CC_Project_Users_IfActive CHECK (IfActive in (0, 1)),
    Create_Date INTEGER NOT NULL,
    Modified_Date INTEGER NOT NULL,
    CONSTRAINT PK_Project_Users PRIMARY KEY (Project_PF, User_PF)
);

Есть хранимая процедура:

Код: Выделить всё

CREATE PROCEDURE Tree_Projects_Get_Nodes_ID(
  AnID BIGINT,
  Self SMALLINT)
RETURNS (
  ID BIGINT)
AS
BEGIN

  IF (:Self > 0) THEN
  BEGIN
    ID = :AnID;
    SUSPEND;
  END

  FOR SELECT Project_ID FROM Projects
  WHERE Parent_ID = :AnID INTO :ID
    DO FOR SELECT ID FROM Tree_Projects_Get_Nodes_ID(:ID, 1) INTO :ID
      DO SUSPEND;
  SUSPEND;

END

Есть запрос:

Код: Выделить всё

SELECT
    DISTINCT P.PROJECT_ID, P.PARENT_ID, P.NAME,
    MN.PROJECT_FK, MN.USER_FK, MN.TITLE, MN.LANGUAGE,
    PS.PROJECT_PF, PS.USER_PF, PS."ROLE"
FROM
    PROJECTS P
JOIN
    TREE_PROJECTS_GET_NODES_ID(8, 1) TP ON TP.ID = P.PROJECT_ID
JOIN
    MODULE_NEWS MN ON MN.PROJECT_FK = P.PROJECT_ID
LEFT JOIN
    PROJECT_USERS PS ON PS.PROJECT_PF IN (
        SELECT ID FROM TREE_PROJECTS_GET_NODES_ID(8, 1)
    ) AND PS.USER_PF = MN.USER_FK

Как можно избавится от этой части:

Код: Выделить всё

PS.PROJECT_PF IN (SELECT ID FROM TREE_PROJECTS_GET_NODES_ID(8, 1))

С уважением, Иван.

[WildSery]

Во-первых, индексы по MODULE_NEWS.PROJECT_FK и PROJECTS.PARENT_ID есть?
Во-вторых, не совсем понял, почему собственно нельзя избавиться, написав ON PS.PROJECT_PF = P.PROJECT_ID AND PS.USER_PF = MN.USER_FK

[Monk]

Код: Выделить всё

SELECT
    ...
FROM PROJECTS P
JOIN TREE_PROJECTS_GET_NODES_ID(8, 1) TP ON (TP.ID = P.PROJECT_ID)
...

И такое у тебя работает?
при INNER JOIN по ХП ты на экране ничего не должен увидеть

Более того, не рекомендуется использовать в запросах INNER JOIN и LEFT JOIN одновременно.

[WildSery]

Более того, не рекомендуется использовать в запросах INNER JOIN и LEFT JOIN одновременно.

Читаем документацию через строку? На самом деле, не рекомендуется лепить LEFT там где он не нужен.

И с процедурами тоже можно клеить. Если осторожно.

[Monk]

И с процедурами тоже можно клеить. Если осторожно.

Да, не прав. Именно в данном случае можно, т.к. процедура не зависит от полей в запросе.

[sallerman]

Да, не прав. Именно в данном случае можно, т.к. процедура не зависит от полей в запросе.

А разве в общем случае всегда зависит?

[avenger]

Во-первых, индексы по MODULE_NEWS.PROJECT_FK и PROJECTS.PARENT_ID есть?

Запрос идет по индексам

Код: Выделить всё

План
PLAN SORT (JOIN (JOIN (PROJECTS INDEX (PROJECTS_PROJECTS),JOIN (P INDEX (PK_PROJECTS),MN INDEX (PROJECTS_MODULE_NEWS))),PS INDEX (PK_PROJECT_USERS)))

Адаптированный план
PLAN SORT (JOIN (JOIN (PROJECTS INDEX (PROJECTS_PROJECTS),JOIN (P INDEX (PK_PROJECTS),MN INDEX (PROJECTS_MODULE_NEWS))),PS INDEX (PK_PROJECT_USERS)))

Во-вторых, не совсем понял, почему собственно нельзя избавиться, написав ON PS.PROJECT_PF = P.PROJECT_ID AND PS.USER_PF = MN.USER_FK

Нельзя. Есть пользователи (Users). Есть таблица с проектами в виде дерева (PROJECTS). Есть пользователи, которые записаны на проекты (PROJECT_USERS).
Пользователь записывается на проект, получает права (PROJECT_USERS.ROLE) на этот проект и на все подпроекты внутри данного проекта. Этот пользователь на подпроект записаться уже не может.
Есть модули, которые привязаны к дереву проектов (например, MODULE_NEWS). В модуле пользователь создает объекты, привязанные к данному пользователю, и к данному проекту.

Пример:
Пусть есть дерево:

Код: Выделить всё

 1
 |-2-
    |-3
    |-4

Пусть есть пользователь ID=1 (таблица Users). Пусть он привязан к подпроекту 2 (PROJECT_USERS). Т.е. Project_FK = 2, User_FK = 1 (одна запись).
Пусть он создал по объекты в модуле (MODULE_NEWS) c:
Project_FK = 2, User_FK = 1
Project_FK = 3, User_FK = 1
Project_FK = 4, User_FK = 1

Теперь ваш запрос выведет:
Project_FK = 2, User_FK = 1, ROLE = число
Project_FK = 3, User_FK = 1, ROLE = null
Project_FK = 4, User_FK = 1, ROLE = null
Мой запрос выведет:
Project_FK = 2, User_FK = 1, ROLE = число
Project_FK = 3, User_FK = 1, ROLE = число
Project_FK = 4, User_FK = 1, ROLE = число

Вот, почему нельзя. Мне надо получить права (Role) пользователя на всех подпроектах создавшего данный объект.

[Кузнецов Евгений]

Добрый день!

To Avenger

1) Ну зачем Вам последний SUSPEND в Tree_Projects_Get_Nodes_ID?
сделайте выборку SELECT ID FROM TREE_PROJECTS_GET_NODES_ID(8, 1)
и посмотрите на количество дубликатов
(с которыми Вы потом боретесь посредством Distinct)

2) Даже если проект с ID = 100 вовсе отсутствует в таблице
SELECT ID FROM TREE_PROJECTS_GET_NODES_ID(100, 1) вернет 100

3) По поводу запроса - если пользователь имеет права
хотя бы на один из подпроектов, то Ваш запрос
покажет, что он имеет права на весь проект.
Поэтому настоятельный совет - регистрируйте права пользователя
явно на каждый объект, чтобы потом не заморачиваться с такими
запросами.

To All

Да, я тут проспал, когда связка table + SP стала безглючной?

[avenger]

1) Ну зачем Вам последний SUSPEND в Tree_Projects_Get_Nodes_ID?
2) Даже если проект с ID = 100 вовсе отсутствует в таблице
SELECT ID FROM TREE_PROJECTS_GET_NODES_ID(100, 1) вернет 10

Спасибо! Переписал ХП

Код: Выделить всё

CREATE PROCEDURE TREE_PROJECTS_GET_NODES_ID (
    ANID BIGINT,
    SELF SMALLINT)
RETURNS (
    ID BIGINT)
AS
BEGIN

  IF ( (:SELF > 0) AND EXISTS(SELECT PROJECT_ID FROM PROJECTS WHERE PROJECT_ID = :ANID) ) THEN
  BEGIN
    ID = :ANID;
    SUSPEND;
  END

  FOR SELECT PROJECT_ID FROM PROJECTS
  WHERE PARENT_ID = :ANID INTO :ID
    DO FOR SELECT ID FROM TREE_PROJECTS_GET_NODES_ID(:ID, 1) INTO :ID
      DO SUSPEND;

END^