0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@pmc-ko(K Ohtsuka)
in

プリザンター内部で動くSQL文を調べてみる[第2回：一覧編]

0
Posted at

はじめに

第 1 回 ではプリザンターの SQL 組み立てアーキテクチャの全体像を解説しました。
本記事（第 2 回）では 一覧画面（Index アクション） に絞り、WHERE 句・ORDER BY 句・ページネーションが実際にどう組み立てられるかを詳しく見ていきます。

バージョン 1.5.2.0 を対象にしています

回 タイトル
第 1 回 アーキテクチャ編 ── SQL を組み立てる仕組みの全体像
第 2 回（本記事） 一覧編 ── フィルタとソートの WHERE・ORDER BY
第 3 回 編集編 ── SELECT・INSERT・UPDATE の実装
第 4 回 カレンダー編 ── 日付範囲クエリの実装
第 5 回 ガントチャート編 ── GanttUtilities.Where() と GanttLimit
第 6 回 バーンダウンチャート編 ── 現在 + 履歴 UNION クエリ
第 7 回 時系列チャート編 ── HorizontalAxis と NormalAndHistory
第 8 回 カンバン編 ── KambanLimit・全件取得・カード移動
第 9 回 クロス集計編 ── GROUP BY と日付軸グループ化式
第 10 回 画像ライブラリ編 ── Binaries INNER JOIN クエリ
第 11 回 分析チャート編 ── 分析パーツごとの現在＋履歴 SQL

一覧画面の処理フロー

GET /items/{siteId}
 └─ IssueUtilities.Index()                         ← L31  IssueUtilities.cs
     └─ GetGridData(context, ss, view, offset)      ← L239 IssueUtilities.cs
         └─ new GridData(context, ss, view,
                         offset, pageSize)           ← L26  GridData.cs
             └─ Get()                               ← L55  GridData.cs
                 ├─ ss.GetGridColumns()             → ① SELECT 列の決定
                 ├─ View.Where()                    → ② WHERE 句の生成
                 ├─ View.OrderBy()                  → ③ ORDER BY 句の生成
                 ├─ ss.Join()                       → ④ JOIN 句の自動決定
                 ├─ Rds.Select()                    ⑤ データ取得 SELECT
                 └─ Rds.SelectCount()               ⑥ 件数取得 SELECT COUNT

IssueUtilities.Index() はセッションからビュー設定を読み取り、GetGridData() に渡します。GetGridData() は GridData コンストラクタを呼び出すだけです。実際の SQL 組み立てはすべて GridData.Get() の中で行われます。

一覧画面の SQL 組み立てフロー

一覧画面のリクエストは次の順に処理されます。

コアとなるのは GridData.Get() メソッドです。

Libraries/Models/GridData.cs
private void Get(
    Context context, SiteSettings ss, View view,
    Sqls.TableTypes tableType, ...)
{
    // ① 表示列を取得
    var gridColumns = ss.GetGridColumns(context, view, includedColumns: true);

    // ② SELECT 句を組み立て
    column = column ?? ColumnUtilities.SqlColumnCollection(
        context, ss, view, columns: gridColumns);

    // ③ WHERE 句を組み立て（フィルタ・権限）
    where = view.Where(context, ss, where: where);

    // ④ ORDER BY 句を組み立て（ソート設定）
    var orderBy = view.OrderBy(context, ss);

    // ⑤ JOIN 句を組み立て（列・WHERE・ORDER BY が参照する結合先）
    join = join ?? ss.Join(context, join: new IJoin[] { column, where, orderBy });

    // ⑥ クエリを実行（データ + 件数）
    var statements = new List<SqlStatement>
    {
        Rds.Select(tableName: ss.ReferenceType, ...,
            column: column, join: join, where: where,
            orderBy: orderBy, offset: offset, pageSize: pageSize),
        Rds.SelectCount(tableName: ss.ReferenceType, join: join, where: where)
    };
    var dataSet = Repository.ExecuteDataSet(context, statements: statements.ToArray());
}

ss.GetGridColumns() による SELECT 列の決定

