Can't generate relationship with "RelatedWhere" configuration

[absolutejam]

Hey, I've created a minimal reproduction for an issue I was experiencing trying to generate a relationship.

I've created 2 tables and wanted to reference the 'primary version' from the parent. In my example, I have a Locations with many LocationVersions, and one if them is the primary version.

https://github.com/absolutejam/bob-unused-err-repro

CREATE TABLE locations (
    id BIGINT NOT NULL PRIMARY KEY,
    public_id VARCHAR(12) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    deleted_at DATETIME,
    version INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE location_versions (
    id BIGINT NOT NULL PRIMARY KEY,
    public_id VARCHAR(12) NOT NULL UNIQUE,
    is_primary_version BOOLEAN NOT NULL DEFAULT(false),
    location_id BIGINT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    picture TEXT,
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    deleted_at DATETIME,
    version INTEGER NOT NULL DEFAULT 0,

    FOREIGN KEY (location_id) REFERENCES locations (id)
);

Here's the relevant section of my bob config:

aliases:
  location_versions:
    relationships:
      locations_primary_version: "PrimaryVersion"

relationships:
  locations:
    - name: "locations_primary_version"
      never_required: true
      sides:
        - from: "locations"
          to: "location_versions"
          columns:
            - [id, location_id]
          modify: "from"
          to_where:
            - column: "is_primary_version"
              sql_value: "true"
              go_value: "true"

I had to add the alias because otherwise it clashed with the generated FK relationship:

initializing aliases: relationship alias conflict for 'Location' in table 'location_versions': locations_primary_version conflicts with fk_location_versions_0

# location_versions.bob.go
func attachLocationVersionPrimaryVersion0(ctx context.Context, exec bob.Executor, count int, locationVersion0 *LocationVersion) (*LocationVersion, error) {
	setter := &LocationVersionSetter{
		IsPrimaryVersion: &true, # ❌ invalid operation: cannot take address of true (untyped bool constant)
	}

	err := locationVersion0.Update(ctx, exec, setter)
	if err != nil {
		return nil, fmt.Errorf("attachLocationVersionPrimaryVersion0: %w", err)
	}

	return locationVersion0, nil
}

func (locationVersion0 *LocationVersion) InsertPrimaryVersion(ctx context.Context, exec bob.Executor, related *LocationSetter) error {
	locationVersion0, err = attachLocationVersionPrimaryVersion0(ctx, exec, 1, locationVersion0) # ❌ undefined: err
	if err != nil {
		return err
	}

	location1, err := insertLocationVersionPrimaryVersion1(ctx, exec, related, locationVersion0)
	if err != nil {
		return err
	}

	locationVersion0.R.PrimaryVersion = location1

	location1.R.LocationVersion = locationVersion0

	return nil
}

# locations.bob.go
func insertLocationLocationVersion1(ctx context.Context, exec bob.Executor, locationVersion1 *LocationVersionSetter) (*LocationVersion, error) {
	locationVersion1.IsPrimaryVersion = &true # ❌ invalid operation: cannot take address of true (untyped bool constant)

	ret, err := LocationVersions.Insert(locationVersion1).One(ctx, exec)
	if err != nil {
		return ret, fmt.Errorf("insertLocationLocationVersion1: %w", err)
	}

	return ret, nil
}

func attachLocationLocationVersion1(ctx context.Context, exec bob.Executor, count int, locationVersion1 *LocationVersion) (*LocationVersion, error) {
	setter := &LocationVersionSetter{
		IsPrimaryVersion: &true, # ❌ invalid operation: cannot take address of true (untyped bool constant)
	}

	err := locationVersion1.Update(ctx, exec, setter)
	if err != nil {
		return nil, fmt.Errorf("attachLocationLocationVersion1: %w", err)
	}

	return locationVersion1, nil
}

func buildLocationVersionPreloader() locationVersionPreloader {
	return locationVersionPreloader{
		Location: func(opts ...sqlite.PreloadOption) sqlite.Preloader {
			return sqlite.Preload[*Location, LocationSlice](sqlite.PreloadRel{
				Name: "Location",
				Sides: []sqlite.PreloadSide{
					{
						From:        LocationVersions,
						To:          Locations,
						FromColumns: []string{"location_id"},
						ToColumns:   []string{"id"},
					},
				},
			}, Locations.Columns.Names(), opts...)
		},
		PrimaryVersion: func(opts ...sqlite.PreloadOption) sqlite.Preloader {
			return sqlite.Preload[*Location, LocationSlice](sqlite.PreloadRel{
				Name: "PrimaryVersion",
				Sides: []sqlite.PreloadSide{
					{
						From:        LocationVersions,
						To:          Locations,
						FromColumns: []string{"location_id"},
						ToColumns:   []string{"id"},
						FromWhere: []orm.RelWhere{
							{
								Column:   ColumnNames.LocationVersions.IsPrimaryVersion, # ❌ undefined: ColumnNames
								SQLValue: "`true`",
								GoValue:  "true",
							},
						},
					},
				},
			}, Locations.Columns.Names(), opts...)
		},
	}
}

I also unquoted "true" but that leads to a 1 in SQLite, and that just ends up with the same issue of trying to take the pointer to a literal (&1).

I'll see if I can fork and resolve this, but thought I'd share it here for visibility too.

For these kind of things, I assume the generator would want to have a static value, eg. var locationsPrimaryVersionToWhereValue = true and use that instead of the literal?

[stephenafamo]

This should be fixed by #556

However, your current configuration still results in invalid codegen. You should remove the modify: "from" line in the configuration.

[absolutejam]

Thanks for this.

I've tried it again, but I'm still getting some errors.

I know you said to remove the modify: "from" but that was the way I could get the codegen to produce a one-to-one relationship.

aliases:
  location_versions:
    relationships:
      locations_versions_details: "Details"
      locations_primary_version: "PrimaryVersionFor"
      # ⬆️💡Needed to stop the error while generating: 
      # initializing aliases: relationship alias conflict for 'Location' in table 'location_versions': fk_location_versions_1 conflicts with locations_primary_version

  locations:
    relationships:
      locations_primary_version: "PrimaryVersion"
      # ⬆️💡Needed to stop the error while generating: 
      # initializing aliases: relationship alias conflict for 'Location' in table 'location_versions': fk_location_versions_1 conflicts with locations_primary_version

  details:
    relationships:
      fk_details_0: "Blueprint"

relationships:
  locations:
    - name: "locations_versions_details"
      sides:
        - from: "location_versions"
          to: "details"
          columns:
            - [id, resource_id]
          to_where:
            - column: "resource_kind"
              go_value: "worldbuilding.LocationKind"
              sql_value: "TEXT"

    - name: "locations_primary_version"
      never_required: false
      sides:
        - from: "locations"
          to: "location_versions"
          columns:
            - [id, location_id]
          modify: "from"
          to_where:
            - column: "is_primary_version"
              go_value: "true"
              sql_value: "BOOLEAN"

LocationVersion.R.PrimaryVersionFor // *Location
Location.R.PrimaryVersion // *LocationVersion

However, some of the methods aren't generated correctly:

func (location0 *Location) InsertPrimaryVersion(ctx context.Context, exec bob.Executor, related *LocationVersionSetter) error {
	var err error

	location0, err = attachLocationPrimaryVersion0(ctx, exec, 1, location0, locationVersion1) // ❌ undefined: locationVersion1
	if err != nil {
		return err
	}

	locationVersion1, err := insertLocationPrimaryVersion1(ctx, exec, related)
	if err != nil {
		return err
	}

	location0.R.PrimaryVersion = locationVersion1

	locationVersion1.R.PrimaryVersionFor = location0

	return nil
}

I also have another relationship without the modify: "from" that is generating similar errors with a missing parameter:

    - name: "locations_versions_details"
      sides:
        - from: "location_versions"
          to: "details"
          columns:
            - [id, resource_id]
          to_where:
            - column: "resource_kind"
              go_value: "worldbuilding.LocationKind"
              sql_value: "TEXT"

But I'm not sure why these are being created on Location (location.bob.go) instead of LocationVersion.

func (location0 *Location) InsertResourceDetails(ctx context.Context, exec bob.Executor, related ...*DetailSetter) error {
	if len(related) == 0 {
		return nil
	}

	var err error

	details1, err := insertLocationResourceDetails0(ctx, exec, related, locationVersion0)  // ❌ undefined: locationVersion0
	if err != nil {
		return err
	}

	location0.R.ResourceDetails = append(location0.R.ResourceDetails, details1...)

	for _, rel := range details1 {
		rel.R.ResourceLocationVersion = location0 // ❌ cannot use location0 (variable of type *Location) as *LocationVersion value in assignment
	}
	return nil
}

func (location0 *Location) AttachResourceDetails(ctx context.Context, exec bob.Executor, related ...*Detail) error {
	if len(related) == 0 {
		return nil
	}

	var err error
	details1 := DetailSlice(related)

	_, err = attachLocationResourceDetails0(ctx, exec, len(related), details1, locationVersion0)  // ❌ undefined: locationVersion0
	if err != nil {
		return err
	}

	location0.R.ResourceDetails = append(location0.R.ResourceDetails, details1...)

	for _, rel := range related {
		rel.R.ResourceLocationVersion = location0  // ❌ cannot use location0 (variable of type *Location) as *LocationVersion value in assignment
	}

	return nil
}

[stephenafamo]

1. Can you share a schema of the details table?
2. The error will persist as long as you keep "modify: from". If you want to make it a to-one relationship, include a unique constraint on the location_id column

[absolutejam]

1. Can you share a schema of the details table?

CREATE TABLE details (
    id INTEGER NOT NULL PRIMARY KEY,
    chronicle_id INTEGER NOT NULL,
    public_id VARCHAR(12) NOT NULL UNIQUE,
    name TEXT NOT NULL,
    resource_kind TEXT NOT NULL,
    resource_id INTEGER NOT NULL,
    `values` JSON,
    blueprint_id INTEGER NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME,
    deleted_at DATETIME,
    version INTEGER NOT NULL DEFAULT 0,

    FOREIGN KEY (chronicle_id) REFERENCES chronicles (id),
    FOREIGN KEY (blueprint_id) REFERENCES detail_blueprints (id)
);

3. The error will persist as long as you keep "modify: from". If you want to make it a to-one relationship, include a unique constraint on the location_id column

location_id is NOT NULL and has a FK constraint (FOREIGN KEY (location_id) REFERENCES locations (id)) in my schema. Do I need to do anything else in the relationship definition to force this?

EDIT: I seem to be able to force the correct behaviour with:

constraints:
  location_versions:
    uniques:
      - name: "location_id_unique"
        columns: [location_id]

[stephenafamo]

Yes, but please note that while you can configure "virtual constraints", it is better to add the unique constraint on the location_id column of the location_versions table.

By using a virtual constraint, there is no actual guarantee of data integrity.

[stephenafamo]

Are you still facing issues?

[absolutejam]

I tried adding UNIQUE to the schema and it didn't change, and the FK should imply unique constraint anyway - perhaps this needs to be added in the column parsing?

I'm still facing issues in the generated methods such as InsertResourceDetails & AttachResourceDetails (as per #553 (comment)). I'm not really sure why it's trying to generate the methods on the Location instead of LocationVersion - and I don't think I've misconfigured anything, but I will re-check my config.

[stephenafamo]

I tried adding UNIQUE to the schema and it didn't change, and the FK should imply unique constraint anyway - perhaps this needs to be added in the column parsing?

I'll investigate this... A unique foreign key should imply a to-one by default.

I'm not really sure why it's trying to generate the methods on the Location instead of LocationVersion

When a relationship is defined, it also includes the "reverse" relationship by default. You can add no_reverse: true to the relationship config to prevent this. (Sorry this is missing in the documentation).

[absolutejam]

Ahhh, it was a config issue that I missed multiple times!

My model relationship looks like this:

Location -(many)-> LocationVersion -(many)-> Detail

I couldn't understand why Location was getting the methods InsertResourceDetail & AttachResourceDetails generated, but this should only be on LocationVersion.

But in my config, I accidentally added both relationships under locations key. When I checked the config, I only checked the from field 🤦

relationships:
  locations: # ❗This should be `location_versions` and the 2nd relationship should be under `locations` header
    - name: "locations_versions_details"
      sides:
        - from: "location_versions"
          to: "details"
          columns:
            - [id, resource_id]
          to_where:
            - column: "resource_kind"
              go_value: "worldbuilding.LocationKind"
              sql_value: "TEXT"

    - name: "locations_primary_version"
      never_required: false
      sides:
        - from: "locations"
          to: "location_versions"
          columns:
            - [id, location_id]
          modify: "from"
          to_where:
            - column: "is_primary_version"
              go_value: "true"
              sql_value: "BOOLEAN"

EDIT: Now it seems like my Location -(many)-> LocationVersion relationship is generating as a singular all of a sudden? For example, location.R.LocationVersion (*LocationVersion) which was previously location.R.LocationVersions ([]LocationVersion).

So it seems if I add the locations_primary_version relationship, it will overwrite any relationship from locations.id -> location_versions.location_id to be a one-to-many. If I remove it again, PrimaryVersion is a one-to-one again.