activerecord class from custom sql query

Question

Is it possible to create an activerecord class from a custom sql query or view ? It doesn't need to be editable. example:

class c
  select a.*, b.* from a, b where a.code = b.code
end

This example is a join where all the fields from both tables are resent, the one to one joins in activerecord only present fields from one table, the others are accessible through a.bs.fieldname. I would like them aal to be fields on the same level, thus in one class.

so that a.code, a.name and b.code, b.extra can be accessed as c.code, c.name, c.extra

Dmitry Lihachev · Accepted Answer · 2014-09-11 09:07:10Z

1

ActiveRecord will works with your views as with tables. So, firstly create custom view for your things

CREATE VIEW some_things AS (select a.*, b.* from a, b where a.code = b.code)

And then make ActiveRecord based class for accessing them (app/models/some_thing.rb)

class SomeThing < ActiveRecord::Base
end

You can access to your things as like as any other AR objects

p SomeThing.where(code: 'xxx-yyy').order(:name).limit(10).all

answered Sep 11, 2014 at 9:07

Dmitry Lihachev

5046 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steve Zelaznik Over a year ago

The drawback of this approach is that most RDBMS systems don't allow an index on a view. This means you're doing a linear scan to find the record you want.

Dmitry Lihachev Over a year ago

You always can create indexes on source tables for increasing VIEW performance. So, in this case you can create 2 indexes: a(code) and b(code). It will be good idea to add index by a(name) field.

Collectives™ on Stack Overflow

activerecord class from custom sql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related