CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecord
colkas
colkas

Reputation: 585

Access JSON field of join table with Active Record

One shop has_many products and a product belongs_to a shop. The shop has opening_hours and closing_hours columns. Each column stores a hash which looks like the following:

opening_hours = {'Monday' => '12:00', 'Tuesday' => '13:00'}

and so on

I am working with current date and time, and I would like to retrieve all products which stores have opening_hours smaller than current time which is smaller than closing_hours. Basically all the products that are available when the shop is open.

I did something like that in my controller:

day_today = Date.today.strftime('%A')
time_now = Time.new.strftime('%H:%M')
@products = Product.joins(shop: [{opening_hours[day_today] < time_now, 
                                 {closing_hours[day_today] > time_now }])

Edit

This is the code that stores the opening times in the DB

hours_table = shop.search('table')
    opening_hours = {}
    closing_hours = {}
    hours_table.first.search('tr').each do |tr|
      cells = tr.search('td')
      day = cells.first.text.strip
      hours = cells.last.text.strip.tr('-', '').split
      opening_hours[day] = hours[0].to_time.strftime('%H:%M')
      closing_hours[day] = hours[1].to_time.strftime('%H:%M')
    end

shop = Shop.new(
        name: shop_name,
        opening_hours: opening_hours,
        closing_hours: closing_hours
      )
      shop.save

      new_product = Product.new(
        name: foo,
        description: foo,
        price: foo,
        company: 'foo',
      )
      new_product.shop = shop
      new_product.save

This is the migration to add opening hours to Shop. The same is done for closing hours.

class AddOpeningHoursToShop < ActiveRecord::Migration[5.1]
  def change
    add_column :shops, :opening_hours, :json, default: {}
  end
end

This is the migration to add the shop ID to products

class AddShopToProducts < ActiveRecord::Migration[5.1]
  def change
    add_reference :products, :shop, foreign_key: true
  end
end

Any guess?

Upvotes: 0

Views: 858

Answers (1)

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

Reputation: 33491

You can use the ->> operator to access a specific JSON field as text:

SELECT opening_hours->>'Monday' AS monday_opening_hour FROM shops;

Same for closing_hours, it just changes the name of the column and field.

So the AR representation in your case would be like:

Product
  .joins(:shop)
  .where(
    "shops.opening_hours->>:day <= :now AND shops.closing_hours->>:day >= :now",
    day: day_today,
    now: time_now
  )

You can easily bind the key name and the time you're going to ask for, here as day and now.

Notice, it's not needed to explicitly prepend the table name, but I'm doing so just for the sake of clarity.

This can also be done with the BETWEEN operator:

Product
  .joins(:shop)
  .where(
    ":now BETWEEN shops.opening_hours->>:day AND shops.closing_hours->>:day",
    day: day_today,
    now: time_now
  )

You can use the one you think is more clear.

In any case, you can get rid of storing the time_now variable, by using the NOW function of PostgreSQL. It'll handle the time format difference if you cast it to text (NOW()::text):

Product
  .joins(:shop)
  .where(
    "NOW()::text BETWEEN shops.opening_hours->>:day AND shops.closing_hours->>:day",
    day: day_today
  )

Similarly, the day can also be obtained from NOW. You can use to_char(NOW(), 'Day') for that

SELECT to_char(date, 'Day') AS day
FROM generate_series (NOW() - '6 days'::interval, NOW(), '1 day') date;

    day
-----------
 Thursday
 Friday
 Saturday
 Sunday
 Monday
 Tuesday
 Wednesday

So:

Product
  .joins(:shop)
  .where("NOW()::text BETWEEN shops.opening_hours->>(to_char(NOW(), 'Day'))
                          AND shops.closing_hours->>(to_char(NOW(), 'Day'))")

Upvotes: 1

Related Questions