3 models, joining and nested queries

Rails
1

I have 3 models,

class Host
  ref: string
  address: string,
  name: string
     primary_key='ref'
     has_many :rooms, :class_name=>"Room", :foreign_key=>'host_ref'

Class Room
  ref :string
  host_ref: string
  capacity: integer
     primary_key='ref'
     has_many :bookings, :class_name=>"Booking",

:foreign_key=>‘room_ref’
belongs_to :host

Class Booking
  ref: string
  room_ref :string
  start_date: date
  end_date: string
  number_of_guests :integer
     primary_key='ref'
     belongs_to :room

Here one should be able to see the vacancies against each host.
A person when he enters the start date, end date and number of persons
to
book for a room, he must get the list of hosts who have rooms vacant and
number of vacancies. How can I write the query in rails?

for example:

Suppose the records are
  Host
     ref       name         address
     host#1    Mr Allan     23, Camden Street
     host#2    Mr Tom       12, nassau Street
     host#3    Mr anice     34, Philip street

  Room
     ref       host_ref    capacity
     room#1    host#1      3
     room#2    host#2      1
     room#3    host#2      4
     room#4    host#3      2
     room#5    host#3      1
     room#6    host#3      2
     room#7    host#3      3

  Booking
     ref    room_ref    start_date    end_date       no_of_guests
     b#1    room#1      10-02-2013    20-02-2013     2
     b#2    room#2      05-02-2013    15-02-2013     1
     b#3    room#1      01-02-2013    13-02-2013     1
     b#4    room#3      15-02-2013    28-02-2013     1
     b#5    room#5      10-03-2013    15-03-2013     1
     b#6    room#7      15-02-2013    25-02-2013     1

I want to search for the number of vacancies against each host.
suppose I want to search as

start date: 15-02-2013   end date:18-02-2013    number of persons:2

I must get the result as:

host#2 Mr Tom       12, nassau Street
room#3  1 booked 3 free 4 total

host#3    Mr anice     34, Philip street
room#4  0 booked 2 free 2 total
room#6  0 booked 2 free 2 total
room#7  1 booked 2 free 3 total

Example 2: If I want to search as

start date: 01-02-2013 end date 13-02-2013  no of persons:1

I must get the result as

host#1    Mr Allan     23, Camden Street
room#1  2 booked  1 free  3 total

host#2 Mr Tom       12, nassau Street
room#2 0 booked 1 free 1 total

host#3
room#4 0 booked 2 free 2 total
room#5 0 booked 1 free 1 total
room#6 0 booked 2 free 2 total
room#7 1 booked 2 free 3 total

How can I achieve this result in rails? Can I get the query for this
or
loop?

2

Hey,
Test this code.
Call

Host.available(start_date,end_date)

hope it will help you.

3

Pardeep D. wrote in post #1099533:

Hey,
Test this code.
Call

Host.available(start_date,end_date)

hope it will help you.

But we search with number of persons.

4

On 26 February 2013 11:47, Sajeev Zacharias
[email protected] wrote:

  ref :string
  host_ref: string
  capacity: integer
     primary_key='ref'
     has_many :bookings, :class_name=>"Booking",

:foreign_key=>‘room_ref’
belongs_to :host

You will need a foreign_key spec on the above.

Class Booking
  ref: string
  room_ref :string
  start_date: date
  end_date: string

Why is end_date a string?

  number_of_guests :integer
     primary_key='ref'
     belongs_to :room

You will need a foreign_key spec here also.

Unless you are connecting to a legacy database that you /definitely/
cannot change then do not use unusual names for the keys, and do not
use string types. With Rails your life will be much simpler if you
stick to the Rails conventions.

Here one should be able to see the vacancies against each host.
A person when he enters the start date, end date and number of persons to
book for a room, he must get the list of hosts who have rooms vacant and
number of vacancies. How can I write the query in rails?

If you cannot see how to write the complete query then, for a start,
just do what you can in the query and do the rest in code. Make sure
that your automated tests check out the results and the tests pass.
Then, if necessary, you can refactor the code and queries to make it
more efficient, secure in the knowledge that your tests will show that
it is correct. If you cannot even see how to make a start then the
first thing is to write the requirement in pseudo code (something like
find hosts where …and …). Once you can write that down clearly and
unambiguously then you should be well on the way towards the solution.

Colin

5

So you can modified that given method with no of persons…