3

In my Rails 6 app I've got model Journey with defined active scope:

class Journey < ApplicationRecord
  has_many :users, through: :journey_progresses
  has_many :journey_progresses, dependent: :destroy

  scope :active, -> { where(is_deleted: false) }
end

In my endpoint I want to show user journey_progress and for serializer journey itself. To do so I'm using below query which works well

      def query
        current_user.journey_progresses.includes(:journey)
      end

How to modify above query to show only active journeys? I tried to just add where to be like current_user.journey_progresses.includes(:journey).where(is_deleted: false) but I'm getting an error:

Caused by PG::UndefinedColumn: ERROR:  column journey_progresses.is_deleted does not exist

According to this answer I tried with current_user.journey_progresses.includes(:journey).where(journey: {is_deleted: false} ) but I'm getting another error:

Caused by PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "journey"

What is the right syntax for that sort of action?

mr_muscle
  • 2,536
  • 18
  • 61

4 Answers4

5

You can merge a scope of a joined model.

current_user.journey_progresses.joins(:journey).merge(Journey.active)

This is better than testing for the is_deleted boolean because in future, if the definition of what makes a journey active changes, you won't need to modify the above line.

SteveTurczyn
  • 36,057
  • 6
  • 41
  • 53
  • Ok but won't that slow down the query? include is a neat solution besides, I'm afraid about N+1 query... – mr_muscle Aug 14 '20 at 14:06
  • It wont worked at all - I'm getting `[]` instead of list of journeys_progress – mr_muscle Aug 14 '20 at 14:25
  • @mr_muscle Before worrying about any N+1 performance issues, you should make your query work in the first place – claasz Aug 14 '20 at 14:32
  • My query works well without `is_deleted`. With your merge I gets nothing - `[]` – mr_muscle Aug 14 '20 at 14:38
  • Just checking... did you set a default of false when you created the column? You might have `nil` instead of `false` in the database. Try `scope :active, -> { where(is_deleted: [nil, false]) }` when you define the scope. – SteveTurczyn Aug 14 '20 at 17:42
  • Also, it's not doing N+1 query, it merges in the conditions from the second association, it doesn't run the second association. – SteveTurczyn Aug 14 '20 at 17:46
  • https://apidock.com/rails/ActiveRecord/SpawnMethods/merge – SteveTurczyn Aug 14 '20 at 17:47
2

You are misusing includes here. includes is only for optimization, to reduce number of queries going to the database.

Since you want to filter rows based on values in journey, you need to join it:

current_user.journey_progresses.joins(:journey)

As a general advice for these kind of problems I would recommend looking at the Rails log to see what SQL is generated at the end. Dealing with multiple has_many, scope, include, etc. can become confusing quickly.

UPDATE

You can actually combine joins with includes like this:

Foo.includes(:bars).where('bars.name = ?', 'example').references(:bars)

See https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-includes

claasz
  • 2,059
  • 1
  • 14
  • 16
  • This depends of the query. Sometimes active record WILL generate a join when you use `includes`. For example, in a CanCan app, the command `User.includes(:role).where(roles: {id:1}).to_sql` shows a LEFT JOIN in the console. I can't put the output query because of character limits. – cesartalves Aug 14 '20 at 13:55
  • Is there any other way to achieve that? Includes is used for easy load and joins works by lazy loading. So I think this is inappropriate. – mr_muscle Aug 14 '20 at 13:55
  • You mean *eager loading*? – claasz Aug 14 '20 at 14:29
2

Can you try the following?

current_user
  .journey_progresses.
  .joins(:journey)
  .where("journeys.is_deleted = ?", false)
Kumar
  • 3,116
  • 2
  • 16
  • 24
2
current_user.journey_progresses.includes(:journey).where(journey: {is_deleted: false} )

The above syntax is available from Rails 5.

You could try below possible ways,

  1. Adding custom association

In JourneyProgress Model

class JourneyProgress < ApplicationRecord
    belongs_to :active_journey, -> { where(is_deleted: false) }, class_name: 'Journey'
end

and include the association while querying.

    current_user.journey_progresses.includes(:active_journey)
  1. Adding default scope - But this scope will always be applied on querying from that model(https://apidock.com/rails/ActiveRecord/Base/default_scope/class)

default_scope -> { where(is_deleted: false) }

Aarthi
  • 1,451
  • 15
  • 39
  • I'm using Rails 6 and this query `current_user.journey_progresses.includes(:journey).where(journey: {is_deleted: false} )` won't worked. Failed with error: `ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "journey"` like described earlier. – mr_muscle Aug 14 '20 at 14:08
  • OK. Then you can try the methods that I have shared. – Aarthi Aug 14 '20 at 14:09
  • So based on shared code, will you use the deleted records and non deleted records in the app? – Aarthi Aug 14 '20 at 14:11
  • 1
    Yes, I will use both of them. – mr_muscle Aug 14 '20 at 14:19
  • So the journey progresses will be marked as deleted when the journey gets deleted? – Aarthi Aug 14 '20 at 17:03
  • 2
    @mr_muscle The reason the query doesn't work is when passing nested where attribute you must specify the name of the table, not of the association. `.where(journey: {...})` should be `.where(journeys: {...})`, table names are plural by default. – 3limin4t0r Aug 14 '20 at 22:04
  • @3limin4t0r you were right! it should be plural, now it works! – mr_muscle Aug 16 '20 at 17:29