Declare Rails model from raw sql query

Question

I have wrote one SQL function which takes one argument and return Table type.

SQL Function -

create or replace function get_some_data (p_val in number)
  return my_tab_type_coll pipelined as
begin
  FOR i in (select final_test_tb.*,  PERIOD_BETWEEN_TWO_DATES(
    FROM_TZ(CAST(from_date AS TIMESTAMP), 'UTC'),
    FROM_TZ(CAST(to_date AS TIMESTAMP), 'UTC') as period
  from (
    select dl.id, dl.trip_id, dl.address, dl.from_date, dl.avg_speed, dl.high_speed, 
      case when tmp = 2 and nvl(lead(avg_speed) over (order by received_at asc), 1) = 0
      then lead(to_date) over (order by received_at asc)
      else to_date end to_date, tmp
      from (
        select test.*, case when avg_speed <> 0 then 1
                       when nvl(lag(avg_speed) over (order by received_at asc), 1) <> 0 then 2
                       when nvl(lead(avg_speed) over (order by received_at asc), 1) <> 0 then 3
                       end tmp
          from  test
          where foo_id = p_val) dl
      where tmp is not null) final_test_tb
   where tmp in (1, 2)) loop
    pipe row(my_tab_type(i.id, i.foo_id,
 i.avg_speed, i.high_speed, i.distance, i.address_en, i.from_date, i.to_date));
  end loop;
  return;
end;

Here, I already created two types i.e row type - my_tab_type & table type -my_tab_type_coll

While fetching the records using sql command =>

select * from TABLE(get_some_data(10003));

Is it possible to construct rails model from this function? So that, it will be possible to apply filters and pagination for that model/table.

@SergioTulentsev - Please check. I have added SQL function which returns Table type. — kd12, Jun 21 '15 at 07:41
I fail to understand how that is supposed to return a function via a raw `return;`. Is that SQL that is that weird, or is it just me?.. The point is: try constructing the simplest possible function that returns something obvious. It's much easier to work on top of that. You can then replace that simple function with something more advanced and apply suggested solutions. — D-side, Jun 21 '15 at 09:47
ActiveRecord relies on table metadata to build its models (getting information from the database about column names, types, sizes, etc.) Your function just returns data, so I think no, you won't be able to use it with activerecord. — Sergio Tulentsev, Jun 21 '15 at 13:31

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

Possible? yes... but likely-a-good-idea? NO.

Active Record is a good match for a simple table.

A SQL function is not a good match and will likely be harder to integrate with Active Record then to just use "execute" - and iterating over the result sets.

Here's a StackOverflow question that might help with that: Rails 3 execute custom sql query without a model

edited May 23 '17 at 11:58

Community

1
1

answered Jun 22 '15 at 07:25

Taryn East

27,486
9
86
108

1

Possible to base a model on _that_? I'd like to know how. – Sergio Tulentsev Jun 22 '15 at 08:50
yes, thank you @Taryn East !! Currently, I am doing the same way as mentioned using sql function. – kd12 Jun 22 '15 at 13:23
@SergioTulentsev no you really wouldn't ;) If I was forced to find a way - I'd probably be using Active Model rather than Active Record... but you'd have to drag me kicking and screaming to do it. – Taryn East Jun 23 '15 at 00:43

score 0 · Answer 2 · answered Jun 22 '15 at 10:18

0

I would suggest a slightly different approach: instead of returning a "temp table", instead make it a real table, but add one column: a session-key or user-key.

If the table just exists, you can create the model. When you need the data, you additionally give a 'user-key' or 'session-key' so multiple sessions could be calculating stuff in the same table but will not interfere with each other.

This way, imho, you get the best of both worlds: your function can calculate data on the fly, but it inserts in a table, which acts as a temp table, but persists so your model can count on it.

answered Jun 22 '15 at 10:18

nathanvda

49,707
13
117
139

Does this solution affect the application performance some how? Using sql function, I can get the result by connection "execute" but without active-record model. – kd12 Jun 22 '15 at 13:20
Use the stored procedure (function) to fill the data, use active record to read it. Performance should be almost similar imho. You might have a little performance loss, but you will have more programmer happiness :) – nathanvda Jun 22 '15 at 14:14

Declare Rails model from raw sql query

2 Answers2