@Selection macro uses positional, not named, field mapping

[Muhammed9991]

Description

When using the @Selection macro with a custom struct, the mapping appears to depend on the order of fields, not their names. This behavior isn't documented and could lead to subtle bugs if the field order in the struct doesn't exactly match the column order in the SQL query.

Example:

SELECT
   COUNT(CASE WHEN isArchived = 0 THEN 1 END) AS activeCount,
   COUNT(CASE WHEN isArchived = 1 THEN 1 END) AS archivedCount
FROM jobApplications;

@Selection
struct JobApplicationStatusCounts: QueryRepresentable {
    var activeCount: Int
    var archivedCount: Int
}

var jobApplicationStatusCounts = #sql("""
    SELECT 
    COUNT(CASE WHEN isArchived = 0 THEN 1 END) AS activeCount,
    COUNT(CASE WHEN isArchived = 1 THEN 1 END) AS archivedCount
    FROM jobApplications
""", as: JobApplicationStatusCounts?.self)

This works correctly.

However, swapping the struct field order like this:

struct JobApplicationStatusCounts: QueryRepresentable {
    var archivedCount: Int
    var activeCount: Int
}

...produces incorrect values (likely archivedCount = 5 and activeCount = 0).

Expected

• Field names should be used to map columns to struct fields (name-based mapping).
• Or, if positional mapping is the intended behaviour, this should be clearly documented as a potential "gotcha", since it contradicts what many developers might expect.

Checklist

• I have determined whether this bug is also reproducible in a vanilla SwiftUI project.
• If possible, I've reproduced the issue using the main branch of this package.
• This issue hasn't been addressed in an existing GitHub issue or discussion.

Expected behavior

No response

Actual behavior

No response

Reproducing project

No response

Structured Queries version information

0.1.1

Destination operating system

iOS 18

Xcode version information

Xcode 16.3

Swift Compiler version information

swift-driver version: 1.120.5 Apple Swift version 6.1 (swiftlang-6.1.0.110.21 clang-1700.0.13.3)
Target: arm64-apple-macosx15.0

[stephencelis]

Hi @Muhammed9991! This is actually working as expected, as all decoding in StructuredQueries is positional, and depends on you using the query builder to enforce this field ordering. If you want to still use SQL strings in the selection you can do so:

@Selection
struct JobApplicationStatusCounts: QueryRepresentable {
  var activeCount: Int
  var archivedCount: Int
}

let jobApplicationStatusCounts = JobApplication
  .select {
    JobApplicationStatusCounts.Columns(
      activeCount: #sql("COUNT(CASE WHEN \($0.isArchived) = 0 THEN 1 END)"),
      archivedCount: #sql("COUNT(CASE WHEN \($0.isArchived) = 1 THEN 1 END)")
    )
  }

Or you could use the count helper with a filter clause:

let jobApplicationStatusCounts = JobApplication
  .select {
    JobApplicationStatusCounts.Columns(
      activeCount: $0.count(filter: !$0.isArchived),
      archivedCount: $0.count(filter: $0.isArchived)
    )
  }

Since this isn't a bug in the library, I'm going to convert to a discussion.

[stephencelis]

@Muhammed9991 Do you have a suggestion as to where this should be documented? Was there a particular part of the docs you would have read?

FWIW the #sql macro is always going to introduce a little danger into a query and ideally you should start with the query builder. Is there a reason why you reached for #sql for the entire statement here?

[Muhammed9991]

Thanks for the reply, @stephencelis.

A few things from my experience as someone still learning both SQLite and this library:

• Initially, I wasn’t aware of the CASE WHEN support in StructuredQueries, so I used raw SQL. I remembered from the Structured Queries series that sometimes SQL is better suited for certain scenarios, and given the early stage of the library, I figured some things might not yet be documented.
• Eventually, I found the case syntax, but it took some digging. It might help to make that more discoverable or cross-referenced in the macro documentation.
• I also didn’t realise that StructuredQueries is positional. That only became clear after I first built my own initialiser and then discovered the @Selection macro — which auto-generates code assuming a specific argument order. Ironically, not knowing about the macro helped me understand the positional behaviour.
• It would be great if the documentation explicitly noted this positional behaviour when @Selection is first introduced — particularly under "Custom selections" in the Query Cookbook.
• Overall, I’m still getting a feel for when to rely on the query builder vs. using raw SQL. The #sql macro is powerful, but as a newcomer, I’m not always sure when it’s safe to use or when I might be better off sticking with the builder.

Hope this context is helpful — and happy to contribute doc edits if that would be useful!

[stephencelis]

• Initially, I wasn’t aware of the CASE WHEN support in StructuredQueries, so I used raw SQL. I remembered from the Structured Queries series that sometimes SQL is better suited for certain scenarios, and given the early stage of the library, I figured some things might not yet be documented.

It's definitely true that we think SQL strings are better for certain scenarios, but we recommend sticking with the builders for as long as possible and then introducing small #sql fragments into a larger query rather than using #sql to write entire queries. While you can write entire #sql statements, we consider this an advanced use for folks that are quite familiar with SQL and the affordances of the #sql macro.

• Eventually, I found the case syntax, but it took some digging. It might help to make that more discoverable or cross-referenced in the macro documentation.

I'm a little confused. Visiting the documentation should land you here:

https://swiftpackageindex.com/pointfreeco/swift-structured-queries/~/documentation/structuredqueriescore

Where Case already appears prominently in the sidebar under the "Expressions" section:

Can you explain how we could make this more discoverable?

• I also didn’t realise that StructuredQueries is positional. That only became clear after I first built my own initialiser and then discovered the @Selection macro — which auto-generates code assuming a specific argument order. Ironically, not knowing about the macro helped me understand the positional behaviour.

I think the main confusion here was trying to write a large #sql statement without retaining as much static information in the query as possible. For example, in our documentation on Safe SQL Strings we try to steer folks toward always interpolating static information from the macro-generated code wherever possible:

"""
SELECT \(Reminder.columns) FROM \(Reminder.self)  -- ✅
SELECT * FROM reminders                           -- ❌
"""

The positionality of the SELECT * query would have the same problem you encountered.

Our belief is that library users general shouldn't have to think about the positionality of the decoder when using the query builder or the static schema information, and we don't think folks should be writing queries without these tools. And given this, the only folks that should ever have to think about positionality of decoding are the folks writing their own decoders.

And while we really didn't intend for folks to use @Selection outside a builder, the static schema information applies here, as well. In this case had you done the following it would have worked just fine:

var jobApplicationStatusCounts = #sql("""
  SELECT \(JobApplicationStatusCounts.Columns(activeCount: …, archiveCount: …))
  FROM jobApplications
