Rails order based on association doesn't work properly

0
votes

I’m trying to sort movies by release_date but it doesn’t work properly

@movies = Movie.where(id: movie_ids).recent

If I use the opposite scope (:old) it’s working but not with (:recent)

# Movie.rb

# Associations  
has_many :movie_countries, dependent: :destroy
has_many :countries, through: :movie_countries

# Scopes
scope :recent,    -> { includes(:movie_countries).order('movie_countries.release_date DESC') }
scope :old,       -> { includes(:movie_countries).order('movie_countries.release_date ASC') }

Example:

MovieCountry id: 1, movie_id: 1, release_date: '2018-01-01'
MovieCountry id: 2, movie_id: 2, release_date: '2019-01-01'
MovieCountry id: 3, movie_id: 3, release_date: '2020-01-01'

movies = Movie.recent
movies => [movie id: 1, movie id: 3, movie id: 2]

movies = Movie.old
movies => [movie id: 1, movie id: 2, movie id: 3]

As you can see, the :old scope works properly but not :recent

Backtraces with real data:

  • Recent:

    SELECT "movies"."id" AS t0_r0, "movies"."external_id" AS t0_r1, "movies"."original_title" AS t0_r2, "movies"."original_language" AS t0_r3, "movies"."imdb_id" AS t0_r4, "movies"."tmdb_id" AS t0_r5, "movies"."rentrak_film_id" AS t0_r6, "movies"."running_time" AS t0_r7, "movies"."status" AS t0_r8, "movies"."website" AS t0_r9, "movies"."backdrop_url" AS t0_r10, "movies"."created_at" AS t0_r11, "movies"."updated_at" AS t0_r12, "movie_countries"."id" AS t1_r0, "movie_countries"."country_id" AS t1_r1, "movie_countries"."movie_id" AS t1_r2, "movie_countries"."release_date" AS t1_r3, "movie_countries"."iso_code" AS t1_r4, "movie_countries"."created_at" AS t1_r5, "movie_countries"."updated_at" AS t1_r6 FROM "movies" LEFT OUTER JOIN "movie_countries" ON "movie_countries"."movie_id" = "movies"."id" WHERE "movies"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100, $101, $102, $103, $104, $105, $106, $107, $108, $109, $110, $111, $112, $113, $114, $115, $116, $117, $118, $119, $120, $121, $122, $123, $124, $125, $126, $127, $128, $129, $130, $131, $132, $133, $134, $135, $136, $137, $138, $139, $140, $141, $142, $143, $144, $145, $146, $147, $148, $149, $150, $151, $152, $153, $154, $155, $156, $157, $158, $159, $160, $161, $162, $163, $164, $165, $166, $167, $168, $169, $170, $171, $172, $173, $174, $175, $176, $177, $178, $179, $180, $181, $182, $183, $184, $185, $186, $187, $188, $189, $190, $191, $192, $193, $194, $195, $196, $197, $198, $199, $200, $201, $202, $203, $204, $205, $206, $207, $208, $209, $210, $211, $212, $213, $214, $215, $216, $217, $218, $219, $220, $221, $222, $223, $224, $225, $226, $227, $228, $229, $230, $231, $232, $233, $234, $235, $236, $237, $238, $239, $240, $241) ORDER BY movie_countries.release_date DESC

  • Old :

    SELECT "movies"."id" AS t0_r0, "movies"."external_id" AS t0_r1, "movies"."original_title" AS t0_r2, "movies"."original_language" AS t0_r3, "movies"."imdb_id" AS t0_r4, "movies"."tmdb_id" AS t0_r5, "movies"."rentrak_film_id" AS t0_r6, "movies"."running_time" AS t0_r7, "movies"."status" AS t0_r8, "movies"."website" AS t0_r9, "movies"."backdrop_url" AS t0_r10, "movies"."created_at" AS t0_r11, "movies"."updated_at" AS t0_r12, "movie_countries"."id" AS t1_r0, "movie_countries"."country_id" AS t1_r1, "movie_countries"."movie_id" AS t1_r2, "movie_countries"."release_date" AS t1_r3, "movie_countries"."iso_code" AS t1_r4, "movie_countries"."created_at" AS t1_r5, "movie_countries"."updated_at" AS t1_r6 FROM "movies" LEFT OUTER JOIN "movie_countries" ON "movie_countries"."movie_id" = "movies"."id" WHERE "movies"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100, $101, $102, $103, $104, $105, $106, $107, $108, $109, $110, $111, $112, $113, $114, $115, $116, $117, $118, $119, $120, $121, $122, $123, $124, $125, $126, $127, $128, $129, $130, $131, $132, $133, $134, $135, $136, $137, $138, $139, $140, $141, $142, $143, $144, $145, $146, $147, $148, $149, $150, $151, $152, $153, $154, $155, $156, $157, $158, $159, $160, $161, $162, $163, $164, $165, $166, $167, $168, $169, $170, $171, $172, $173, $174, $175, $176, $177, $178, $179, $180, $181, $182, $183, $184, $185, $186, $187, $188, $189, $190, $191, $192, $193, $194, $195, $196, $197, $198, $199, $200, $201, $202, $203, $204, $205, $206, $207, $208, $209, $210, $211, $212, $213, $214, $215, $216, $217, $218, $219, $220, $221, $222, $223, $224, $225, $226, $227, $228, $229, $230, $231, $232, $233, $234, $235, $236, $237, $238, $239, $240, $241) ORDER BY movie_countries.release_date ASC

1
sqlruby-on-railsrubyactiverecordsql-order-by
I don't see how that ORDER BY would produce incorrect results, there could be confusing results if NULLs were involved but nothing like the example. Could you show us some real data? Even just the release dates? – mu is too short
Try to change includes for joins. When you still have wrong order, it indicates the issues is in your data. Because provided "Example" is not reproducible and does not correspond to "Backtraces with real data". – Pavel Mikhailyuk
@PavelMikhailyuk it returns the same thing with joins. If I show you the result of the query you will only see returned movies and not the movie_countries (who has the release_date column) – Guillaume
Anyway, your "Example" is wrong: it returns right result on empty DB with 3 Movie and 3 corresponding MovieCountry. So, the issue is your data and no one can help you without it. I can just guess, that you have more than 1 MovieCountry per Movie with different release_dates. – Pavel Mikhailyuk
@PavelMikhailyuk you're right. The issue is that the movie had 2 movie_countries... 1 from France with an old release_date, and another one with a release_date for December. Thanks! – Guillaume

1 Answers

0
votes

I found an alternative to fix this issue :

# Movie.rb
scope :old,       -> {includes(:movie_countries).order('movie_countries.release_date ASC') }
scope :recent,    -> { old.reverse }

SOLUTION

Thanks to@PavelMikhailyuk 's answer I found the solution

# Movie.rb
scope :recent,    -> (iso_code = 'FR') { includes(:movie_countries).where(movie_countries: {iso_code: iso_code}).order('movie_countries.release_date DESC') }

The problem was that my movie had 2 movie_countries.

[#<MovieCountry id: 8, country_id: 2, movie_id: 5, release_date: "2018-12-10", iso_code: "GB", created_at: "2018-10-23 15:23:09", updated_at: "2018-10-23 15:23:09">, 
 #<MovieCountry id: 7, country_id: 1, movie_id: 5, release_date: "2018-10-24", iso_code: "FR", created_at: "2018-10-23 15:23:09", updated_at: "2018-10-23 15:23:09">]

I must search the movie_countries for a specific country AND THEN sort them.