thr3ads.net - Rails - 3 models, joining and nested queries [Feb 2013]

If this information is useful, please help other people find it:
Share via:

Sajeev Zacharias

2013-Feb-26 11:47 UTC

3 models, joining and nested queries

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?

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/YMbxBYzIiKsJ.
For more options, visit https://groups.google.com/groups/opt_out.

Pardeep Dhingra

2013-Feb-28 12:56 UTC

head link

Re: 3 models, joining and nested queries

Hey,
Test this code.
Call

Host.available(start_date,end_date)

hope it will help you.

Attachments:
http://www.ruby-forum.com/attachment/8183/testcode.tar.gz


-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit https://groups.google.com/groups/opt_out.

Sajeev Zacharias

2013-Feb-28 14:48 UTC

head link

Re: 3 models, joining and nested queries

Pardeep Dhingra 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.

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit https://groups.google.com/groups/opt_out.

Colin Law

2013-Feb-28 15:14 UTC

head link

Re: 3 models, joining and nested queries

On 26 February 2013 11:47, Sajeev Zacharias
<sajeev.zacharias4-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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
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
>
> 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?
>
> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To view this discussion on the web visit
> https://groups.google.com/d/msg/rubyonrails-talk/-/YMbxBYzIiKsJ.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit https://groups.google.com/groups/opt_out.

Pardeep Dhingra

2013-Feb-28 16:41 UTC

head link

Re: 3 models, joining and nested queries

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

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit https://groups.google.com/groups/opt_out.

Seemingly Similar Threads

Search for more seemingly similar threads

Rails - Feb 2013 - 3 models, joining and nested queries

3 models, joining and nested queries

Re: 3 models, joining and nested queries

Re: 3 models, joining and nested queries

Re: 3 models, joining and nested queries

Re: 3 models, joining and nested queries

Seemingly Similar Threads