Generating a LEFT JOIN via configured relationship

[oliviabarnett]

Hi!

I have two tables -- accounts and kyc_infos, each of which contains an account_id and app_id field. The account_id and app_id fields are a unique combination for both tables.

I wanted to do a LEFT JOIN when selecting from the accounts table, pulling in the related kyc_info row (if present). I followed the preload instructions here, but ended up creating a relationships configuration in my bobgen.yaml that was backwards:

relationships:
  accounts:
    - name: "accounts_to_kyc_info"
      never_required: true
      sides:
        - from: "accounts"
          to: "kyc_info"
          columns:
            - [account_id, account_id]
            - [app_id, app_id]
      modify: "from"

I had to change it to:

  kyc_info:
    - name: "kyc_info_to_accounts"
      never_required: true
      sides:
        - from: "kyc_info"
          to: "accounts"
          columns:
            - [account_id, account_id]
            - [app_id, app_id]
      modify: "from"

That allowed this to work:

	q := dbmodels.Accounts.Query(
		ctx, r.executor,
		sm.OrderBy(dbmodels.AccountColumns.CreatedAt).Desc(),
		sm.OrderBy(dbmodels.AccountColumns.ID).Desc(),
		// Fetch one more than the limit to check if there's a next page
		sm.Limit(limit+1),

		dbmodels.PreloadAccountKycInfo(),
	)
        dbAccounts, err := q.All()

I think it would be super helpful if the configuration file was also included in the example -- its not clear what the relationship between jets and pilots is to me and led to my setting this up incorrectly.

It's also not really clear to me why I have to set up both directions in the first place -- I would kind of think just one defined relationship would work both ways?

[stephenafamo]

Can you share a rough schema for these tables, including any primary key or unique constraints?

(PS: I think if you left out the modify attribute, the preload would work with the first configuration)

[jalaziz]

PreloadAccountKycInfo() doesn't get generated unless we use the second flipped configuration. However, this is not clear from the docs.

For example, for this example, it seems like the configuration should be similar to:

relationships:
  pilots:
    - name: "pilots_to_jets"
      never_required: true
      sides:
        - from: "pilots"
          to: "jets"
          columns:
            - [id, pilot_id]

rather than:

relationships:
  jets:
    - name: "jets_to_pilots"
      never_required: true
      sides:
        - from: "jets"
          to: "pilots"
          columns:
            - [id, pilot_id]
    - name: "jets_to_copilots"
      never_required: true
      sides:
        - from: "jets"
          to: "pilots"
          columns:
            - [id, copilot_id]

Or would we just need to define multiple sides and flip from/to?

[stephenafamo]

I'll take a closer look at this.

Preload mods should be generated for both sides since it is a one-to-one relationship.

[jalaziz]

@stephenafamo Any chance you had a chance to look into this and why both sides are not being generated?

[stephenafamo]

I figured out the issue.

The unique indexes are not discovered. The query we have currently only checks for unique constraints, not unique indexes... I'll fix this in the next update.

[stephenafamo]

Should be fixed by #303

No need to mess around with the modify parameter at all