Need help querying user object sharing permissions with a many_to_many association in ecto

[strickinato]

I’m having a hard time building out the queries and changesets for a sharing with permissions system using ecto.

What I want:

Let’s say there are users and objects, and I want users to be able to share objects with eachother with admin, write, and view permissions. Think, for example, the way you can share google docs with one another. I can share it with them just to view it, or to be able to edit it, etc.

How I modeled it:

I have it modeled so there’s a user table.
I have an object table.
And I have a join table user_objects, that has a user_id foreign key, a store_id foreign key, and a permissions column that is an enum of admin, edit, read.

What I have so far:

As a logged in user, the query for seeing all my tables was no problem. I have:

  def list_user_objects(%Accounts.User{} = user) do
    Objects.Object
    |> user_objects_query(user)
    |> Repo.all()
  end

  defp user_objects_query(query, %Accounts.User{id: user_id}) do
    from(object in query,
      join: user_objects in assoc(object, :user_objects),
      where: user_objects.user_id == ^user_id,
      select: %{ object | permission: objects.permission },
      preload: [:user_objects]
    )
  end

This includes in the ecto schema, a virtual field for the permission, so I’m able to access the permission level in the view.

  @primary_key {:id, Ecto.UUID, autogenerate: true}
  schema "object" do
    field :name, :string
    has_many :user_objects, Objects.UserObject
    many_to_many :users, Accounts.User, join_through: Objects.UserObject, on_replace: :delete

    # TODO: side question, How can I re-use this enum value in the UserObject schema as well?
    field :permission, Ecto.Enum, values: [admin: 1, edit: 2, read: 3], virtual: true

    timestamps()
  end

This all works great!!

Now my question:

When I’m querying the other way. That is, for the single object view, I want to list out all the users and what their permission levels are, I’m having trouble figuring out how to reasonably load the user information for each user that is associated through the UserObjects.

1. I think I could put a virtual field for permission on the Accounts.User schema and so a similar query to the one above.But that doesn’t really make sense. It feels wrong to do this because that attribute doesn’t really have anything to do with Accounts.User…

So might I create a second User struct that encapsulates this specific view of a user?

2. I thought, maybe I could preload the user on user_object, and in the template, access things by enumerating through the user_objects and accessing the user. eg.

    <%= for user_object <- @user_objects do %>
      <tr>
        <td><%= user_object.user.email %></td>
        <td><%= user_object.permission %></td>
      </tr>
    <% end %>
  </tbody>

But this doesn’t seem to work because Object doesn’t have a :user association, so I can’t seem to preload it.

  defp user_stores_query(query, %Accounts.User{id: user_id}) do
    from(store in query,
      join: user_stores in assoc(store, :user_stores),
      join: users in assoc(user_stores, :user),
      where: user_stores.user_id == ^user_id,
      select: %{ store | permission: user_stores.permission },
      # THIS DOESN'T WORK!
      preload: [:user_stores, :user]
    )
  end

3. I think, I probably could write multiple queries that gets all the information, and then in elixir merge the data together, but I feel like there must be an “ecto-y” way to do this!

Does anyone have any suggestions on how they might go about this?

[kokolegorille]

Hello and welcome,

I do not fully understand how your permissions work, in particular, I don’t see the role of store.

Something I would consider (without store) is

user has many permissions
user has many owned objects
permission belongs_to object
permission belongs_to user
object belongs_to creator
object has many permissions

I don’t see any difficulties to query from user, or object.

But probably because I don’t understand why You need to use virtual field, and what user_store is doing.

[strickinato]

Thanks for the response.

1. Dang! In my application, they are stores, but in the write up I changed things to object so it would be more general, but then at the very end there, I made a mistake and didn’t change that final query. It should be like this:

@strickinato

  defp user_objects_query(query, %Accounts.User{id: user_id}) do
    from(object in query,
      join: user_object in assoc(store, :user_objects),
      join: users in assoc(user_objects, :user),
      where: user_objects.user_id == ^user_id,
      select: %{ object | permission: user_objects.permission },
      # THIS DOESN'T WORK!
      preload: [:user_objects, :user]
    )
  end

As to your suggestion (ignoring all the “store” stuff) - it seems you understand the relational model perfectly.

I perhaps should have clarified, when thinking about SQL, this all isn’t necessarily a problem. Querying for objects on a users is not difficult, and querying for users via an object is similar.

Where I’m struggling is how I might translate what might normally be a fairly simple data access to Ecto and Elixir models in a reasonable / idiomatic way.

For example, in the user_objects_query defined above, in order to get this code to work:
select: %{ object | permission: objects.permission },, I had to create the virtual permission field on the Object schema, for the application not to complain to me that %Objects.Object can’t have a field called permission.

It seems possible to return plain maps from all the queries, but from what I can tell from online resources, the norm is to return domain models from queries.

So I’m trying to figure out how I can setup the ecto schemas properly to make both directions user ↔ object.

I hope that makes sense

[ruslandoga]

# THIS DOESN'T WORK!
      preload: [:user_objects, :user]

Regarding this part, you can force preload to use the results from join.

|> preload([o, uo, u], [user_objects: uo, user: u])

[strickinato]

Thanks!

I was able to load the user object using ecto’s keyword syntax by doing the following:

    from(object in query,
      join: user_objects in assoc(object, :user_objects),
      join: users in assoc(user_objects, :user),
      where: user_objects.user_id == ^user_id,
      select: %{ object | permission: user_objects.permission },

      # Preloading the association properly!
      preload: [user_objects: {user_objects, user: users}]
    )