Many_to_many extra columns

Questions / Help
1

Hi there,

I have done some research on this problem and can’t figure out why my associations are not working properly. First port of call was this post (and I think it has got me in the right direction):

My situation?

I have Media and I have Playlists, modelled with the following schemas:

defmodule Server.Playback.Playlist do

  use Ecto.Schema
  import Ecto.Changeset

  alias Server.Playback.Playlist
  alias Server.Playback.PlaylistMedia

  schema "playlists" do
    field :name, :string
    field :playback_mode, :string
    field :playlist_id, :binary_id
    timestamps()
    has_many :media, PlaylistMedia
  end

  @doc false
  def changeset(%Playlist{} = playlist, attrs) do
    playlist
    |> cast(attrs, [:name, :playback_mode, :playlist_id])
    |> validate_required([:name])
    |> unique_constraint(:name)
  end

end

defmodule Server.Playback.Playlist do

  use Ecto.Schema
  import Ecto.Changeset

  alias Server.Playback.Playlist
  alias Server.Playback.PlaylistMedia

  schema "playlists" do
    field :name, :string
    field :playback_mode, :string
    field :playlist_id, :binary_id
    timestamps()
    has_many :media, PlaylistMedia
  end

  @doc false
  def changeset(%Playlist{} = playlist, attrs) do
    playlist
    |> cast(attrs, [:name, :playback_mode, :playlist_id])
    |> validate_required([:name])
    |> unique_constraint(:name)
  end

end

And then I have a schema to represent the many_to_many relationship. Bear in mind that I can’t use many_to_many as it doesn’t return the extra columns that are in the following schema:

defmodule Server.Playback.PlaylistMedia do

  use Ecto.Schema
  import Ecto.Changeset

  alias Server.Playback.PlaylistMedia
  alias Server.Playback.Media
  alias Server.Playback.Playlist

  schema "playlist_media" do
    belongs_to :playlist, Playlist, [references: :playlist_id, primary_key: true]
    belongs_to :media, Media, [references: :media_id, primary_key: true]
    field :order, :integer, [primary_key: true]
    field :playlist_value, :integer
  end

  @doc false
  def changeset(%PlaylistMedia{} = playlist_media, attrs) do
    playlist_media
    |> cast(attrs, [:order, :playlist_value])
  end

end

When I try and access the media entries for a given playlist, I end up with the following error:

SELECT p0.`id`, p0.`playlist_id`, p0.`media_id`, p0.`order`, p0.`playlist_value`, p0.`playlist_id` FROM `playlist_media` AS p0 WHERE (p0.`playlist_id` = ?) ORDER BY p0.`playlist_id` [60]
[info] GET /api/media/
[debug] Processing with ServerWeb.MediaController.index/2
  Parameters: %{}
  Pipelines: [:api]
[debug] QUERY OK source="media" db=3.9ms decode=0.2ms queue=0.1ms
SELECT m0.`id`, m0.`media_id`, m0.`filename`, m0.`thumb_filename`, m0.`type`, m0.`bytes`, m0.`inserted_at`, m0.`updated_at` FROM `media` AS m0 []
[info] Sent 200 in 6ms
[info] Sent 500 in 125ms
[error] #PID<0.1784.0> running ServerWeb.Endpoint terminated
Server: localhost:4443 (https)
Request: GET /api/playlists/7f2bbd11-1ad8-4b48-b996-885df0e6fd2d
** (exit) an exception was raised:
    ** (Mariaex.Error) (1054): Unknown column 'p0.id' in 'field list'

So it appears to be trying to perform the correct query, but I don’t understand why its trying to grab p0.id when I don’t have that as part of my schema (and its not in my migration). Also its trying to select playlist_id twice… I did have this working with many_to_many, but I wasn’t able to get the extra columns “order” and “playlist_value” - which is why I have headed this way…

Cheers…

2

I think the reason it is using :id is because of the following schema attribute (from the docs):

@primary_key - configures the schema primary key. It expects a tuple {field_name, type, options} with the primary key field name, type (typically :id or :binary_id, but can be any type) and options. Defaults to {:id, :id, autogenerate: true}. When set to false, does not define a primary key in the schema unless composite keys are defined using the options of field.

I have not set this attribute - so it has always been the default of {:id, :id, autogenerate: true}… So I don’t get why many_to_many didn’t have a problem with it set with its default value… It also doesn’t explain why its trying to get playlist_id twice either