Many-to-many relationship and pivot columns.

[hiendaovinh]

Hi, I'm trying to build a many-to-many relationship by customizing with this

relationships:
  users:
    - name: "users-organizations"
      sides:
        - from: "users"
          to: "organization_user"
          columns: [[id, user_id]]
        - from: "organization_user"
          to: "organizations"
          columns: [[organization_id, id]]

It works well by returning the related organizations for users but I want to access more fields on the organization_user table. Is there anyway to specify extra columns when code-gen the joins? The scan target should be the OrganizationJoined, not the original Organization struct because it contains more fields from organization_user table.

[daddz]

Couldn't you just select the organization_user table first and then join what ever you need from the organization and user tables?

E.g. models.OrganizationUsers.Query(...)

If that's not what you need a bit more context would be helpful like the structure of your tables and what kind of query you want to do.

[stephenafamo]

The complexity of trying to

If I understand correctly what you're trying to do, you have a few options:

1. Have two separate relationships.

1. One from users to organization_user
2. Another from organization_user to organizations

models.Users.Query(
  models.ThenLoadUserOrganizationUsers(
    sm.Columns(...), // filter the join table columns
    models.PreloadOrganizationUsersOrganization,
  ),
)

2. Use a view

Create a "Joined View" and define your relationship to the view.