Structured syntax for join on json array column

[importRyan]

For a left join and selection, are there structured operator(s) for filtering on json-encoded array of foreign keys? (Or is Tagged/_CodableJSONRepresentation just missing a conditional conformance?)

Example to-many relationship:

@Table
struct Exercise {
	let id: Tagged<Self, String>
        ...
	@Column(as: [BodyRegion.ID].JSONRepresentation.self)
	var trained: [BodyRegion.ID]
}

@Table
struct BodyRegion {
	let id: Tagged<Self, String>
	var name: String
}

When composing a join and selection for a detail view, I had expected something like .leftJoin { ... } to operate on the @Column [BodyRegion.ID] as a collection:

...	X
region.id.in(exercise.trained)	Instance method 'in' requires that 'TableColumn<Exercise.TableColumns.QueryValue, Array<BodyRegion.ID>.JSONRepresentation>' (aka 'TableColumn<Exercise, _CodableJSONRepresentation<Array<Tagged<BodyRegion, String>>>>') conform to 'Sequence'
region.id.in(exercise.trained.map { $0.somehowConformToCollection() })	no obvious choice to create / cast a sequence
exercise.trained.contains(region.id)	contains is not available

@Selection
struct DetailScreenSelection: Identifiable, Sendable {
	var id: Exercise.ID { exercise.id }
	let exercise: Exercise
	@Column(as: [BodyRegion].JSONRepresentation.self) let trained: [BodyRegion]
}

Exercise
	.leftJoin(BodyRegion.all) { exercise, region in 
	      region.id.in(exercise.trained) // Error: TableColumn<Exercise, _CodableJSONRepresentation<Array<Tagged<BodyRegion, String>>>> does not conform to Sequence
	 }
	.select { exercise, region in
		Selection_Detail(
			exercise: exercise,
			regions: region.jsonGroupArray()
		)
	}

[mbrandonw]

Hi @importRyan, your tables are not modeled the correct way for SQL. A parent table does not hold onto all of its child values. Instead, the child table has a foreign key pointing to its parent:

@Table
struct Exercise: Identifiable {
  let id: String
}

@Table
struct BodyRegion: Codable {
  let id: String
  var name: String
  let exerciseID: Exercise.ID
}

Then you can define a @Selection that selects all exercises along with their trained body regions like so:

@Selection
struct DetailScreenSelection {
  let exercise: Exercise
  @Column(as: [BodyRegion].JSONRepresentation.self)
  let trained: [BodyRegion]
}

And then the query to select into this data type can be done simply as:

func query() {
  _ = Exercise
    .group(by: \.id)
    .leftJoin(BodyRegion.all) { $0.id.eq($1.exerciseID) }
    .select {
      DetailScreenSelection.Columns(
        exercise: $0,
        trained: $1.jsonGroupArray()
      )
    }
}

Note that you need a group(by:) clause in order to aggregate with jsonGroupArray.

I know it may seem weird to model things like this if you are used to SwiftData / Core Data, where parent models literally hold onto all of their child models, but it comes with a lot of benefits. You can see some examples of modeling schemas like this in the SharingGRDB demo apps, such as this one for the reminders app.

[importRyan]

Gotcha. I misinterpreted the @column documentation as adding a layer beyond normal for forming relationships.

Relationships go in a separate table: neither Reminder/Exercise nor Tag/BodyRegion store a reference each other; the ReminderTag table (aka RelationshipTable) has its rows modeling the lines pointing at each other to form (one/many–many/one) relationships.

[mbrandonw]

The ReminderTag join table is only necessary for many-to-many associations. If you want a one-to-many association you can have just a single foreign key in the child table.