ss.GetGridColumns() は、サイト設定の「一覧に表示する項目」（GridColumns）から SQL の SELECT 句に使う列一覧を返します。

Libraries/Models/GridData.cs
// Get() 内の列取得処理
var gridColumns = ss.GetGridColumns(
    context: context,
    view: view,
    includedColumns: true);   // VIEW 定義に含まれる列を取得

// Ver 列は必ず含める（変更履歴表示の判定に使用）
if (!gridColumns.Any(o => o.ColumnName == "Ver"))
{
    gridColumns.Add(ss.GetColumn(context: context, columnName: "Ver"));
}

// SqlColumnCollection に変換（JOIN に必要なテーブル名情報も付与）
column = column ?? ColumnUtilities.SqlColumnCollection(
    context: context,
    ss: ss,
    view: view,
    columns: gridColumns);

GridColumns に含まれる列の種類によって、SELECT 句と JOIN 句の内容が変わります。

列の種類 追加される SELECT 列 追加される JOIN
通常列（Status, Manager, …） "Issues"."列名" なし
タイトル列（ItemTitle） "Items"."Title" AS "ItemTitle" Items INNER JOIN
結合サイト列 "テーブル別名"."列名" 結合先 LEFT OUTER JOIN

ss.Join() による JOIN 句の自動生成

ss.Join() は IJoin インターフェースを実装する各コレクションに JoinTableNames() を呼び出し、必要なテーブル名を自動収集して JOIN 句を生成します。

Libraries/Settings/SiteSettings.cs
public SqlJoinCollection Join(Context context, params IJoin[] join)
{
    return SqlJoinCollection(
        context: context,
        tableNames: join
            .Where(o => o != null)
            .SelectMany(o => o.JoinTableNames())  // 各コレクションが参照テーブル名を返す
            .Distinct()
            .ToList());
}

GridData.Get() での呼び出し：

Libraries/Models/GridData.cs
// column（SELECT 列）・where（WHERE 条件）・orderBy（ソート列）が参照する
// テーブルを一括収集して JOIN を自動生成
join = join ?? ss.Join(
    context: context,
    join: new IJoin[]
    {
        column,   // SELECT 句が参照するテーブル（Items, 結合サイトなど）
        where,    // WHERE 句が参照するテーブル
        orderBy   // ORDER BY 句が参照するテーブル（タイトルソート時に Items）
    });

Items テーブルへの JOIN には、最新バージョンのレコードのみを取得するための相関サブクエリが自動付与されます：

INNER JOIN "Items" ON "Items"."ReferenceId" = "Issues"."IssueId"
    AND "Items"."Ver" = (
        SELECT MAX("NItems"."Ver") FROM "Items" "NItems"
        WHERE "NItems"."ReferenceId" = "Issues"."IssueId")

このため、履歴テーブルに複数バージョンがあっても最新の Title だけが JOIN されます。

発行される SQL のイメージ

一覧画面では、データ取得と件数取得の 2 本の SQL が同時に実行されます。

-- ① データ取得
SELECT "Issues"."IssueId", "Items"."Title", "Issues"."Status", ...
FROM "Issues"
INNER JOIN "Items" ON "Items"."ReferenceId" = "Issues"."IssueId"
    AND "Items"."Ver" = (
        SELECT MAX("NItems"."Ver") FROM "Items" "NItems"
        WHERE "NItems"."ReferenceId" = "Issues"."IssueId")
WHERE
    "Issues"."TenantId" = @TenantId_0
    AND "Issues"."SiteId" = @SiteId_0
    AND ...（フィルタ条件）
ORDER BY "Issues"."UpdatedTime" DESC, "Issues"."IssueId" DESC
OFFSET @_U_Offset ROWS FETCH NEXT @_U_PageSize ROWS ONLY

-- ② 件数取得
SELECT COUNT(*) "Count"
FROM "Issues"
INNER JOIN ...
WHERE ...（同じ WHERE 句）

WHERE 句の組み立て詳細

View.Where() は以下の 5 ステップで WHERE 条件を積み上げます。

