CODEWITHSUNDEEP
ruby-on-railsactiverecord
Cat Named Dog
Cat Named Dog

Reputation: 1698

Ruby on Rails Active Record query joining two tables and query based on condition

I have two tables: Transactions and Properties.

I have one condition to satisfy that doesn't require joining tables.

On my Transactions query:

My next condition requires joining properties to transactions so that I can:

Basically, a new column was added to transactions called sold_or_leased and a lot of them are null. I need to an extra query to cover the null columns. 

    #test variables for month
    date = "2019-11-01"
    month = Date.parse date

    # below satisfies my first part
    @testobj = Transaction.where(sold_or_leased: "leased")
      .where("sales_date >= ? AND sales_date < ?", month.beginning_of_month, month.end_of_month).count

But now I need to extend this query to include properties and test a property column

I'm not sure where to go from here:

    @testobj = Transaction.joins(:property)
      .where(sold_or_leased: "leased")
      .where("sales_date >= ? AND sales_date < ?", month.beginning_of_month, month.end_of_month)
      .or(
        Transaction.where(sold_or_lease: nil)
      ).count

Also, when I add a join and then an or clause, i get an error Relation passed to #or must be structurally compatible. Incompatible values: [:joins]

I will share relevant model info:

Transaction Model:

class Transaction < ApplicationRecord
  belongs_to :user
  belongs_to :property
end

Property Model:

class Property < ApplicationRecord
  has_one :property_transaction, class_name: 'Transaction', dependent: :destroy
end

With the help of Sebastian, I have the following (which still produces the structural error message):

Transaction.joins(:property)
    .where(sales_date: month.all_month,
           sold_or_leased: nil,
           properties: { for_sale: false, for_lease: true })
    .or(
      Transaction.joins(:property)
      .where(sold_or_leased: "leased")
      .where("sales_date >= ? AND sales_date < ?", month.beginning_of_month, month.end_of_month)
    )

Upvotes: 1

Views: 540

Answers (1)

Sebasti&#225;n Palma
Sebasti&#225;n Palma

Reputation: 33420

In theory, you should be able to access the properties table columns after a join.

Looking to your current code and what you need to get you could try with:

Transaction
  .joins(:property)
  .where(sales_date: month.all_month)
  .where(
    "(sold_or_leased IS NULL AND properties.for_sale = false AND properties.for_lease = true) OR
     (sold_or_leased = 'leased')"
  )

If you're unable to use the ActiveRecord::QueryMethods#or, you can always use the SQL OR operator within a string argument to where.

Notice month.all_month produces the whole range of dates for a corresponding month, which when used with where is converted to the first and last day of the month:

SELECT ... WHERE "transactions"."sales_date" BETWEEN $1 AND $2 AND ... [["sales_date", "2019-11-01"], ["sales_date", "2019-11-30"]]

Shorter than the month.beginning_of_month and month.end_of_month variation.

Upvotes: 1

Related Questions