Convert a complex query in rails given a sql query

Question

I am struggling with converting a sql query in rails.

Background

I have 3 tables named bus,stop and schedule. Bus table has fields id, and name. Stop table has fields id, and name. Schedule table has fields id,bus_id,stop_id,arrival,and bustag.

This is the query i have in sql

select A.bus_id as busid, A.stop_id as source, A.arrival as atime, B.arrival as dtime from
(SELECT * from schedules as S where S.stop_id = #{startStopId}) A
inner join
(SELECT * from schedules as S where S.stop_id = #{endStopId}) B
on A.bustag = B.bustag
where A.arrival < B.arrival
and A.arrival > CURTIME();

In rails I have done this so far

@possible_buses = Schedule.where(stop_id: [startStopId,endStopId])

Now I want to do same as mysql query that is I want to further process this list of possible buses get list of buses at startStop who's bustag equals bustag of the buses at endStop where arrival time is less at startStop than arrival time at endStop.

I would highly appreciate if someone can help me out. I am not good in rails query and this would help me a long way.

Sample Tables

BusTable               StopTable       ScheduleTable
id Name                id Name         id bus_id stop_id arrival bustag 
1  ttc(inbound)        1  mall         1   1      1       3:00      1 
2  ttc(outbound)       2  home         2   1      2       3:15      1  
                       3  downtown     3   1      3       3:30      1  
                       4  uptown       4   1      4       3:45      1 
                                       5   1      1       3:15      2
                                       6   1      2       3:30      2
                                       7   1      3       3:45      2
                                       8   1      4       4:00      2
                                       9   2      4       2:55      3  
                                      10   2      3       3:10      3
                                      11   2      2       3:25      3
                                      12   2      1       3:35      3
                                      13   2      4       3:10      4
                                      14   2      3       3:20      4
                                      15   2      2       3:30      4
                                      16   2      1       3:45      4

Sample Query and Expected Output

For instance if user wants to go from the Mall at 2:30 to uptown Then the following ids should be returned from the schedule : -> 1,5. Since Both the Ids will get you the bus_id that take can you from the mall to uptown.

I Hope this is more clear. please feel free to ask for more information. Thanks.

it would be very helpfull if you illustrate your post with tables data samples and expacted result and/or sqlfiddle — Alex, May 02 '15 at 01:42
Hi , I have added more details @Alex. Please ask for more questions if you are not clear — , May 02 '15 at 02:23
Except active record, you can also execute raw sql like: `sql = "select * from blog"` `raw_data = ActiveRecord::Base.connection.execute(sql)` — coderz, May 02 '15 at 02:29
@Alex I posted the expected result so if someone wants to go from mall to uptown the expected answer should return schedule id is 1,5 because both those ids have bus_ids that can take you from mall to uptown. — , May 02 '15 at 02:42
@coderz can you please post it as answer or better yet even show me how to do this using rails convention i would be very thankful — , May 02 '15 at 02:49

score 0 · Answer 1 · answered May 02 '15 at 02:11

0

Here is a better query, which in theory should do the same thing.

I'll update my response once more details are provided.

SELECT
    start.bus_id busid, 
    start.stop_id source, 
    start.arrival atime, 
    stop.arrival dtime 
FROM
    schedules start
INNER JOIN
    schedules stop ON
        stop.bustag = start.bustag
        AND stop.arrival > start.arrival
WHERE
    start.stop_id = #{startStopId}
    AND stop.stop_id = #{endStopId})
    AND start.arrival > now();

answered May 02 '15 at 02:11

Jonathan

2,778
13
23

Hi Augwa i just added more information, Also i was wondering if you query is possible to do in Rails using active record query methods – May 02 '15 at 02:25
it should be possible as there's nothing crazy happening with this query. I'm not fluent in active record or rails so I can't give you a proper response beyond the sql query side of things. – Jonathan May 02 '15 at 02:27

score 0 · Answer 2 · edited May 23 '17 at 10:24

Sorry, it should be a comment but as soon as I did job for you

http://sqlfiddle.com/#!9/45f47/9

And I expect that you will explain what is wrong with your query or result I post this answer for future real response.

So as you can see in my fiddle your query (with small change to select A.id field) already returns 1,5 values that you expected. So what is wrong? what other result are you looking for?

select A.id, A.bus_id as busid, A.stop_id as source, A.arrival as atime, B.arrival as dtime from
(SELECT * from ScheduleTable as S where S.stop_id = 1) A
inner join
(SELECT * from ScheduleTable as S where S.stop_id = 4) B
on A.bustag = B.bustag
where A.arrival < B.arrival
and A.arrival > '2:30';

EDIT https://stackoverflow.com/a/15408419/4421474 here is solution how to run custom query with rails

I would like to do it in Rails without using sql – May 02 '15 at 02:47 — , May 02 '15 at 02:47

coderz · Accepted Answer · 2015-05-02T04:03:27.030

Other awnsers have provided the right SQL. So I only show how to execute raw SQL in Rails.

Rails supports not only active record, but also it allows executing raw SQL. It returns an array, each element in the array is a hash with all your selected columns as key and data as value. The returned array is just like which active record way returns.

Here is a sample:

# first establish connection, if not explicitly specify establish connection, it should use default configuration, which is config/database.yml
ActiveRecord::Base.establish_connection(
  :adapter => "mysql2",
  :host => "localhost",
  :port => 3306,
  :username => "myuser",
  :password => "mypass",
  :database => "somedatabase",
  :pool => 1,
  :timeout => 5000
)
sql = "select name, age from users where age < 30"
raw = ActiveRecord::Base.connection.execute(sql)
# raw is like [{"name" => "Tom", "age" => 28}, {"name" => "Bob", "age" => 26}]
raw.each(:as => :hash) do |row|
  puts row.inspect # row is hash, something like {"name" => "Tom", "age" => 28}
end

You can run rails runner 'puts ActiveRecord::Base.configurations.inspect' to check your default DB connection info.

Do you need to specify connection name host name and user name? — , May 02 '15 at 03:05

Convert a complex query in rails given a sql query

3 Answers3