""", as: JobApplicationStatusCounts.self)

You are giving us food for thought on maybe more explicitly steering folks away from referencing anything in a #sql without using the static macro-generated schema definition, though. We just need to find the right places in the docs to do so.

• It would be great if the documentation explicitly noted this positional behaviour when @Selection is first introduced — particularly under "Custom selections" in the Query Cookbook.

Because we don't think folks should be using @Selection in the way you originally were above we're not sure it's appropriate to document how rows are decoded there, but if you disagree and would like to provide further info, we are all ears!

Whenever we do document this, it probably best belongs in QueryDecoder or QueryDecodable docs, which probably wouldn't have helped you here, but would help folks implementing custom decoders.

• Overall, I’m still getting a feel for when to rely on the query builder vs. using raw SQL. The #sql macro is powerful, but as a newcomer, I’m not always sure when it’s safe to use or when I might be better off sticking with the builder.

Hopefully the above context answers this point, but to recap:

• Always prefer a builder over an entire #sql statement.
• Introduce small #sql fragments into a larger builder whenever you begin to fight the builder's type system.
• Always interpolate to as much static information as possible into #sql.

Let us know if you have any further questions!

[stephencelis]

@Muhammed9991 Added a callout here: #41

[Muhammed9991]

Thanks — I think I may not have explained myself clearly enough on a couple of points.

In hindsight, the case syntax was easier to find than I initially thought. My first instinct was to look in the Query Cookbook, and when I didn’t see it there, I started digging into the code. I likely just overlooked it, but I still think it would be helpful to include a small example in the Cookbook — maybe in a dedicated “Advanced queries” section? That could serve as a useful reference point for situations where someone isn’t sure whether the builder can express a particular pattern.

And yes, I understand the recommendation to prefer the builder and avoid large #sql blocks — I’ll definitely keep that in mind. I’m also looking forward to the new Point-Free series to help build a better intuition for when to use each approach.

Thanks again for the reply — this has been really helpful! I’ll take a look at the PR

[stephencelis]

@Muhammed9991 The Query Cookbook isn't intended to be an exhaustive reference for query builder analogs to various SQL APIs (that's what the Expressions section is for). Query Cookbook is mainly to show off StructuredQueries-specific functionality for reusing query helpers.

[Muhammed9991]

Ahh fair enough. I think it being too down in the list. I initially completely ignored it. Just saw it.

Just for reference for people seeing this in future: