advanced_ar_rales_engine.sql

-- ADVANCED ACTIVE RECORD
-- RALES_ENGINE**
-- Find top 5 most expensive invoices with successful transactions

-- What do we need?
-- * invoices
--     * ID
-- * Invoice Items
--     * Quantity and unit price (multiply) (AS revenue)
--     * invoice ID
-- * Transactions
--     * Result == “success"
--     * Invoice ID
-- * RELATIONSHIPS
--     * Join invoice items and invoices by invoice ID
--     * Join invoices and transactions by invoice ID
--         * Where clause for result == “sucess”
-- * Need to ORDER (desc) then LIMIT 5

-- ACTIVERECORD writing raw SQL in rails c
sql = 'select * from merchants;'
Merchant.find_by_sql(sql)

-- OR
ActiveRecord::Base.connection.execute(sql)
  --  (0.8ms)  select * from merchants;
--  => #<PG::Result:0x007fc717b43030 status=PGRES_TUPLES_OK ntuples=100 nfields=4 cmd_tuples=100>

-- converts to array =>
ActiveRecord::Base.connection.execute(sql).to_a

-- rails dbconsole -> will connect you to DB specified in .yml *write SQL

select * from merchants;

-- id  |               name                |     created_at      |     updated_at
-- -----+-----------------------------------+---------------------+---------------------
--    1 | Schroeder-Jerde                   | 2012-03-27 14:53:59 | 2012-03-27 14:53:59
--    2 | Klein, Rempel and Jones           | 2012-03-27 14:53:59 | 2012-03-27 14:53:59
--    3 | Willms and Sons                   | 2012-03-27 14:53:59 | 2012-03-27 14:53:59
--    4 | Cummings-Thiel                    | 2012-03-27 14:53:59 | 2012-03-27 14:53:59
--    5 | Williamson Group                  | 2012-03-27 14:53:59 | 2012-03-27 14:53:59

-- START HERE
Invoice.joins(:transactions).where("transactions.result = ?", "success")

Invoice.joins(:transactions).where("transactions.result = ?", "success").count
-- OR
Invoice.joins(:transactions).where(transactions: {result: "success"}).count
--  => 4648 records

-- join em
Invoice.joins(:transactions).joins(:invoice_items).where(transactions: {result: "success"})

-- SQL AS
SELECT  "invoices".* FROM "invoices"
INNER JOIN "transactions" ON "transactions"."invoice_id" = "invoices"."id"
INNER JOIN "invoice_items" ON "invoice_items"."invoice_id" = "invoices"."id"
WHERE "transactions"."result" = $1 LIMIT $2

-- cleaner
Invoice.joins(:transactions, :invoice_items).where(transactions: {result: "success"})

-- SELECT -> Invoice.select(:id) => brings back JUST invoice and their ID
Invoice.select("invoices.*") -- invoice.all equivalent

-- Virtual attributues
Invoice.select("invoices.id").joins(:transactions, :invoice_items).where(transactions: {result: "success"})

--  #<ActiveRecord::Relation [#<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 2>, #<Invoice id: 2>, ...]>

-- **
Invoice.select("invoices.id, (invoice_items.quantity * invoice_items.unit_price)as revenue").joins(:transactions, :invoice_items).where(transactions: {result: "success"})
--  => #<ActiveRecord::Relation [#<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 1>, #<Invoice id: 2>, #<Invoice id: 2>, ...]>

-- SQL AS
SELECT  invoices.id, (invoice_items.quantity * invoice_items.unit_price) as revenue FROM "invoices"
INNER JOIN "transactions" ON "transactions"."invoice_id" = "invoices"."id"
INNER JOIN "invoice_items" ON "invoice_items"."invoice_id" = "invoices"."id"
WHERE "transactions"."result" = $1 LIMIT $2

items = _ -- seems to grab the last sql command and assign it to a variable

items.first.revenue
--  => 461646

-- SUM REVENUE BY ID
Invoice.select("invoices.id, sum(invoice_items.quantity * invoice_items.unit_price)as revenue").joins(:transactions, :invoice_items).where(transactions: {result: "success"}).group(:id)

-- #<ActiveRecord::Relation [#<Invoice id: 1>, #<Invoice id: 2>, #<Invoice id: 4>, #<Invoice id: 5>, #<Invoice id: 6>, #<Invoice id: 7>, #<Invoice id: 8>, #<Invoice id: 9>, #<Invoice id: 10>, #<Invoice id: 11>, ...]>

