Topic: Foreign Key Validation

I've to do a simple validation while getting fields entered in my Form. They should be validated against the values in another Table. I've added the "validates_associated" in my model which doesn't seem to work...I'm sure there is a solution to this problem as it's a very common validation. Please help me out.

Re: Foreign Key Validation

You can do custom validations by creating a "validate" method in the model:

def validate
  errors.add_to_base('some error...') if # some condition...
end

I can't provide more detailed code without knowing the specifics.

Railscasts - Free Ruby on Rails Screencasts

Re: Foreign Key Validation

I have added a "validate" method in my model, but how to capture the error? Should it be in the controller instead? The req_id field in subreqs table needs to be validated against req.id field while entering data in the form.

def validate
     if req && !req.valid?
       req.errors.each { |attr, msg| errors.add(attr,msg) }
       flash[:notice] = 'Req Id invalid'
     end
   end

Re: Foreign Key Validation

Oh, are you just trying to display the errors which are in the req models? In that case, yes, you would handle this in the controller/view. A given model should only have the errors for itself, not the models it relates to. If you post the code in the controller I may be able to help more.

Railscasts - Free Ruby on Rails Screencasts

Re: Foreign Key Validation

class SubreqsController < ApplicationController

  before_filter :login_required, :only=>['index','new','search','create','update','edit']

  # GETs should be safe (see http://www.w3.org/2001/tag/doc/whenToUseGet.html)
  verify :method => :post, :only => [ :destroy, :create, :update ],
         :redirect_to => { :action => :list }

  def validate
    if req && !req.valid?
      req.errors.each { |attr,msg| errors.add(attr, msg)}
    end
  end

  def index
  end

  def index_by_scope_id
  end
 
  def index_by_req_id
  end

  def index_by_br_tr_no
  end

  def index_by_title
  end

 
  def list
    @subreq_pages, @subreqs = paginate :subreqs, :per_page => 10
  end

  def show
    @subreq = Subreq.find(params[:id])
  end

  def new
    @subreq = Subreq.new
    @resources = Resource.find_all
    @bfields = Bfield.find_all
    @itfields = Itfield.find_all
    @user = session[:user]
    @subreq.user_id = @user.id
  end

  def search
      @query = request.raw_post
      @q = @query[7...@query.length]
      @phrase = "%" + @q + "%"
      @results = Subreq.find(:all, :conditions=> ["Title like ?", @phrase])
      @total = Subreq.count(:conditions=> ["Title like ?", @phrase] )

  end

  def search_by_scope_id
      @results = Subreq.find(:all, :conditions=> ["Scope_id LIKE ?", "%#{params[:search]}%" ] )
      @total = Subreq.count(:conditions=> ["Scope_id like ?", "%#{params[:search]}%" ] )
      render :layout=>false
  end


  def search_by_req_id
      @results = Subreq.find(:all, :conditions=> ["Req_id LIKE ?", "%#{params[:search]}%" ] )
      @total = Subreq.count(:conditions=> ["Req_id like ?", "%#{params[:search]}%" ] )
      render :layout=>false
  end
       
  def search_by_br_tr_no
      @results = Subreq.find(:all, :conditions=> ["Br_tr_no like ?", "%#{params[:search]}%" ] )
      @total = Subreq.count(:conditions=> ["Br_tr_no like ?", "%#{params[:search]}%" ] )
      render :layout=>false
  end

  def search_by_title
      @results = Subreq.find(:all, :conditions=> ["Title like ?", "%#{params[:search]}%" ] )
      @total = Subreq.count(:conditions=> ["Title like ?", "%#{params[:search]}%" ] )
      render :layout=>false
  end


  def create
    @subreq = Subreq.new(params[:subreq])
    @subreq.resource_ids = Resource.find(params[:resource_ids]) if params[:resource_ids]
    @subreq.bfield = Bfield.find(params[:bfield_ids]) if params[:bfield_ids]
    @subreq.itfield = Itfield.find(params[:itfield_ids]) if params[:itfield_ids]
    #These are added in case there are validation errors when saving and the new method is re-rendered
    @resources = Resource.find_all
    @bfields = Bfield.find_all
    @itfields = Itfield.find_all
    @user = session[:user]
    @subreq.user_id = @user.id
   
    #@subreq.req_completed_at = @subreq.created_at
    #@req = Req.find(params[:subreq][:req_id])
    #if @req.id.nil?
    #  flash[:notice] = 'Req Id is not valid.'
    #end

       
    if @subreq.sub_function.blank?
       @subreq.real_number = @subreq.br_tr_no + "." + @subreq.scope_id + "." + @subreq.req_extension
    else
       @subreq.real_number = @subreq.br_tr_no + "." + @subreq.sub_function + "." + @subreq.scope_id + "." + @subreq.req_extension
    end
   
    #@subreq.real_number = @subreq.real_num

    if @subreq.save
      flash[:notice] = 'Subreq was successfully created.'
      redirect_to :action => 'edit', :id=>@subreq
    else
      render :action => 'new'
    end
    @su
  end

  def edit
    @subreq = Subreq.find(params[:id])
    @resource = @subreq.resource
    @bfield = @subreq.bfield
    @itfield = @subreq.itfield
    @user = @subreq.user
    @resources = Resource.find_all
    @bfields = Bfield.find_all
    @itfields = Itfield.find_all

  end

  def update
    @subreq = Subreq.find(params[:id])
    @subreq.resource_ids = Resource.find(params[:resource_ids]) if params[:resource_ids]
    @subreq.bfield = Bfield.find(params[:bfield_ids]) if params[:bfield_ids]
    @subreq.itfield = Itfield.find(params[:itfield_ids]) if params[:itfield_ids]
    @user = session[:user]
    @subreq.user_id = @user.id
    #@subreq.req_updated_at = @subreq.updated_at
    @subreq.last_updated_by_id = @user
    #These are added in case there are validation errors when saving and the new method is re-rendered
    @resources = Resource.find_all
    @bfields = Bfield.find_all
    @itfields = Itfield.find_all

    if @subreq.sub_function.blank?
       @subreq.real_number = @subreq.br_tr_no + "." + @subreq.scope_id + "." + @subreq.req_extension
    else
       @subreq.real_number = @subreq.br_tr_no + "." + @subreq.sub_function + "." + @subreq.scope_id + "." + @subreq.req_extension
    end

    if @subreq.phase_id.to_s != params[:subreq][:phase_id]
       @phase_tracking = PhaseTracking.new
          @phase_tracking.phase_id = @subreq.phase_id
          @phase_tracking.req_id = @subreq.id
          if @phase_tracking.save
             flash[:notice] = 'PhaseTracking was successfully created.'
          end
    end

    if @subreq.status_id.to_s != params[:subreq][:status_id]
       @status_tracking = StatusTracking.new
          @status_tracking.status_id = @subreq.status_id
          @status_tracking.req_id = @subreq.id
          if @status_tracking.save
             flash[:notice] = 'StatusTracking was successfully created.'
          end
    end
   


    if @subreq.update_attributes(params[:subreq])
      flash[:notice] = 'Subreq was successfully updated.'
      redirect_to :action => 'edit', :id => params[:id]
    else
      render :action => 'edit'
    end
  end

  def destroy
    Subreq.find(params[:id]).destroy
    redirect_to :action => 'list'
  end

  def sub_reqs_report
    @subreq = Subreq.find(:all, :conditions => ["scope_id = ?","%#{params[:search]}%"])
    render_without_layout
        sub_reqs
