Jul 25

We have been using  PostgresSQL  in our new product Quiz Stack.

Quiz Stack is a SAAS based  product. Which can  conduct,manage,analysis quizzes.We are using rails and postgresql as our backend.It’s subdomain app and hence we decide to use postgresql schema based approach for storing/querying data.

While working on one of the feature It was needed to draw some random fixed questions for each user.So each user has different order of questions which  is maintain for each specific user.

In Mysql it’s called field function to achieve this ,unfortunately there isn’t  anything as of I know in PostgresSql or is it ? :)

Well we can obviously write a function which can help you do the same.Let’s the function name be sortbyid. 

Following code snippet is a function definition

CREATE OR REPLACE FUNCTION sortByID(anyelement, VARIADIC anyarray) RETURNS BIGINT AS $$
				SELECT n FROM (
   			 	SELECT ROW_NUMBER() OVER () AS n, x FROM unnest($2) x)
   			 	 numbered WHERE numbered.x = $1;
			$$ LANGUAGE 'SQL' IMMUTABLE STRICT;

So here how I get the result from Question table  for 3 record with order or id as 2,1,3

Continue reading »

Tagged with:
Aug 19

In normal rails application contain one database,but if we want rails application having more than one database that is multiple data base.   we can achieve this using multiple way.one of the way is i am showing here.it’s just 3 steps.


Lets take an e.g Project has many milestone and milestone has many task

In normal scenario model looks like following way

 
class Project < ActiveRecord::Base 
  has_many :milestones 
end
 
class Milestone < ActiveRecord::Base 
  has_many :tasks 
 belongs_to :project 
end
 
class Task < ActiveRecord::Base 
  belongs_to :milestone 
end

Now  we want that milestone is stored on milestone_db database and task on task_db database
Rails multiple database

Now to achieve this we have to do the following steps

1) Edit Database.yml


 milestone_dev: 
 reconnect: false
 encoding: utf8 
 username: <user_name> 
 adapter: mysql 
 database: milestone_db 
 pool: 5 
 password: <password> 

task_dev: 
 reconnect: false 
 encoding: utf8 
 username: <user_name> 
 adapter: mysql 
 database: task_db 
 pool: 5 
 password: <password> 

Now create milestone_db and task_db database.


2) Edit milestone.rb and task.rb


class Milestone < ActiveRecord::Base 
  #add this line to use milestone_db 
  self.establish_connection :milestone_dev 
  has_many :tasks 
  belongs_to :project 
end 
 

class Task < ActiveRecord::Base 
  #add this line to use task_db 
  set_table_name "tasks" 
  belongs_to :milestone 
end

3) After editing model edit migration file


class CreateMilestones < ActiveRecord::Migration 
  def self.connection 
    Milestone.connection 
  end 
 .....
end

Now rails application is ready with multiple database.

Tagged with:
preload preload preload