ドキュメント
Bob ORM
Query Builder
SQLite
Select

Select

Bob を使用した SQLite の SELECT クエリの例を以下に示します。

基本的な SELECT 

SELECT sm.id, sm.name
FROM seeded_models sm
WHERE sm.id > ?
ORDER BY sm.id
LIMIT ?
sqlite.Select(
    sm.Columns("id", "name"),
).From(
    sm.Name().As("sm"),
).Where(
    sm.ColByName("id").GT(sqlite.Arg(100)),
).OrderBy(
    sm.ColByName("id"),
).Limit(10)

JSON 操作

SQLite の JSON 関数を使用した SELECT：

SELECT JSON_EXTRACT(data, '$.name') as name
FROM users
WHERE JSON_EXTRACT(data, '$.age') > ?
sqlite.Select(
    sqlite.F("JSON_EXTRACT", sqlite.Quote("data"), sqlite.Arg("$.name")).As("name"),
).From("users").
Where(
    sqlite.F("JSON_EXTRACT", sqlite.Quote("data"), sqlite.Arg("$.age")).GT(sqlite.Arg(25)),
)

FTS (Full-Text Search) 

SELECT * FROM documents 
WHERE documents MATCH ?
sqlite.Select(sqlite.Raw("*")).
From("documents").
Where(
    sqlite.Quote("documents").Match(sqlite.Arg("search term")),
)

Common Table Expressions (CTE) 

WITH RECURSIVE series(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM series WHERE x < ?
)
SELECT * FROM series
cte := sqlite.Select(sqlite.Raw("1")).
    UnionAll(
        sqlite.Select(sqlite.Quote("x").Plus(sqlite.Arg(1))).
            From("series").
            Where(sqlite.Quote("x").LT(sqlite.Arg(10))),
    )

sqlite.Select(sqlite.Raw("*")).
WithRecursive("series", cte).
WithColumns("x").
From("series")

GLOB パターンマッチング 

SELECT * FROM files WHERE filename GLOB ?
sqlite.Select(sqlite.Raw("*")).
From("files").
Where(
    sqlite.Quote("filename").Glob(sqlite.Arg("*.txt")),
)

DATE 関数 

SELECT * FROM events 
WHERE date(created_at) = date('now')
sqlite.Select(sqlite.Raw("*")).
From("events").
Where(
    sqlite.F("date", sqlite.Quote("created_at")).EQ(sqlite.F("date", sqlite.Arg("now"))),
)

GROUP BY と集計関数 

SELECT category, COUNT(*) as count, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING count > ?
sqlite.Select(
    sqlite.Quote("category"),
    sqlite.F("COUNT", sqlite.Raw("*")).As("count"),
    sqlite.F("AVG", sqlite.Quote("price")).As("avg_price"),
).From("products").
GroupBy(sqlite.Quote("category")).
Having(
    sqlite.Quote("count").GT(sqlite.Arg(5)),
)

WINDOW 関数 

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) as rank
FROM employees
sqlite.Select(
    sqlite.Quote("name"),
    sqlite.Quote("salary"),
    sqlite.F("RANK").Over().OrderBy(sqlite.Quote("salary").Desc()).As("rank"),
).From("employees")

ATTACH DATABASE 

ATTACH DATABASE ? AS backup
sqlite.Raw("ATTACH DATABASE ? AS backup", "/path/to/backup.db")

PRAGMA 情報の取得 

PRAGMA table_info(users)
sqlite.Raw("PRAGMA table_info(?)", "users")

これらの例は、Bob を使用して SQLite で実行可能な様々な SELECT クエリパターンを示しています。

InsertUpdate