end

def sub_reqs
     # !/usr/bin/env ruby

    #RAILS_ENV = 'production'

    #require File.dirname(__FILE__) + '/../config/environment'
    require "spreadsheet/excel"
    today = Date.today
   
    file = "sub_reqs_report_#{today}.xls"
    workbook = Spreadsheet::Excel.new("#{RAILS_ROOT}/public/reports/#{file}")
       
    worksheet = workbook.add_worksheet("Sub Requirements Report")
    worksheet.write(0, 0, "ID")
    worksheet.write(0, 1, "Revision Date")
    worksheet.write(0, 2, "BR/TR Req")
    worksheet.write(0, 3, "Req Definition")
    worksheet.write(0, 4, "Req Extension")
    worksheet.write(0, 5, "Real Number")
    worksheet.write(0, 6, "BR/TR Req Description")
    worksheet.write(0, 7, "Req Version No.")
    worksheet.write(0, 8, "Business Owner")
    worksheet.write(0, 9, "Assumptions")
    worksheet.write(0, 10, "Comments")
    worksheet.write(0, 11, "Sub Function")
    worksheet.write(0, 12, "Date Req Completed")
    worksheet.write(0, 13, "Amdocs OOB")
           
    startingRow = 1
    numResource = 0
    numResourceArea = 0
    numComment = 0
   
    @subreq.each do |subreq|
        row = startingRow

        worksheet.write(row, 0, "#{subreq.id}")
        worksheet.write(row, 1, "#{subreq.updated_at}")
        worksheet.write(row, 2, "#{subreq.br_tr_no}")
        worksheet.write(row, 3, "#{subreq.title}")
        worksheet.write(row, 4, "#{subreq.req_extension}")
        worksheet.write(row, 5, "#{subreq.real_number}")
        worksheet.write(row, 6, "#{subreq.req_desc}")
        worksheet.write(row, 7, "#{subreq.req_version_no}")
        worksheet.write(row, 8, "#{subreq.business_owner}")
       
        worksheet.write(row, 11, "#{subreq.sub_function}")
        worksheet.write(row, 12, "#{subreq.created_at}")
        worksheet.write(row, 13, "#{subreq.appln_scope}")
       
       
        numAssumption = 0
        @assumptionss = Assumptions.find_all_by_subreq_id(subreq.id)
        unless @assumptions.nil?
            @assumptions.each do |assumption|
            worksheet.write(row, 10, "#{assumption.assumption}")
            row +=1
            numAssumption += 1
        end
        endAssumption = row
        end

        row = startingRow
        numComment = 0
        @comments = subreq.comment
        unless @comments.nil?
            @comments.each do |comment|
            worksheet.write(row, 9, "#{comment.comment}")
            row +=1
            numComment += 1
        end
        endComment = row
        end
   
        if endAssumption > endComment
            startingRow += numAssumption
            startingRow -= 1
        end

        if endComment > endAssumption
            startingRow += numComment
            startingRow -= 1
        end
       
        startingRow += 1

    end

    workbook.close
