Recently I have started exploring Phoenix as part of my learning. Phoenix is a web framework just like Rails but it built on top of Elixir and Ecto is an ORM just like Active Record use by Phoenix to talk to DB.

Since the semantics of Phoenix is similar to rails makes me very comfortable with Phoenix. Let see some similarities between Ecto and Active Record. Prior to that, the important aspect about Ecto is it follows Explicitness whereas Active Record follows implicitness. Let check out these characteristics with examples:

Model Generation

In Active Record

rails g model User first_name:string last_name:string
# app/models/user.rb
class User < ApplicationRecord
end
# db/migrate/20170925112416_create_users.rb
class CreateUsers < ActiveRecord::Migration[5.1]
  def change
    create_table :users do |t|
      t.string :first_name
      t.string :last_name
      t.timestamps
    end
  end
end

In Ecto

mix phx.gen.scheme User users first_name:string last_name:string
# lib/stc/user.ex
defmodule Stc.User do
 use Ecto.Schema
 import Ecto.Changeset
 alias Stc.User
 schema “users” do
   field :first_name, :string
   field :last_name, :string
   timestamps()
 end
end
# priv/repo/migrations/20170925112137_create_user.exs
defmodule Stc.Repo.Migrations.CreateUser do
 use Ecto.Migration
 def change do
   create table(:user) do
     add :first_name, :string
     add :last_name, :string
     timestamps()
   end
end

Unlike Active Record, in Ecto, we have to mention schema to access necessary attributes from a table. Otherwise, Ecto returns only those fields that are defined in the schema.
In Active Record when you mention User, implicitly it understands that it should talk with users table. Whereas in Ecto if you mention schema as the userfor the Usermodel then it talks to the tableuser instead of.users

Querying

In Active Record

# To insert single record
> User.create(first_name: ‘siva’, last_name: ‘Gollapalli’)
# To Fetch all records
> User.all

In Ecto

# To insert single record
> %Stc.User{first_name: “Siva”, last_name: “Gollapalli”} |> Stc.Repo.insert
# To Fetch all records
> Stc.User |> Stc.Repo.all

In Ecto, querying will be handled by a module called Repo which is an OTP app. By DB config, it knows which DB it should talk but to query a table we should explicitly pass the table name to Repo to get query result as above.

Associations

In Active Record

class User < ApplicationRecord
  has_many :posts
end
class Post < ApplicationRecord
  belongs_to :user
end
> user = User.first
> post = Post.create(description: "associations", user: user)
# To Fetch all posts of a user
> user.posts

In Ecto

# lib/stc/user.ex
defmodule Stc.User do
 use Ecto.Schema
 import Ecto.Changeset
 alias Stc.User
 schema "users" do
   has_many :posts, Stc.Post
 end
end
# lib/stc/post.ex
defmodule Stc.Post do
 use Ecto.Schema
 import Ecto.Changeset
 alias Stc.Post
schema "posts" do
   field :description, :string
   belongs_to :user, Stc.user
 end
end
> user = Stc.Repo.get(Stc.User, 1)
> Ecto.build_assoc(user, 
                  :posts, 
                  %{description: "First post"}) |> Stc.Repo.insert
# To Fetch all posts of a user
> Stc.Repo.all assoc(user, :posts)

As we know that in Rails, depends upon the relationship, AR would find respective model unless and until if relationship defers from the model name. If it differs then we explicitly pass an option called inclass_name relation.

In Ecto, it follows the same pattern but by default. As you see we are explicitly mentioning module name while defining a relation.

Though for the users coming from Rails background might face hiccups in their baby steps but as you progress it gives a better understanding.

Thanks for reading. Any suggestions would be welcome!!!.

Effective querying using SQL over Rails Effective querying using SQL over Rails

Most of the rails developers write queries on daily basis. Some of the other day by mistake we follow rails way instead of SQL way to do tasks like sorting etc. But you might ask what’s the problem regarding which approach I take as long as I am able to get correct results. Yes, you may get correct results but you will end up with performance problems in future. Following a set of scenarios I am observed that SQL querying out powered rails:

Apply sorting on a combination of different tables

I have three tables namely deposits, purchases and withdraws. Now, I want to display latest three records from a combination of three tables.

  • Let’s check rails way

Benchmark.ms do 
  (Deposit.all + Purchase.all + Withdraw.all).sort do |x, y|       x.updated_at <=> y.updated_at
  end
end
Deposit Load (0.6ms) SELECT "deposits".* FROM "deposits"
Purchase Load (0.4ms) SELECT "purchases".* FROM "purchases" 
Withdraw Load (0.7ms) SELECT "withdraws".* FROM "withdraws"
=> 121.91500002518296
  • Let’s check SQL way

