Extending raw queries with optional where clause

rubyonrails-talk
1

I know there are good libraries like ransack that work excellently with active record, but we are working with a database that is not generated using active record. We are only allowed to read. I want to know the best possible approach to deal with the following problem.

Lets say I have this query.

SELECT * from users where deleted is null limit 100;

It is the default query for first page, but we do have a search field that may add another condition in where clause like

SELECT * from users where deleted is null and first_name like '%jason%' limit 100;

I am adding this first_name condition in hackiest way and I don’t like it. Is there any gem avaialble that would allow me to add this condition smoothly like

query = SQL(SELECT * from users where deleted is null limit 100;) query. where(’ first_name like ‘%jason%’‘’)

2

You can use ActiveRecord directly to create that query:

User.where("first_name LIKE ?" , "%#{first_name}%")

Do you want to combine those two queries ?

3

This would mean I have a model User. The database is not created using active records. It is totally external, we have more than 100 tables to query. To answer your question. Yes. I have default query as SELECT * from users where deleted is null limit 100

and I want to extend it as much as possible with more where conditions.

4

You can absolutely have an ActiveRecord-based model on a table that you don’t have write access to. You get all the goodies of AR with Arel-based Ruby query methods, and you don’t need to worry about that table not having been set up with ActiveRecord in mind. You can even mark the model as read-only if you want to be sure that you won’t try to save something there “accidentally”.

First thing, define the table name and primary key:

class Widget < ApplicationModel
  self.table_name = 'awful_legacy_db_table_name'
  self.primary_key = 'widget_id'
  ...

That lets you call Widget.find(123) and get the value you expect.

Use the Attribute API to declare that deleted “boolean” as such:

attribute :deleted, :boolean, default: false

You can define your default query as a scope (even a default_scope, although those are very sharp knives you don’t want loose in the spoon drawer).

scope :active, -> { where(deleted: false) }

And then you can chain scopes and further where clauses and limits as you like.

Widget.active.where('name like ?', "%super%").limit(5)

Another way to approach this legacy table is to use something like the Scenic gem to create a view in your database, set up along the ActiveRecord lines, that maps the legacy table into a shape that conforms to Rails’s conventions. That can give you a lot of flexibility, even to the point of combining multiple legacy tables into a single view with just the bits you need from each.

Walter

5

If the database isn’t created using Rails migrations and the tables doesn’t have their relevant models, then you’ll need to create the model for each table which you want to use in your Rails app.

For eg. if you’ve a legacy_users table then you can define a model as such:

class User < ActiveRecord::Base
  self.table_name = 'legacy_users'
  self.primary_key = 'id'
end

Then you can use this model using ActiveRecord queries like:

User.where(deleted: nil).where("first_name LIKE ?" , "%#{first_name}%")

provided that deleted and first_name are columns defined in legacy_users table.