Rails raw SQL example

Question

How can I convert this code to raw sql and use in rails  Because When I deploy this code in heroku there is a request timeout error I think this will be faster if I use raw sql    payments   PaymentDetail joins  project  order  payment details created at desc    payment errors   PaymentError joins  project  order  payment errors created at desc     all payments     payments    payment errors

User · Answer

I want to work with exec query of the ActiveRecord class  because it returns the mapping of the query transforming into object  so it gets very practical and productive to iterate with the objects when the subject is Raw SQL   Example   values   ActiveRecord  Base connection exec query  select   from clients   p values   and return this complete query      id   1   name    user 1      id   2   name    user 2      id   3   name    user 3      To get only list of values  p values rows    1   user 1     2   user 2     3   user 3      To get only fields columns  p values columns    id    name

User · Answer

You can also mix raw SQL with ActiveRecord conditions  for example if you want to call a function in a condition   my instances   MyModel where not attribute a  nil       where  crc32 attribute b        slot       select  id

User · Answer

I know this is old    But I was having the same problem today and found a solution   Model find by sql   If you want to instantiate the results   Client find by sql     SELECT   FROM clients   INNER JOIN orders ON clients id   orders client id   ORDER BY clients created at desc        gt    lt Client id  1  first name   Lucas   gt    lt Client id  2  first name   Jan  gt          Model connection select all  sql   to hash   If you just want a hash of values   Client connection select all  SELECT first name  created at FROM clients    WHERE id    1    to hash     gt        first name   gt  Rafael    created at   gt  2012-11-10 23 23 45 281189        first name   gt  Eileen    created at   gt  2013-12-09 11 22 35 221282       Result object   select all returns a result object  You can do magic things with it   result   Post connection select all  SELECT id  title  body FROM posts     Get the column names of the result  result columns     gt    id    title    body      Get the record values of the result  result rows     gt    1   title 1    body 1           2   title 2    body 2                        Get an array of hashes representing the result  column   gt  value   result to hash     gt     id    gt  1   title    gt   title 1    body    gt   body 1            id    gt  2   title    gt   title 2    body    gt   body 2                        ActiveRecord  Result also includes Enumerable  result each do  row    puts row  title           row  body   end   Sources    ActiveRecord -  Findinig by SQL  Ruby on Rails -  Active Record Result

User · Answer

You can do this  sql    quot Select   from     your sql query here quot  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

User · Answer

You can execute raw query using ActiveRecord  And I will suggest to go with SQL block   query    lt  lt -SQL    SELECT      FROM payment details   INNER JOIN projects            ON projects id   payment details project id   ORDER BY payment details created at DESC SQL  result   ActiveRecord  Base connection execute query

User · Answer

You can do direct SQL to have a single query for both tables  I ll provide a sanitized query example to hopefully keep people from putting variables directly into the string itself  SQL injection danger   even though this example didn t specify the need for it    results      ActiveRecord  Base connection select all    ActiveRecord  Base send  sanitize sql array            your SQL query goes here and         are replaced       a  b  c     each do  record      instead of an array of hashes  you could put in a custom object with attributes    results  lt  lt   col a name  record  col a name    col b name  record  col b name         end   Edit  as Huy said  a simple way is ActiveRecord  Base connection execute         Another way is ActiveRecord  Base connection exec query        rows  And you can use native prepared statements  e g  if using postgres  prepared statement can be done with raw connection  prepare  and exec prepared as described in https   stackoverflow com a 13806512 178651  You can also put raw SQL fragments into ActiveRecord relational queries  http   guides rubyonrails org active record querying html and in associations  scopes  etc  You could probably construct the same SQL with ActiveRecord relational queries and can do cool things with ARel as Ernie mentions in http   erniemiller org 2010 03 28 advanced-activerecord-3-queries-with-arel   And  of course there are other ORMs  gems  etc   If this is going to be used a lot and adding indices won t cause other performance resource issues  consider adding an index in the DB for payment details created at and for payment errors created at   If lots of records and not all records need to show up at once  consider using pagination    https   www ruby-toolbox com categories pagination https   github com mislav will paginate   If you need to paginate  consider creating a view in the DB first called payment records which combines the payment details and payment errors tables  then have a model for the view  which will be read-only   Some DBs support materialized views  which might be a good idea for performance   Also consider hardware or VM specs on Rails server and DB server  config  disk space  network speed latency etc   proximity  etc  And consider putting DB on different server VM than the Rails app if you haven t  etc

[sql] Rails raw SQL example

Examples related to sql

Examples related to ruby-on-rails