CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4model-view-controller
Nir
Nir

Reputation: 2657

How to show results based on sql result data

I'm using Closure tree idea in my new website. In one of the show views I want to select data by the id (1-level descendant) or if the id is null the first level. How do I connect sql with set result?

The Query:

select id,name 
from tags t 
join tag_hierarchies th (t.id = th.ancestor_id) 
where t.id=nvl(?,0) and th.generations=1

Code so far(problem on app/views/show.erb):

db/schema.rb:

  create_table "tags" do |t|
    t.string "name",  :null=>false
    t.boolean "isCat", :default => true
  end

  create_table "tag_hierarchies", :id => false do |t|
    t.integer "ancestor_id", :null => true
    t.integer "descendant_id", :null => false
    t.integer "generations", :null => false
  end

  add_foreign_key(:tag_hierarchies, :tags, :column => 'ancestor_id')
  add_foreign_key(:tag_hierarchies, :tags, :column => 'descendant_id')

app/models/tag.rb

class Tag < ActiveRecord::Base
  #attr_accessible :name, :isCat

  validates :name, uniqueness: false, allow_blank: false
end

app/models/Tag_Hierarchie.rb

class TagHierarchie < ActiveRecord::Base
  #attr_accessible :ancestor_id, :descendant_id, :generations
end

app/views/show.erb

<% provide(:title, category_name_or_constant(@tags)) %>
<h1><%= category_name_or_constant(@tags)%></h1>

<div class="row">
  <div class="span6 offset3">
    <%= for(<<here goes the sql by the Closure tree >>) do |f| %>
        <%= link_to tag.name, tag %>
    <% end %>
  </div>
</div>

Upvotes: 2

Views: 54

Answers (1)

EugZol
EugZol

Reputation: 6555

Add static method (or scope, if you prefer) to your Tag model:

app/models/tag_hierarchy.rb

class TagHierarchy
  belongs_to :tag, foreign_key: :ancestor_id
end

app/models/tag.rb

class Tag
  has_many :tag_hierarchies, foreign_key: :ancestor_id

  def self.descendants(id = nil)
    id ||= 0
    self.where(id: id).joins(:tag_hierarchies).where(tag_hierarchies: {generations: 1})
  end
end

Create a controller:

rake g controller TagsController

Add code to your controller:

app/controllers/tags_controller.rb

class TagsController < ApplicationController
  def index
    @descendants = Tag.descendants
  end

  def show
    @descendants = Tag.descendants(params[:id])
  end
end

Then use all the stuff in your views:

app/views/tags/show.html.erb:

<div class="row">
  <div class="span6 offset3">
    <%= @descendants.each do |tag| %>
        <%= link_to tag.name, tag %>
        <%# you can also use tag.tag_hierarchies here %>
    <% end %>
  </div>
</div>

I suggest you read some tutorials and/or docs on Rails:

Active Record Associations

Rails Routing from the Outside In

Getting Started with Rails

Upvotes: 1

Related Questions