SELECT 'deposit', authorization_code, amount, status, updated_at FROM DEPOSITS 
ORDER BY updated_at 
DESC LIMIT 3)
UNION
(SELECT 'purchase', ref, amount, status, updated_at FROM PURCHASES ORDER BY updated_at 
DESC LIMIT 3) 
UNION
(SELECT 'withdraw', ref, amount, status, updated_at FROM WITHDRAWS ORDER BY updated_at 
DESC LIMIT 3)
ORDER BY updated_at DESC LIMIT 3
=> SQL (2.0ms)

Apply sorting on different types of data

Let’s say a table stores gifs, videos of different duration. Now I want sort videos, gifs in ascending order of content duration separately.

  • Let’s check rails way

Benchmark.ms do 
   videos = SpotBooking.where(content_type:'video')
                       .order('content_duration DESC')
gifs = SpotBooking.where(content_type:'gif')
                     .order('content_duration DESC')
videos + gifs 
end
 
SpotBooking Load (0.4ms) SELECT "spot_bookings".* FROM "spot_bookings" WHERE "spot_bookings"."content_type" = $1 ORDER BY content_duration DESC [["content_type", 3]]
SpotBooking Load (0.4ms) SELECT "spot_bookings".* FROM "spot_bookings" WHERE "spot_bookings"."content_type" = $1 ORDER BY content_duration DESC [["content_type", 2]]
=> 12.477000011131167
  • Let’s check SQL way

SELECT id, content_duration, row_number() 
OVER (PARTITION BY content_type ORDER BY content_duration DESC) 
FROM spot_bookings
=> SQL (0.8ms)

Formatting the data while querying

  • Let’s check rails way

Benchmark.ms do 
  h = {} 
  pricings = SpotPricing.order('starts_at ASC') 
  pricings.each do |b|    
    h[b.starts_at.strftime("%H %M %P")] = b.price.to_f 
  end 
end
SpotPricing Load (0.5ms) SELECT "spot_pricings".* FROM "spot_pricings" ORDER BY starts_at ASC
=> 35.459000151604414
# output
{"02:19 AM"=>"1.00", "03:19 AM"=>"2.00", "04:19 AM"=>"3.00",  
 "05:19 AM"=>"4.00", "06:19 AM"=>"5.00", "07:19 AM"=>"6.00", 
 "08:19 AM"=>"7.00", "09:19 AM"=>"8.00", "10:19 AM"=>"9.00",
 "11:19 AM"=>"10.00", "12:19 PM"=>"11.00", "13:19 PM"=>"12.00",  "14:19 PM"=>"13.00", "15:19 PM"=>"14.00", "16:19 PM"=>"15.00",  "17:19 PM"=>"16.00", "18:19 PM"=>"17.00", "19:19 PM"=>"18.00", "20:19 PM"=>"19.00", "21:19 PM"=>"20.00", "23:19 PM"=>"22.00", "00:19 AM"=>"23.00"}
  • Let’s check SQL way

SpotPricing.order('starts_at ASC').pluck("to_char(starts_at, 'HH24:MI AM')", "to_char(price, 'FM999999999.00')")
SELECT to_char(starts_at, 'HH24:MI AM'), to_char(price, 'FM999999999.00') FROM "spot_pricings" ORDER BY ASC
=> SQL (0.5ms)
# Output
{"02:19 AM"=>"1.00", "03:19 AM"=>"2.00", "04:19 AM"=>"3.00", 
 "05:19 AM"=>"4.00", "06:19 AM"=>"5.00", "07:19 AM"=>"6.00", 
 "08:19 AM"=>"7.00", "09:19 AM"=>"8.00", "10:19 AM"=>"9.00", 
 "11:19 AM"=>"10.00", "12:19 PM"=>"11.00", "13:19 PM"=>"12.00",    "14:19 PM"=>"13.00", "15:19 PM"=>"14.00", "16:19 PM"=>"15.00", "17:19 PM"=>"16.00", "18:19 PM"=>"17.00", "19:19 PM"=>"18.00", "20:19 PM"=>"19.00", "21:19 PM"=>"20.00", "23:19 PM"=>"22.00", "00:19 AM"=>"23.00"}

In all above scenarios, SQL plays best compared to conventional rails way. Sometimes I would feel it’s not a big deal if you not follow design patterns as long as your ability to provide the best outcome.

Any suggestions and feedback would be welcome!!!

Happy querying!!!


Originally published at railslearning.wordpress.com on July 31, 2017.

Want to explore all the ways you can start, run & grow your business?