I was trying to make simple SQL-Query tasks. So i used active record and SQLite for my development environment with PostgreSQL for my prod. env.
I thought ActiveRecord is used because it can generate queries depending on DB used but all my queries have some errors for PostgreSQL.
Queries:
@sql[0] = Task.select(:done, :deadline).order(name: :asc).distinct
@sql[1] = Task.joins(:project).group(:project_id).select("projects.name,
COUNT(*) as TaskCount").order("TaskCount DESC")
@sql[2] = Task.joins(:project).group(:project_id).select("projects.name,
COUNT(*) as TaskCount").order("projects.name ASC")
@sql[3] = Task.select("projects.name AS pName","tasks.*")
.joins(:project).where("projects.name LIKE ?",'N%')
.where("projects.name LIKE ?","%_a_%")'
@sql[4] = Project.joins("LEFT OUTER JOIN tasks
ON 'projects'.'id'='tasks'.'project_id'")
.group(:project_id)
.select("projects.*, COUNT(tasks.project_id) as TaskCount")
.where("projects.name LIKE ?","%_a_%")
@sql[5] = Task.group(:name).having("COUNT(*)>1").order(name: :asc)
@sql[6] = Task.joins(:project).where("projects.name = 'Garage'")
.group("tasks.name, tasks.done, tasks.deadline")
.having("COUNT(*)>1").select("tasks.*, COUNT(*)").order("COUNT(*) DESC")
@sql[7] = Task.where("tasks.done = ?",true).joins(:project).group(:project_id)
.having("COUNT(*)>=10").select("projects.name, COUNT(*) as TaskCount")
.order("projects.id DESC")
Every of them has some errors.
I dont expect you to solve them.
This probably deserves an appropriate answer.
My question how would i avoid them in a first place?
First things first - keep a copy of the rails guides for postgres handy. That will cover most bases. You usually will encounter them in the wild - examples like these abound. SO does a very good job at pointing you in the right direction.
Should i use PostgreSQL for development as well?
Absolutely. Heroku nails it on the head:
Any divergence between the development of an application and its execution in production can cause tiny incompatibilities, causing code that worked in development or staging to fail in production. For instance, avoid using different services between development and production, even when adapters theoretically abstract away any differences in services. Using SQLite locally and PostgreSQL in production; or local process memory for caching in development and Memcached in production, may seem innocuous at first but can be the cause of a variety of production issues.
More reading from this SO post
Oh, and:
Q: What the purpose of active record then? Can i just write pure queries? Because it seems like a better choice. (Maybe i am wrong?)
People fall on both sides of this contention. Some people say we just give up ORM altogether because it is an antipattern. You will find staunch supporters on both sides of the issue.
What we do know, is that it is a useful tool that should be used with a decent amount of underlying knowledge about the SQL getting spit out. It certainly is handy, but be prepared to roll your sleeves up if you find you are executing unperformant queries or something is not working as expected - you can always write pure SQL when needed. Cheers.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments