Proper way to subquery on a join in ecto

Questions / Help
,
1

In short this was what I tried to do.

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

But soon found that …

 ** (Ecto.QueryError) ....query.ex:108: queries in joins can only have `where` conditions in query:

     from c in Clip,
       join: v0 in ^#Ecto.Query<from v in VideoAnalysis, where: v.id == ^7275, order_by: [desc: v.inserted_at], limit: 1>,
       join: v1 in assoc(v0, :video),
       where: v1.id == ^7275
       select: c

trying to figure out how I can query for all the records of a thing through an intermediate record.
IE say I have a VideoAnalysis record that belongs_to a video and has many clips.
How can I query for all the clips of a video analysis if all I know if the video id?

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

Q: How could I make it to where I can limit it to only the newest based on inserted at for VideoAnalysis
in short I’m trying to do all this in the Ecto Query with just one query
if that is even possible

2

:wave:

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 …

1 Like
3

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)>
4

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
    )
5

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
6

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

7

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
1 Like
8

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.

9

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.

10 
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
4 Likes
11

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
12

Awesome thank you. Yeah I guess I may have caused that to not work with leaving the extra join in that from clips.

1 Like