end

end

Re: Foreign Key Validation

It looks like you are only creating one model in the "create" action. If you are simply trying to validate that the "req_id" column is not nil you can do this:

# in Subreq model
validates_presence_of :req_id

Is that what you're trying to do?

Railscasts - Free Ruby on Rails Screencasts

Re: Foreign Key Validation

No Ryan, I already have that validation. I need to validate that the Req_id field entered should exist in the reqs table. This is how my subreqs model looks like:
class Subreq < ActiveRecord::Base
   validates_presence_of :req_id,:br_tr_no,:scope_id,:title,:req_extension,:req_desc,:req_version_no,:business_owner
   belongs_to :req
   belongs_to :user
   belongs_to :status
   belongs_to :phase
   has_and_belongs_to_many :bfield
   has_and_belongs_to_many :itfield
   has_and_belongs_to_many :resource
   has_many :comment
   has_many :assumption
   has_many :next_step
   has_many :mom
   belongs_to :last_updated_by_id, :foreign_key => 'last_updated_by_id', :class_name => 'User'
end

Re: Foreign Key Validation

How about this?

# in Subreq model
def validate
  errors.add_to_base('No Req exists with that id') if req.nil?
end

Railscasts - Free Ruby on Rails Screencasts

Re: Foreign Key Validation

Thanks Ryan for all the help! You rock!

Re: Foreign Key Validation

how to validate a field value when deleting? if that value is refered by another table column, then  v hav to do validation and should giv a message that u cant delete that data.

Last edited by niraan (2007-03-27 07:18:58)

Re: Foreign Key Validation

AFAIK, ActiveRecord doesn't run the validations on deleting. However, you can override the before_destroy callback and raise an exception:

# in model
def before_destroy
  raise #...
end

Then catch the exception in your controller and handle accordingly.

Railscasts - Free Ruby on Rails Screencasts