Query with foreign keys in Ecto Models

Question

I have the following Ecto schemas:

schema "videos" do
    field :name, :string
    field :mpdName, :string
    field :urlPlayready, :string, size: 500
    field :urlWidevine, :string, size: 500
    field :urlFile, :string, size: 500
    field :custom_data, :string, size: 500
    field :drm_today, :boolean
    field :vote, :integer
    has_many :user_videos, VideoBackend.UserVideos
    has_many :users, through: [:user_videos, :user]
    timestamps
  end


schema "users"  do
   field :name, :string
   field :email, :string ,  [:primary_key]
   field :encrypted_password, :string
   field :password, :string, virtual: true
   has_many :user_videos, VideoBackend.UserVideos
   has_many :videos, through: [:user_videos, :video]
   timestamps   
end

schema "user_videos" do
  belongs_to :users, VideoBackend.User
  belongs_to :videos, VideoBackend.Video
  field :time, :float
  field :device, :integer
  timestamps
end

Now I want to make a query that select the user_videos given a specific "user" and a specific "video". In SQL I would write this simple query:

SELECT FROM user_videos WHERE user = "user" and video = "video"

I tried to write the following Ecto query but it does not work:

def single_userVideo(user,video) do
    from u in UserVideos,
    where: u.user == ^user and u.video == ^video
end

Do you know how I could solve it?

Gazler Gazler · Accepted Answer · 2016-01-11T08:31:20

If you are using has_many and belongs_to then you probably want to query based on the _id columns.

So the SQL would be:

SELECT FROM user_videos WHERE user_id = "user" and video_id = "video"

And the query:

def single_user_video(%{id: user_id}, %{id: video_id}) do
  from u in UserVideos,
  where: u.user_id == ^user_id,
  where: u.video_id == ^video_id
end

When where/3 is used more than once in a query it will use AND in the query.

where expressions are used to filter the result set. If there is more than one where expression, they are combined with an and operator. All where expressions have to evaluate to a boolean value.

As an aside, functions in Elxir are written in snake_case so single_userVideos should be single_user_videos

Query with foreign keys in Ecto Models

1 Answers