Use rails console sql query strings to learn sql

Question

Using the ActiveRecord ORM from inside the rails console I'm able to easily and sensibly get: the first item's style's retail price:

[25] pry(main)> Item.first.style.retail_price
  Item Load (0.2ms)  SELECT  "items".* FROM "items"  ORDER BY "items"."id" ASC LIMIT 1
  Style Load (0.1ms)  SELECT  "styles".* FROM "styles" WHERE "styles"."id" = ? LIMIT 1  [["id", 1]]
=> #<BigDecimal:7fe7a6bc7678,'0.6E2',9(27)>

I read in another post that I can use ActiveRecord::Base.connection.execute to run raw sql commands. I was expected to get something that looks sort of like '60.0' back because Item.first.style.retail_price.to_s #=> "60.0" Instead a I get some sort of array with a hash inside it:

[26] pry(main)> ActiveRecord::Base.connection.execute('SELECT  "items".* FROM "items"  ORDER BY "items"."id" ASC LIMIT 1; SELECT  "styles".* FROM "styles" WHERE "styles"."id" = ? LIMIT 1  [["id", 1]]')

   (0.2ms)  SELECT  "items".* FROM "items"  ORDER BY "items"."id" ASC LIMIT 1; SELECT  "styles".* FROM "styles" WHERE "styles"."id" = ? LIMIT 1  [["id", 1]]
=> [{"id"=>1, "size"=>"M", "color"=>"Purple", "status"=>"sellable", "price_sold"=>nil, "sold_at"=>nil, "style_id"=>1, "created_at"=>"2017-02-04 04:47:09.415828", "updated_at"=>"2017-02-04 04:47:09.415828", "clearance_batch_id"=>nil, 0=>1, 1=>"M", 2=>"Purple", 3=>"sellable", 4=>nil, 5=>nil, 6=>1, 7=>"2017-02-04 04:47:09.415828", 8=>"2017-02-04 04:47:09.415828", 9=>nil}]

Also when I try to the same query string is rails db I get a syntax error:

rails db
SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
sqlite> SELECT  "items".* FROM "items"  ORDER BY "items"."id" ASC LIMIT 1; SELECT  "styles".* FROM "styles" WHERE "styles"."id" = ? LIMIT 1  [["id", 1]];
1|M|Purple|sellable|||1|2017-02-04 04:47:09.415828|2017-02-04 04:47:09.415828|
Error: near "[["id", 1]": syntax error
sqlite>

3 questions:

1. What string does ActiveRecord::Base.connection.execute need to return an expected result that looks something like 60.0?
2. What string does rails db need?
3. What is the general pattern of how to translate the sql string that the rails console gives me into a raw sql query string that would work with ActiveRecord::Base.connection.execute and rails db?

Answer 1

If you want just one field from one realtion, you have to specify it in the select clause in SQL. Something like this

SELECT styles.retail_price FROM styles WHERE styles.id = 1

In this case, you obtain just the retail_price but in an array if you use rails's execute method. If you want just the single value use select_value method, not execute. Like

ActiveRecord::Base.connection.select_value("SELECT styles.retail_price FROM styles WHERE styles.id = 1")

In the sqlite3 terminal you obtain a syntax error because of this [["id", 1]]. That's a rails/applicative-level stuff used for prepared statements but is not valid SQL. You should just substitute the id like I did.