-- NOT ORDERED! Item.all or whatever does not return records in any specific way.

-- ACTIVE RECORD TO SQL EXPLORATION WITH .to_sql
puts Invoice.select("invoices.id, sum(invoice_items.quantity * invoice_items.unit_price)as revenue").joins(:transactions, :invoice_items).where(transactions: {result: "success"}).group(:id).to_sql

SELECT invoices.id, sum(invoice_items.quantity * invoice_items.unit_price) as revenue FROM "invoices"
INNER JOIN "transactions" ON "transactions"."invoice_id" = "invoices"."id"
INNER JOIN "invoice_items" ON "invoice_items"."invoice_id" = "invoices"."id"
WHERE "transactions"."result" = 'success'
GROUP BY "invoices"."id"

--   id  | revenue
-- ------+---------
--   251 |  736490
--  2848 | 1565274
--  3565 | 1573212
--  2026 | 1150743
--  3028 | 2044120
--  2409 |  215431
--   264 | 1697660
--  2024 | 1234520
--  4295 |  407412
--  4349 |  290943
--  4718 |   90680
--  3370 |  181604
--  4323 |  651795
--  2961 |  498028
--   496 |  573820

  Invoice.select("invoices.id, sum(invoice_items.quantity * invoice_items.unit_price)as revenue").joins(:transactions, :invoice_items).where(transactions: {result: "success"}).group(:id).order('revenue desc').limit(100)

  -- SQL
  SELECT  invoices.id, sum(invoice_items.quantity * invoice_items.unit_price) as revenue FROM "invoices"
  INNER JOIN "transactions" ON "transactions"."invoice_id" = "invoices"."id"
  INNER JOIN "invoice_items" ON "invoice_items"."invoice_id" = "invoices"."id"
  WHERE "transactions"."result" = 'success'
  GROUP BY "invoices"."id" ORDER BY revenue desc LIMIT 100

--   id  | revenue
-- ------+---------
--  1560 | 4896887
--  3394 | 4787797
--  4377 | 4722254
--  3584 | 4695214
--  2711 | 4692545
--  2039 | 4610923
--  1281 | 4540090
--  4796 | 4299984
--   323 | 4299035
--   585 | 4180631
--  4591 | 4176431
--  2298 | 4107447

invoices = _

invoices.map(&:revenue)
-- [4896887, 4787797, 4722254, 4695214, 4692545, 4610923, 4540090, 4299984] etc.


-- MERCHANT REVENUE -> the revenue for all merchants
-- Tables
-- * Merchants
--     * ID
-- * Invoices
--     * merchant ID
-- * Invoice Items
--     * sum(quantity * price) as revenue
--     * invoice ID
-- * Transactions
--     * Status == success

-- Relationships
-- * WHERE merchants and invoices
-- * Join invoices and invoice items
-- * Join invoices and transactions

merchant = Merchant.first
merchant.invoices

Merchant.joins(invoices: [:transactions])
Merchant.joins(invoices: [:transactions, :invoice_items])
-- do a joins on invoices but then join the other two on invoices -> as defined in our invoice relationships

-- SQL
SELECT "merchants".* FROM "merchants"
INNER JOIN "invoices"      ON "invoices"."merchant_id"     = "merchants"."id"
INNER JOIN "transactions"  ON "transactions"."invoice_id"  = "invoices"."id"
INNER JOIN "invoice_items" ON "invoice_items"."invoice_id" = "invoices"."id"


-- ALMOST
Merchant.select("merchants.*, sum(invoice_items.quantity*invoice_items.unit_price) as revenue").joins(invoices: [:transactions, :invoice_items]).group(:id)

SELECT merchants.*, sum(invoice_items.quantity*invoice_items.unit_price) as revenue FROM "merchants"
INNER JOIN "invoices" ON "invoices"."merchant_id" = "merchants"."id"
INNER JOIN "transactions" ON "transactions"."invoice_id" = "invoices"."id"
INNER JOIN "invoice_items" ON "invoice_items"."invoice_id" = "invoices"."id"
GROUP BY "merchants"."id"

