Comments (6)
Sorry, this is hopefully just lack of documentation (which I'm working on), so here goes:
If you write SQL by hand, you need to access its data by hand. If you want to use Expression objects, you must use Query objects to build the queries.
Join queries add complexity, especially when a table's column name collides with a column of the table it's being joined to. This means when when you join a table to itself, every column you reference is ambiguous. In the end, you need to disambiguate columns. You can do this by:
- Namespacing the column expressions, using
query[column]
(which becomesquery.column
). - For tables that join themselves, alias the query using
query.alias("alias_name")
, which is effectivelyquery AS alias_name
.
E.g., with your example as a guide:
let id = Expression<Int>("id")
let categoryid = Expression<Int>("categoryid")
let name = Expression<String>("name")
// etc.
let PDFs = db["pdf"]
let categories = db["categories"]
// all the above is usually defined once and only once, elsewhere
let query = PDFs.
select(PDFs[*], categories[id], categories[name], categories[thumb], /* etc. */).
join(categories, on: categories[id] == PDFs[categoryid]).
filter(PDFs[languageid] == language.id)
for row in query {
println("category name: \(row[categories[name]])")
}
There could be bugs lurking above, especially using table[*]
and then referencing its columns. Please report back if you have issues.
from sqlite.swift.
Great.
Thanks again Stephen
from sqlite.swift.
I'm writing some test cases for join row access right now. I'll try to patch up any bugs I find soon.
from sqlite.swift.
Row access is currently broken. You can access fields using their column name only (row[name]
instead of row[categories[name]]
), which means that duplicate column names will overwrite the previous result.
Unfortunately, iOS doesn't compile SQLite with SQLITE_ENABLE_COLUMN_METADATA
, which makes it a bit more difficult to disambiguate column names during statement execution:
https://www.sqlite.org/compile.html#enable_column_metadata
I'll look into tracking Query column names better internally, but for now consider this a bug.
from sqlite.swift.
Fixed by 7fa9a57
from sqlite.swift.
- If a
Query
callsselect
with specific column names, honor the namespacing or lack thereof explicitly.- If a
Query
callsselect
with a namespaced*
, honor the namespacing while expanding the columns.- If a
Query
joins another table and the select is the default,*
, namespace all expanded columns.- If a
Query
does not join another table, do not namespace expanded columns.
In other words, when you access row data during a join, either use select
to document exactly how you'll access that data, or always namespace row column during retrieval:
row[categories[name]] // not row[name]
from sqlite.swift.
Related Issues (20)
- bug in optional support for decoding HOT 1
- Version 0.15.0 Not Applied in CocoaPods HOT 10
- Swift Compiler Error when project depend SQLite.swift and sqlite3_flutter_libs HOT 1
- ncorrect checksum for freed object HOT 1
- Double quotes when using ULID HOT 1
- Unable to decode optional custom type HOT 2
- Sqllite
- Symbol not found: _$s6SQLite10ConnectionC15TransactionModeO8deferredyA2EmFWC
- Error: use of @import in framework header is discouraged, requires -fmodules
- SQLite.swift and SQLCipher HOT 1
- SQLiteCipher and visionOS support
- New SQL Coders HOT 1
- PrivacyInfo.xcprivacy HOT 2
- Will this lib consider `sendable`?
- Expression - name space conflict with Xcode 16/iOS 18 HOT 5
- Need Updated Framework on top of SQLITE3 Version 3.46.0
- assertionFailure happened in iterator
- Invalid bundle identifier when using SQLite.swift package + Testlight
- Backup, export, restore SQL database using AirDrop/Firebase HOT 1
- SQLCipher not an error (code: 21)
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlite.swift.