1

I have this long sql query that I am making, but I need to make ActiveRecord calls on it and cant. Is their an easy way to convert sql to active record??

sql_query =  "SELECT k.id AS k_key_id, k.key, c.string, s.name as state, s.color, h.created_at, h.updated_at, l.id as l_language_id, l.language_code, u.id as user_id, u.email "
sql_query += "FROM keys k, cords c, histories h, languages l, users u, states s "
sql_query += "WHERE c.key_id = k.id "
sql_query += "AND c.language_id = l.id "
sql_query += "AND c.state_id = s.id "
sql_query += "AND h.key_id = k.id "
sql_query += "AND h.action = 'Created' "
sql_query += "AND h.user_id = u.id "
if search_parameters != ""
  sql_query += "AND (k.key LIKE '%#{search_parameters}%' "
  sql_query += "OR c.string LIKE '%#{search_parameters}%') "
end
sql_query += "LIMIT #{limit}"
sql_query += "OFFSET #{offset}"  

return ActiveRecord::Base.connection.execute(sql_query)

This returns an array of objects that look like:

{"k_key_id"=>"1",
      "key"=>"device.ios",
      "string"=>"this is an ios string",
      "state"=>"New",
      "color"=>"red",
      "created_at"=>"2015-08-05 19:57:21.342388",
      "updated_at"=>"2015-08-05 19:57:21.342388",
      "l_language_id"=>"1",
      "language_code"=>"en",
      "user_id"=>"2",
      "email"=>"blah@gmail.com"}

Which is exactly what I want. All this data in one spot, combined from all my tables.

Any help would be much appreciated!

user3591126
  • 211
  • 1
  • 8
  • Is your query actually doing what you want it to do, at the moment? – Max Williams Aug 11 '15 at 15:25
  • Yes. Everything works except I need to be able to make ActiveRecord calls with it. – user3591126 Aug 11 '15 at 15:30
  • What would you expect to get back? You're asking for fields from lots of different tables. Are you expecting to get back a collection of lots of different kinds of records? If you just need a collection of arrays of values then do `ActiveRecord::Base.connection.select_all(sql_query)` – Max Williams Aug 11 '15 at 15:38
  • Can you post your models? And what kind of calls would you like to make? – lunr Aug 17 '15 at 22:23

1 Answers1

0

Create models for your tables and use the active_record way to join your query:

http://guides.rubyonrails.org/active_record_querying.html#joining-tables

So looks something like

Keys.joins(:cords, :histories, ...).where(histories: {action: 'Created'}).limt(limit)...
Axel Tetzlaff
  • 1,355
  • 8
  • 11
  • Is another command besides joins available? I believe joins expects the tables to be related right? I have some unrelated tables that I want to join together. – user3591126 Aug 11 '15 at 15:46
  • You can specify the joins in SQL with that method as well. see: http://stackoverflow.com/questions/3245201/left-outer-joins-in-rails-3 – Axel Tetzlaff Aug 11 '15 at 15:52