--  id  |               name                |     created_at      |     updated_at      |  revenue
-- -----+-----------------------------------+---------------------+---------------------+-----------
--   43 | Marks, Shanahan and Bauch         | 2012-03-27 14:54:03 | 2012-03-27 14:54:03 |  67455552
--    8 | Osinski, Pollich and Koelpin      | 2012-03-27 14:53:59 | 2012-03-27 14:53:59 |  45687132
--   11 | Pollich and Sons                  | 2012-03-27 14:54:00 | 2012-03-27 14:54:00 |  47175225
--   80 | Jakubowski, Predovic and Hudson   | 2012-03-27 14:54:07 | 2012-03-27 14:54:07 |  56851104
--   39 | Schuppe, Friesen and Schmeler     | 2012-03-27 14:54:02 | 2012-03-27 14:54:02 |  62561774
--   16 | Bosco, Howe and Davis             | 2012-03-27 14:54:00 | 2012-03-27 14:54:00 |  51648239
--   54 | Zemlak-Collins                    | 2012-03-27 14:54:04 | 2012-03-27 14:54:04 |  65057284
--   47 | Franecki-Ullrich                  | 2012-03-27 14:54:03 | 2012-03-27 14:54:03 |  64569011
--    3 | Willms and Sons                   | 2012-03-27 14:53:59 | 2012-03-27 14:53:59 |  45542709
--   61 | Auer, Crooks and Shanahan         | 2012-03-27 14:54:05 | 2012-03-27 14:54:05 |  89440794
--   96 | Swaniawski-Cremin                 | 2012-03-27 14:54:08 | 2012-03-27 14:54:08 |  57002910
--   87 | Johnson-Wiza                      | 2012-03-27 14:54:08 | 2012-03-27 14:54:08 |  65972848
--   67 | Torp and Sons                     | 2012-03-27 14:54:06 | 2012-03-27 14:54:06 |  84060507
--   14 | Dicki-Bednar                      | 2012-03-27 14:54:00 | 2012-03-27 14:54:00 | 129002328
--   99 | Fahey-Stiedemann                  | 2012-03-27 14:54:09 | 2012-03-27 14:54:09 |  62558908

-- FIN
Merchant.select("merchants.*, sum(invoice_items.quantity*invoice_items.unit_price) as revenue").joins(invoices: [:transactions, :invoice_items]).group(:id).merge(Transaction.unscoped.successful).order("revenue desc")


SELECT merchants.*, sum(invoice_items.quantity*invoice_items.unit_price) as revenue FROM "merchants"
INNER JOIN "invoices" ON "invoices"."merchant_id" = "merchants"."id"
INNER JOIN "transactions" ON "transactions"."invoice_id" = "invoices"."id"
INNER JOIN "invoice_items" ON "invoice_items"."invoice_id" = "invoices"."id"
WHERE "transactions"."result" = 'success'
GROUP BY "merchants"."id"
ORDER BY revenue desc

--  id  |               name                |     created_at      |     updated_at      |  revenue
-- -----+-----------------------------------+---------------------+---------------------+-----------
--   14 | Dicki-Bednar                      | 2012-03-27 14:54:00 | 2012-03-27 14:54:00 | 114839374
--   89 | Kassulke, O'Hara and Quitzon      | 2012-03-27 14:54:08 | 2012-03-27 14:54:08 | 101527515
--   98 | Okuneva, Prohaska and Rolfson     | 2012-03-27 14:54:09 | 2012-03-27 14:54:09 |  91742486
--   10 | Bechtelar, Jones and Stokes       | 2012-03-27 14:54:00 | 2012-03-27 14:54:00 |  83149384
--   61 | Auer, Crooks and Shanahan         | 2012-03-27 14:54:05 | 2012-03-27 14:54:05 |  82959718
--   79 | Pacocha-Mayer                     | 2012-03-27 14:54:07 | 2012-03-27 14:54:07 |  79070225
--   53 | Rath, Gleason and Spencer         | 2012-03-27 14:54:04 | 2012-03-27 14:54:04 |  77277860
--   90 | Friesen, Hackett and Runte        | 2012-03-27 14:54:08 | 2012-03-27 14:54:08 |  75615137
--   36 | Bernhard, Stanton and Funk        | 2012-03-27 14:54:02 | 2012-03-27 14:54:02 |  74871521
--   84 | Terry-Moore                       | 2012-03-27 14:54:07 | 2012-03-27 14:54:07 |  74100027
--   69 | Watsica-Parisian                  | 2012-03-27 14:54:06 | 2012-03-27 14:54:06 |  73899074
--   66 | Bechtelar LLC                     | 2012-03-27 14:54:05 | 2012-03-27 14:54:05 |  73067051
--   70 | Weimann-Schimmel                  | 2012-03-27 14:54:06 | 2012-03-27 14:54:06 |  72642270