Libraries/Settings/View.cs
public SqlWhereCollection Where(Context context, SiteSettings ss, ...)
{
    // ① 一括処理フィルタ
    SetBulkProcessingFilter(context, ss, process, where);
    // ② 定型フィルタ（未完了・自分・期限間近など）
    SetGeneralsWhere(context, ss, where);
    // ③ 列フィルタ（画面のフィルタ欄）
    SetColumnsWhere(context, ss, where);
    // ④ テキスト検索フィルタ
    SetSearchWhere(context, ss, where, itemJoin);
    // ⑤ 権限フィルタ（テナント・サイト・行レベル）
    Permissions.SetPermissionsWhere(context, ss, where, permissionType);
    return where;
}

② 定型フィルタ（SetGeneralsWhere）

画面上部のフィルタチェックボックス（「未完了のみ」「自分」「期限間近」「遅延」「期限超過」）はここで SQL に変換されます。

未完了（Incomplete）

// View.cs SetGeneralsWhere
if (Incomplete == true && HasIncompleteColumns(context, ss))
{
    where.Add(
        tableName: ss.ReferenceType,
        columnBrackets: "\"Status\"".ToSingleArray(),
        _operator: "<" + Parameters.General.CompletionCode); // Status < 900（完了コード）
}

生成される SQL 条件：

"Issues"."Status" < 900

自分（Own）

// View.cs SetGeneralsWhere
if (Own == true && HasOwnColumns(context, ss))
{
    where.Add(
        tableName: ss.ReferenceType,
        columnBrackets: new[] { "\"Manager\"", "\"Owner\"" },
        name: "_U",
        value: context.UserId,
        _operator: "=",
        multiColumnOperator: " or ");  // Manager = @_U OR Owner = @_U
}

生成される SQL 条件：

("Issues"."Manager" = @_U OR "Issues"."Owner" = @_U)

期限間近（NearCompletionTime）

// View.cs SetGeneralsWhere
if (NearCompletionTime == true && HasNearCompletionTimeColumns(context, ss))
{
    where.Add(
        tableName: ss.ReferenceType,
        columnBrackets: "\"CompletionTime\"".ToSingleArray(),
        _operator: " between '{0}' and '{1}'".Params(
            DateTime.Now.Date.AddDays(ss.NearCompletionTimeBeforeDays * -1),
            DateTime.Now.Date.AddDays(ss.NearCompletionTimeAfterDays + 1)));
}

生成される SQL 条件（NearCompletionTimeBeforeDays=3、NearCompletionTimeAfterDays=3 の例）：

"Issues"."CompletionTime" between '2026-03-12' and '2026-03-19'

遅延（Delay）

// View.cs SetGeneralsWhere
where
    .Add(tableName: ss.ReferenceType,
         columnBrackets: "\"Status\"".ToSingleArray(),
         _operator: "<" + Parameters.General.CompletionCode)  // 未完了
    .Add(tableName: ss.ReferenceType,
         columnBrackets: "\"ProgressRate\"".ToSingleArray(),
         _operator: "<",
         raw: Def.Sql.ProgressRateDelay  // 進捗率が期待値を下回る
             .Replace("#TableName#", ss.ReferenceType));

③ 列フィルタ（SetColumnsWhere）

フィルタ欄に入力された値は ColumnFilterHash（列名→値の辞書）に格納され、SetColumnsWhere() で SQL 条件に変換されます。

Libraries/Settings/View.cs
private void SetColumnsWhere(
    Context context, SiteSettings ss, SqlWhereCollection where,
    Dictionary<string, string> columnFilterHash, ...)
{
    columnFilterHash?
        .Select(data => new {
            Column = ss.GetColumn(context, data.Key),
            Or  = data.Key.StartsWith("or_"),    // OR グループ
            And = data.Key.StartsWith("and_"),   // AND グループ
            Eq  = data.Key.StartsWith("eq_"),    // 完全一致
            NotEq = data.Key.StartsWith("notEq_"), // 否定一致
        })
        .ForEach(data =>
        {
            if (data.Or)
            {
                var orHash = data.Value.Deserialize<Dictionary<string, string>>();
                var orWhere = new SqlWhereCollection();
                SetColumnsWhere(context, ss, orWhere, orHash, ...);
                if (orWhere.Any()) where.Or(or: orWhere);   // ← OR 条件
            }
            else if (data.And)
            {
                var andHash = data.Value.Deserialize<Dictionary<string, string>>();
                var andWhere = new SqlWhereCollection();
                SetColumnsWhere(context, ss, andWhere, andHash, ...);
                if (andWhere.Any()) where.Add(and: andWhere); // ← AND 条件
            }
            // 個別列の条件追加（型に応じて数値・日時・文字列に分岐）
        });
}

