CODEWITHSUNDEEP
ruby-on-railsactiverecord
Andrew Zelenets
Andrew Zelenets

Reputation: 59

How to write complex query in Ruby

Need advice, how to write complex query in Ruby.

Query in PHP project:

    $get_trustee = db_query("SELECT t.trustee_name,t.secret_key,t.trustee_status,t.created,t.user_id,ui.image from trustees t 
                             left join users u on u.id = t.trustees_id
                             left join user_info ui on ui.user_id = t.trustees_id
                             WHERE t.user_id='$user_id' AND trustee_status ='pending'
group by secret_key
ORDER BY t.created DESC")

My guess in Ruby:

get_trustee = Trustee.find_by_sql('SELECT t.trustee_name, t.secret_key, t.trustee_status, t.created, t.user_id, ui.image FROM trustees t
        LEFT JOIN users u ON u.id = t.trustees_id
        LEFT JOIN user_info ui ON ui.user_id = t.trustees_id
        WHERE t.user_id = ? AND
              t.trustee_status = ?
        GROUP BY secret_key
              ORDER BY t.created DESC',
              [user_id, 'pending'])

Upvotes: 1

Views: 476

Answers (2)

WattsInABox
WattsInABox

Reputation: 4636

Option 1 (Okay)

Do you mean Ruby with ActiveRecord? Are you using ActiveRecord and/or Rails? #find_by_sql is a method that exists within ActiveRecord. Also it seems like the user table isn't really needed in this query, but maybe you left something out? Either way, I'll included it in my examples. This query would work if you haven't set up your relationships right:

users_trustees = Trustee.
    select('trustees.*, ui.image').
    joins('LEFT OUTER JOIN users u ON u.id = trustees.trustees_id').
    joins('LEFT OUTER JOIN user_info ui ON ui.user_id = t.trustees_id').
    where(user_id: user_id, trustee_status: 'pending').
    order('t.created DESC')

Also, be aware of a few things with this solution:

  1. I have not found a super elegant way to get the columns from the join tables out of the ActiveRecord objects that get returned. You can access them by users_trustees.each { |u| u['image'] }
  2. This query isn't really THAT complex and ActiveRecord relationships make it much easier to understand and maintain.
  3. I'm assuming you're using a legacy database and that's why your columns are named this way. If I'm wrong and you created these tables for this app, then your life would be much easier (and conventional) with your primary keys being called id and your timestamps being called created_at and updated_at.

Option 2 (Better)

If you set up your ActiveRecord relationships and classes properly, then this query is much easier:

class Trustee < ActiveRecord::Base
  self.primary_key = 'trustees_id' # wouldn't be needed if the column was id
  has_one :user
  has_one :user_info
end

class User < ActiveRecord::Base
  belongs_to :trustee, foreign_key: 'trustees_id' # relationship can also go the other way
end

class UserInfo < ActiveRecord::Base
  self.table_name = 'user_info'
  belongs_to :trustee
end

Your "query" can now be ActiveRecord goodness if performance isn't paramount. The Ruby convention is readability first, reorganizing code later if stuff starts to scale.

Let's say you want to get a trustee's image:

trustee = Trustee.where(trustees_id: 5).first
if trustee
  image = trustee.user_info.image
  ..
end

Or if you want to get all trustee's images:

Trustee.all.collect { |t| t.user_info.try(:image) } # using a #try in case user_info is nil

Option 3 (Best)

It seems like trustee is just a special-case user of some sort. You can use STI if you don't mind restructuring you tables to simplify even further.

This is probably outside of the scope of this question so I'll just link you to the docs on this: http://api.rubyonrails.org/classes/ActiveRecord/Base.html see "Single Table Inheritance". Also see the article that they link to from Martin Fowler (http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html)

Resources

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

Upvotes: 4

Manoj Sehrawat
Manoj Sehrawat

Reputation: 1303

Yes, find_by_sql will work, you can try this also:

Trustee.connection.execute('...')

or for generic queries:

ActiveRecord::Base.connection.execute('...')

Upvotes: 0

Related Questions