Question 364472 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Select by two conditions in has_many relation

I struggle with creating a performant select of the database.
Following case:

A booking has 2 attributes departure and arrival.
Both attributes are type date.
I want to select all Persons with bookings with departure: 1.month.ago and no Booking in the future.

Relation:

class Booking
   has_many :persons, class_name: 'Person', through: :stays
end

class Stay
  belongs_to :booking, class_name: 'Booking'
  belongs_to :person, class_name: 'Person'
end

class Person
   has_many :bookings, class_name: 'Booking', through: :stays
end

Because there are ~3.800.000 Persons in the database I can’t work with a selection by a ruby logic after selection.

I’ve tried some requests, but I do not get any result, even there are some.

Person.joins(:bookings).where('bookings.departure < ? AND NOT EXISTS (SELECT 1 FROM bookings WHERE arrival >= ?)', 1.month.ago, Time.zone.today).find_each(&:clear)

Is it possible to have such a Database request, or do I need to work with one case in active record and the others by a logic?

Person.joins(:bookings).where('bookings.departure < ?', 1.month.ago).find_each do |pers|
  pers.clear unless pers.bookings.where('arrival >= ?', Time.zone.today).exists?  
end
Tags:

2

Answers


  1. 0

    Does something like this work for you?

    valid_bookings, invalid_bookings = 
  Booking.partition do |booking|
    booking.departure < one_month_ago && 
    booking.arrival < Time.current
  end
    
Person.where( 
  id: valid_bookings.pluck(:person_id) - 
      invalid_bookings.pluck(:person_id)
)
    Login or Signup to reply.
  2. 0

    It appears you are just missing joining a join here:

    NOT EXISTS (SELECT 1 FROM bookings WHERE arrival >= ?)

    You will need to change this to

    NOT EXISTS (
  SELECT 1 
  FROM bookings 
  INNER JOIN stays ON stays.booking_id = bookings.id 
  WHERE arrival >= ? AND stays.person_id = people.id)

    We should be able to convert this query in the ActiveRecord/Arel as follows:

    Person.joins(:bookings)
  .where(bookings: {departure: ...1.month.ago})
  .where(
    Arel::Nodes::Not.new(
      Stay.select(1)
        .joins(:bookings)
        .where(
          Stay.arel_table[:person_id].eq(Person.arel_table[:id])
            .and(Bookings.arel_table[:arrival].gteq(Time.zone.today))
        ).arel.exists)
  )

    This should result in the following query

    SELECT 
  people.*
FROM 
  people 
  INNER JOIN stays ON stays.person_id = people.id
  INNER JOIN bookings ON bookings.id = stays.booking_id
WHERE 
  bookings.departure < '2024-02-16' 
AND 
  NOT (
    EXISTS (
      SELECT 1 
      FROM stays
        INNER JOIN [bookings] ON bookings.id = stays.booking_id 
      WHERE 
        stays.person_id = people.id 
        AND bookings.arrival >= '03-26-2024'))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search