Order by Count of Association (Select, Where, IN, Count)

[Dan Van Brunt]

I have the raw SQL query. I'm trying to see if there is a way to write the same using either 100% Sequelize or part and part literal.

SELECT * FROM "Video" WHERE "Video".id IN (

        SELECT "VideoTag".video_id 

        FROM "VideoTag" 

        WHERE "VideoTag".video_id!=@item 

        GROUP BY "VideoTag".video_id 

        ORDER BY COUNT(CASE WHEN "VideoTag".tag_id IN (

                SELECT "VideoTag".tag_id 

                FROM "VideoTag" 

                WHERE "VideoTag".video_id=@item) 

                THEN 1 END) DESC);

Basically, it a bunch of Videos that have a many-to-many with Tags. I will pass in a video_id and expect that this query will return all the videos that have Tags that this video does, while ordering them, videos with more similar tags at the top.