Proper way to subquery on a join in ecto

Have you tried using subquery/2?

latest_video_analysis =
  from va in VideoAnalysis,
    where: va.id == ^video_id,
    limit: 1,
    order_by: [desc: :inserted_at]

from v in Video,
  join: va in subquery(latest_video_analysis), on: v.id == va.video_id,
  where: v.id == ^video_id

Not sure if it would even work …

I got that far, though I though it was the same thing I was already doing. That said it does not raise an error, though on the other hand it does not return expected results.

iex(24)> subset_query = from(va in VideoAnalysis,
...(24)>     where: va.id == ^video_id,
...(24)>     limit: 1,
...(24)>     order_by: [desc: :inserted_at]
...(24)>   )
#Ecto.Query<from v in VideoAnalysis, where: v.id == ^3,  order_by: [desc: v.inserted_at], limit: 1>
iex(25)>
nil
iex(26)> Repo.all(
...(26)>   from(c in Clip,
...(26)>       join: va in subquery(subset_query),
...(26)>       join: v in assoc(va, :video)
...(26)>   )
...(26)> )

[debug] QUERY OK source="clips" db=4.4ms queue=0.1ms
SELECT * FROM "video_analyses" AS v0 WHERE (v0."id" = $1) ORDER BY v0."inserted_at" DESC LIMIT 1) AS s1 ON TRUE INNER JOIN "videos" AS v2 ON v2."id" = s1."video_id" [3]
[]
iex(27)>

What’s video_id, why is it in both where clauses?

from(q in query,
      join:
        va in ^from(va in VideoAnalysis,
          where: va.id == ^video_id, # here
          limit: 1,
          order_by: [desc: :inserted_at]
        ),
      join: v in assoc(va, :video),
      where: v.id == ^video_id # and here
    )

But there is a catch in my case join: video_analysis in assoc(c, :video_analysis), would return multiple records.

How is that a catch. Given that a video can have multiple clips you would expect the same video analysis to appear with each clip that belongs to the video.

I’m assuming

Clip [0..*] -- [1] Video [1] -- [0..*] VideoAnalysis

Sorry trial and error copy and pasting

The idea is that I only know of a video but I want all the clips found on the newest video_analysis which belongs to the video. the issue is that there are many video_analysis that belong to a given video

What about a subquery in a lateral join then? Ah, it’s not necessary actually. What about this?

latest_video_analysis =
  from va in VideoAnalysis,
    where: va.video_id == ^video_id,
    limit: 1,
    order_by: [desc: :inserted_at]

from c in Clip,
  join: va in subquery(latest_video_analysis),
  where: c.video_analisis_id == va.id

the issue is that there are many video_analysis that belong to a given video

So you want each clip no more than once even if it is referenced by multiple video analyses (for the same video).

A subquery using distinct/3 on the video id could work.

the stack is like so.

VideoAnalysis:

• belongs_to :video
• has_many :clips

Video:

• has_many :video_analyses

Clip:

• belongs_to :video_analysis

I want all the clips found on the newest VideoAnalysis that belongs to video x.

newest_videoanalysis = 
  from va in VideAnalysis,
  order_by: [desc: va.inserted_at],
  where: va.video_id == ^video_id,
  limit: 1

from clips in Clips,
  join: nva in subquery(newest_videoanalysis), on: clips.video_analysis_id == nva.id

You are starting with a video_id - so ultimately the can only be 1 or 0 distinct video_ids - 0 being the case when you can’t find any VideoAnalyses, 1 if there is one or more Video Analyses.

So it seems to boil down to

• Give me all video clips if there is at least one Video analysis
• No clips if no Video analysis can be found

So your query could be phrased as:

• Give me all the Clips for video_id if there is at least one Video Analysis (… which seems odd)

Something along the lines of

• Given the set n latest Video Analyses
• Get me the distinct set of video_ids
• And get me the associated Clips

sounds much more likely

Never mind, so it was

Video [1] -- [0..*] VideoAnalysis [1] -- [1..*] Clip