/sql-to-ar-translations

sql-to-ar-translations

SQL to Active Record translations (practice exercises)

Authors

Morgan and Vishal.

Exercises

SQL to AR

SELECT *
FROM
users;
User.all
SELECT *
FROM
users
WHERE
user.id = 1;
User.find(1)
SELECT *
FROM posts
ORDER BY created_at DESC
LIMIT 1;
Post.all.last
SELECT *
FROM users JOIN posts
ON posts.author_id = users.id
WHERE
posts.created_at >= '2014-08-31 00:00:00';
User.joins("JOIN posts ON posts.author_id = users.id")
    .where("posts.created_at > '2014-08-31 00:00:00'")
SELECT
count(*)
FROM users
GROUP BY favorite_color
HAVING count(*) > 1;
User.select("COUNT(*) AS color_count")
  .group(:favorite_color)
  .having("color_count > 1")

AR to SQL

Post.all
SELECT *
FROM posts
Post.first
SELECT *
FROM posts
ORDER BY id
LIMIT 1
Post.last
SELECT *
FROM posts
ORDER BY id DESC
LIMIT 1
Post.where(:id => 4)
SELECT *
FROM posts
WHERE id=4
Post.find(4)
SELECT *
FROM posts
WHERE id=4
User.count
SELECT COUNT(*)
FROM posts
Post.select(:name).where(:created_at > 3.days.ago).order(:created_at)
SELECT name
FROM posts
ORDER BY created_at
WHERE created_at > DATEADD(day,-3,GETDATE())
Post.select("COUNT(*)").group(:category_id)
SELECT COUNT(*)
FROM posts
GROUP BY category_id
All posts created before 2014
SELECT *
FROM posts
WHERE created_at < 2014
A list of all (unique) first names for authors who have written at least 3 posts
SELECT DISTINCT first_name, COUNT(*)
FROM authors
JOIN posts ON authors.id=posts.authorid
GROUP BY authors.id
HAVING COUNT(*) >= 3
The posts with titles that start with the word "The"
SELECT *
FROM posts
WHERE title LIKE 'The%'
Posts with IDs of 3,5,7, and 9
SELECT *
FROM posts
WHERE id IN (3,5,7,9)

Custom Translation: 3 Queries

  1. How many posts are written by authors who are older than 46?
SELECT COUNT(*)
FROM posts JOIN authors ON posts.authorid = authors.id
WHERE age > 46
Post.joins("JOIN authors ON posts.authorid = authors.id")
    .where("age > 46")
    .count
  1. Which author has written the most posts?
SELECT name
FROM authors JOIN posts ON authors.id = posts.authorid
GROUP BY authors.id
ORDER BY COUNT(*)
LIMIT 1
Author.joins("JOIN posts ON authors.id = posts.authorid")
      .group("authors.id")
      .order("COUNT(*)")
      .first
  1. Who was the last author to join?
SELECT name
FROM authors
ORDER BY created_at DESC
LIMIT 1
Author.last