列の型に応じて条件の組み方が変わります。

列の型 生成される SQL の例
数値（decimal） "Issues"."NumA" >= 100 AND "Issues"."NumA" <= 200
日時（datetime） "Issues"."StartTime" >= @_D_0 AND "Issues"."StartTime" < @_D_1
文字列（nvarchar） "Issues"."ClassA" like N'%keyword%'
分類（選択肢） "Issues"."ClassA" in (@ClassA_0, @ClassA_1)
チェックボックス "Issues"."CheckA" = 1

④ テキスト検索フィルタ（SetSearchWhere）

画面上部の検索ボックスは Search プロパティに格納され、SetSearchWhere() で LIKE 検索に変換されます。複数のキーワードを「 or 」でつなぐか改行で区切ると OR 検索になります。

Libraries/Settings/View.cs
private void SetSearchWhere(
    Context context, SiteSettings ss, SqlWhereCollection where, bool itemJoin)
{
    Search?
        .Replace("　", " ")
        .Replace(" or ", "\n")  // "A or B" → "A\nB" として OR 分割
        .Split('\n')
        .Where(o => !o.IsNullOrEmpty())
        .ForEach(search =>
            collection.Add(and: new SqlWhereCollection()
                .FullTextWhere(context, ss, searchText: search, ...)));
    if (collection.Any())
        where.Add(or: collection);  // キーワード間は OR
}

生成される SQL 条件（例："プリザンター" で検索）：

(
    "Items"."Title" like N'%プリザンター%'
    OR "Issues"."Body" like N'%プリザンター%'
    OR "Issues"."ClassA" like N'%プリザンター%'
    ...
)

⑤ 権限フィルタ（SetPermissionsWhere）

テナント ID とサイト ID の絞り込みが必ず付加されます。

Libraries/Security/Permissions.cs
public static SqlWhereCollection SetPermissionsWhere(
    Context context, SiteSettings ss, SqlWhereCollection where, ...)
{
    // テナント絞り込み（必ず付加）
    where.Add(raw: $"\"{ss.ReferenceType}\".\"TenantId\"={context.TenantId}");

    // サイト絞り込み（SiteId が設定されている場合）
    where.Add(raw: $"\"{ss.ReferenceType}\".\"SiteId\"={ss.SiteId}");

    // 行レベル権限（サイト権限がない場合、レコード単位で Permission チェック）
    if (!CheckSitePermission(context, ss, permissionType) && checkPermission)
    {
        where.CheckRecordPermission(context, ss, permissionType);
    }
    return where;
}

生成される SQL 条件の例：

"Issues"."TenantId" = 1
AND "Issues"."SiteId" = 42

ORDER BY 句の組み立て詳細

View.OrderBy() は ColumnSorterHash（列名→昇降順の辞書）から ORDER BY 条件を組み立てます。

