CODEWITHSUNDEEP
ruby-on-railsactiverecordnullincludewhere-clause
GrowthCode
GrowthCode

Reputation: 55

ActiveRecord - "includes" & "where" gettting Nil and Not Nil Statement, with other attributes

I want to efficiently get the results using '.includes()'.

I need the includes Table to retrieve the records that are NOT nil. I need to keep the included: true in the query.

Using postgres.

Role has many tasks.

Tasks belongs to a Role.

The following works but gets the wrong records from the Roles table.

Task.includes(:role).where(included: true, roles: {doer: nil})

The next part is the IDEA of what I want... but obviously syntax is wrong in the where clause's "roles" value.

Task.includes(:role).where(
    included: true,
    roles: {
        doer != nil    #=> where 'doer' is not 'nil'
    })

I'm looking for an efficient query which is why i did the includes. I don't know how to get this without multiple where queries.

If you understand the question but think it could be asked better to be more clear, let me know. I couldn't any clues for this answer anywhere unless multiple where statments are used.

Upvotes: 1

Views: 3690

Answers (3)

RubeOnRails
RubeOnRails

Reputation: 1163

I prefer to avoid strings when possible, so I'd use the following:

Task.includes(:role).where(included: true).where.not(roles: { doer: nil })

If you have specifically set the included column on the tasks table to have NULL FALSE, you could also condense the where calls into a single call, although this will still only launch one query:

Task.includes(:role).where.not(included: false, roles: { doer: nil })

Personally, I'd like to see this cleaned-up a bit with some scopes, providing that these calls are commonplace. Something like:

scope :with_doer, -> { includes(:role).where.not(roles: { doer: nil }) }

so the resulting code would be more readable:

Task.with_doer.where(included: true)

You could obviously extend this pattern to the included: true bit as well.

Note that ActiveRecord queries are built up and then kicked to the database with a "kicker" which Rails kind of sneakily and hackily does through #inspect or #to_a most of the time. So, you don't need to worry about needing to condense the wherecalls into a single call.

Upvotes: 2

trh
trh

Reputation: 7339

What about something like:

Task.includes(:roles).where.not('roles.doer' => nil)

This is a rails 4-and-up convention, for rails 3 it would be something like:

Task.includes(:roles).where("roles.doer IS NOT NULL")

And you don't need the included attribute, it can be removed from the model for these purposes.

Since you do seem to need included (oops)

Task.includes(:roles).where('tasks.included' => true, 'roles.doer' => !nil)

Aaah.. how i love postgres.. When you're asking for efficiency, I think this fails, but I'm returning correct results. If you benchmark the options, this is correct (I think) but slow.

Task.joins('LEFT OUTER JOIN "roles" ON roles.user_id = user_id').where("tasks.included IS true AND roles.doer IS NOT NULL")

Upvotes: 2

Matt16749
Matt16749

Reputation: 119

Write SQL statements like this?

Task.includes(:role).where("included = 'true' AND roles.doer NOT 'nil'")

Upvotes: 0

Related Questions