Better Way to Write Large Sqls Inside Rails Models

Is it a good practice to directly write SQL query in rails?

It's only bad practice if you do it without understanding the alternatives.

That said there is rarely a reason to do this. The framework encapsulates it for you and the benefit is that you have to write less code. The other benefit is database independence. The more direct queries you write, the more likely you'll write something that will break when you switch database engines.

It is easy to test. If you are using the framework properly (i.e. optimizing ActiveRecord as you will find discussed in numerous articles) and still feel like your queries are too slow...you can always benchmark direct queries.

But not knowing how to do something using ActiveRecord associations is not a good reason to resort to direct SQL.

http://guides.rubyonrails.org/association_basics.html

Loading large data sets into a Rails application

I can think of six main items to consider, the last five relate to rails 'magic':

1. Speed. This is huge. Active Record, one-at-a-time inserts can take a second for each row. So that's a million seconds for a million rows - that's 11.5 DAYS which would give it a bad rap by many folks!

2. Validation. You'll need to make sure that the database enforces the same validations that you have in your models / existing data.

3. Timestamps. You need to update timestamps manually if you want to update created_at / updated_at the same way rails would

4. Counter Caches. You'll need to update counts manually.

5. ActiveRecord gems For example if you use acts_as_audited which lets you keep a record trail for data changes to Model records, you won't have that functionaity if you're outside ActiveRecord.

6. Business Logic at the Model Layer. Good programmers try to put functionality at the model (or higher) level when they can. This might include items like updating other data, sending emails, writing to logs, etc. This would not happen if ActiveRecord was not invoked.

Rails: Using greater than/less than with a where statement

Try this

User.where("id > ?", 200) 

Rails raw SQL example

You can do this:

sql = "Select * from ... your sql query here"
records_array = ActiveRecord::Base.connection.execute(sql)

records_array would then be the result of your sql query in an array which you can iterate through.

Modeling PostgreSQL's large objects in Rails

If using ActiveRecord that comes with Rails with one of its adapters, the only formal mapping of database type to Rails or Ruby type that happens is typically defined in the NATIVE_DATABASE_TYPES constant in the adapter which is returned via its native_database_types method. For PostgreSQL in Rails 3.2.x, that is in ActiveRecord::ConnectionAdapters::PostgreSQLAdapter which is here. So, for that adapter, the "binary" type in Rails maps to the "bytea" type in PG. For some types, you can override that database type that it maps to using a gem called activerecord-native_db_types_override. But, we want to use large objects, so...

Migrations

As Jim Deville noted in the comments, you can specify the custom typed column in the table like:

t.column :some_oid, 'blob_oid', :null => false

If you need to do even more that is non-standard, you can also use an execute("SQL GOES HERE;") to create the table using straight SQL. And, if you have an existing legacy schema or SQL changes that have been made outside of the migrations, consider using structure.sql (config.active_record.schema_format = :sql option in config/application.rb and then do: rake db:structure:dump).

Large Objects Read/Write/Check Length/Delete

Copied with some modifications to clarify, etc. from: https://github.com/diogob/carrierwave-postgresql/blob/v0.1.0/lib/carrierwave/storage/postgresql_lo.rb:

Updated: we can but don't need to put a begin before the lo_read/lo_write/lo_lseek and do lo_close in ensure block because per PG documentation "Any large object descriptors that remain open at the end of a transaction will be closed automatically." (thanks to Diogo for that info)

    require 'pg'

    ...

    def read
      (...).transaction do
        lo = connection.lo_open(identifier)
        content = connection.lo_read(lo, file_length)
        connection.lo_close(lo)
        content
      end
    end

    def write(file)
      (...).transaction do
        lo = connection.lo_open(identifier, ::PG::INV_WRITE)
        size = connection.lo_write(lo, file.read)
        connection.lo_close(lo)
        size
      end
    end

    def delete
      connection.lo_unlink(identifier)
    end

    def file_length
      (...).transaction do
        lo = connection.lo_open(identifier)
        size = connection.lo_lseek(lo, 0, 2)
        connection.lo_close(lo)
        size
      end
    end

Instead of connection, use the raw connection from the model or base, e.g. ActiveRecord::Base.connection.raw_connection (see this).

(...).transaction is calling transaction on model or base, e.g. ActiveRecord::Base.transaction (see this).

identifier is the oid that you either need to pass in/set or get from just doing a connection.lo_creat.

Other examples/info:

• http://rubydoc.info/github/nedforce/devcms-core/DbFile
• https://github.com/nedforce/devcms-core
• http://my.safaribooksonline.com/book/web-development/ruby/9780596510329/database/largebinary_objects

The latter and some answers here suggest that you might want to consider storage of large files separate from the DB, e.g. so that you can use cloud storage. But, if only store the paths/IDs to external files that are not managed by the DB, you lose ACID consistency (one or more DB records could point to one or more files that aren't there or one or more files could exist that don't have one or more associated records in the database). Another argument for storing files on the file system is that you can stream files, but PG large object stores files on the filesystem in a way managed by postgres to both ensure ACID consistency and allow streaming (which you can't do with a normal BLOB/Rails binary type). So, it just depends; some find storing in separate storage using path references a better option, and some prefer ACID consistency via Large Objects.

The Easy Way

Just use CarrierWave and carrierwave-postgresql.

Rails: Is Better many small tables or one big table?

In this particular case you are questioning about (which can be applied to similar data modeling), one table would be better. The way I'd design the model Animal is for it to have an attribute species for example.

Getting all dogs and cats would simply be:

Animal.where("species = 'dog' OR species = 'cat'")

Or you can use the Rails 5 or method like so

Animal.where(species: 'dog').or(Animal.where(species: 'cat'))

Take a look at Active Record query interface

Populate an active record collection with different SQLs on the same model in rails

From the sounds of it, you're looking for any Model with a type_id of either 1 or 2. In SQL, you would express this as an IN subclause:

SELECT * FROM models WHERE type_id IN (1, 2);

In Rails, you can pass an array of acceptable values to a where call to generate the SQL IN statement:

Model.where(:type_id => [1, 2])

---

Related Topics