Libraries/Settings/View.cs
public SqlOrderByCollection OrderBy(Context context, SiteSettings ss, ...)
{
    // 拡張 SQL による ORDER BY のカスタマイズ
    orderBy.OnSelectingOrderByExtendedSqls(context, ss, ...);

    // 変更履歴表示時は IssueId DESC, Ver DESC に固定
    if (ShowHistory == true) SetSorterHashOnShowHistory(ss);

    // 画面で設定したソート列を適用
    if (ColumnSorterHash?.Any() == true)
    {
        ColumnSorterHash.ForEach(data =>
        {
            var column = ss.GetColumn(context, data.Key);
            if (column != null)
                OrderBy(context, ss, orderBy, data, column);
        });
    }

    // デフォルトソート：UpdatedTime DESC, IssueId DESC を追加（未設定時）
    if (!orderBy.Any(o => o.ColumnBracket == "\"UpdatedTime\""))
        orderBy.Add(tableName: ss.ReferenceType,
            columnBracket: "\"UpdatedTime\"",
            orderType: SqlOrderBy.Types.desc);
    if (!orderBy.Any(o => o.ColumnBracket == $"\"{Rds.IdColumn(ss.ReferenceType)}\""))
        orderBy.Add(tableName: ss.ReferenceType,
            columnBracket: $"\"{Rds.IdColumn(ss.ReferenceType)}\"",
            orderType: SqlOrderBy.Types.desc);

    return orderBy;
}

ソート列の特殊ケース

タイトル列（Title）や結合テーブルの列は、通常とは異なるテーブル名で ORDER BY が生成されます。

Libraries/Settings/View.cs
private static void OrderBy(
    Context context, SiteSettings ss,
    SqlOrderByCollection orderBy,
    KeyValuePair<string, SqlOrderBy.Types> data,
    Column column)
{
    switch (column.Name)
    {
        case "Title":
        case "ItemTitle":
            // タイトルは Items テーブルの Title 列を参照
            orderBy.Add(new SqlOrderBy(
                columnBracket: "\"Title\"",
                orderType: data.Value,
                tableName: $"{column.TableName()}_Items",
                isNullValue: "''"));
            break;
        default:
            // 通常列
            orderBy.Add(column: column, orderType: data.Value,
                isNullValue: column.IsNullValue(context));
            break;
    }
}

タイトルでソートすると、Items テーブルへの JOIN が自動で追加されます。

デフォルトソートの保証

ソート設定を何もしていない場合でも、UpdatedTime DESC, IssueId DESC が必ず付加されます。これにより同一条件での再現性のあるページネーションが保証されます。

生成される ORDER BY 句の例（「ステータス昇順」でソートした場合）：

ORDER BY
    "Issues"."Status" ASC,
    "Issues"."UpdatedTime" DESC,  ← デフォルト追加
    "Issues"."IssueId" DESC        ← デフォルト追加
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

ページネーション

OFFSET ... FETCH NEXT は SqlOrderByCollection.BuildCommandText() 内で pageSize != 0 のときに自動付加されます。

項目 SQL パラメータ 値の出所
オフセット（開始行） @_U_Offset 現在のページ番号 × 1 ページあたりの件数
取得件数 @_U_PageSize サイト設定の「1 ページの行数」（GridPageSize）

各ビューモードとの SQL 比較

一覧画面（GridData）の SQL 特性を他のビューモードと比較します。

項目 一覧（GridData） カレンダー ガントチャート カンバン
SELECT 列 GridColumns（多数・可変） Id・From・To・Title など最小限 Id・WorkValue・StartTime など進捗列 Id・Title・Status + X/Y/Value 列
ORDER BY ColumnSorterHash + UpdatedTime DESC なし なし なし
GROUP BY なし なし なし なし（C# 集計）
ページネーション OFFSET … FETCH NEXT なし（全件） なし（全件） なし（全件）
件数 SQL あり（SELECT COUNT） あり（CalendarLimit） なし（Count チェックのみ） あり（InRange で COUNT）
上限制御 GridPageSize でページ分割 CalendarLimit で制限 GanttLimit で制限 KambanLimit で制限

まとめ

本記事では、一覧画面の SQL 組み立てを解説しました。

  • SELECT 句：サイト設定の「一覧に表示する項目」から SqlColumnCollection が生成される
  • WHERE 句：定型フィルタ・列フィルタ・テキスト検索・権限フィルタの 4 層が AND で結合される
  • ORDER BY 句：ユーザーのソート設定が適用され、末尾に UpdatedTime DESC, Id DESC が必ず付加される
  • ページネーション：OFFSET ... FETCH NEXT が OrderBy の中で自動付加される

第 4 回では、カレンダーの日付範囲クエリを詳しく見ていきます。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?