Rails Model SQL

Posted by On 2月 16, 2014 0 Comment

Topics

find

Query Method

Others

Reset Database

After testing and if you want to clean.

rake db:setup

Create new databse if not exist(if exist, skip it)

Prepare data

rails generate model Book name publish_year:integer
rake db:migrate

find

Retrieve data from database using primary key

@item = Item.find(1)
@items = Item.find([1,2,3])
Book.find(5).class

find_by

find by column(get first one)

def find_by
  @item = Item.find(name: 'yoona')
  render 'items/show'
end

Query method

where

@items = Item.where(name: 'ABC')
@item = Item.where(name: 'Book', price: 40)
@item = Item.where(date: '20141010'..'20141026')
@item = Item.where(price:[40, 100])

Placeholder

View

<%= form_tag action: :search do %>
  <div class="field">
    <%= label_tag :name, 'Name:' %><br>
	<%= text_field_tag :name %>
  </div>
  
  <div class="field">
    <%= label_tag :price, 'Price:' %><br>
	<%= text_field_tag :price %>
  </div>
  <%= submit_tag 'Search' %>
<% end %>

Controller

def search
  @items = Item.where('name' = ? AND price >= ?', params[:name
  ], params[:price])
  render item/list
end

nameparameter

def search
  @items = Item.where('name' = :name AND price >= :price', name: params[:name
  ], price: params[:price])
  render item/list
end

count

Book.where(publish_year:2014).count

def count
  cnt = Item.where(['price < ?', 50&#93;).count 
  render text: "#{cnt} items"
end
&#91;/ruby&#93;

<hr>
<h4><a name="not">not</a></h4>
Combination with where
[ruby]
def not
  @items = Item.where.not(name: params[:name])
end

between

Book.where(publish_year:2000..2014)

Several search factors

Book.where(publish_year:2000..2014, id:1..5)

in

Book.where(publish_year:[2000, 2003])

This means SELECT * FROM BOOK WHERE release_year IN(2000,2003)

first

Get first data

@item = Item.where(publish_year:[2000, 2003]).first

Last 

@item = Item.order(:date :desc).last

Sanitized SQL

%% like Rilakkuma(include name “Rilakkuma”)

Book.where('name like ?', '%Rilakkuma%')

Number

Book.where('publish_year > ?', 2013).count

More difficult

Book.where('name like ? AND publish_year > ?', '%first%', 2013).count

Using variable

search_word = 'rakuten'
Book.where('name like ?', "%#{search_word}%").count

Select

Get specific column data

def select
  @item Item.where('price' >= 35).select(:name, :price)
end

distinct

distinct same as SQL itself

def select2
  @items = Item.select(:name).distinct.order(:price)
end

order

Book.where(publish_year:2000).order(:name)
Book.where(publish_year:2000).order(:name).sum(:publish_year)

def order
  @items = Item.order(date: :desc)
end

reverse_order

Book.where(publish_year:2000).reverse_order(:name)

limit

Book.limit(5)

offset

Book.limit(2).offset(5)

group

Book.group(:publish_year)

having

@items = Item.select('name, AVG(price)').group(:category).having('AVG(price) >= ?', 25)

none

Generate blank set

def none
  case params[:id]
    when 'all'
	  @items = Item.all
	when 'new'
	  @items = Item.order('date DESC').limit(10)
	when 'cheap'
	  @items = Item.order(:price).liimit
	else
	  @items = Item.none   # not nil
  end
end

pluck

Get column data as array

def pluck
  render text: Item.where(name: 'ABC').pluck(:name, :price)
end

first_or_create, first_or_initialize

If missing data, create new one

average, minimum, maximum, sum

average

def average
  price = Item.where(category: 'history').average(:price)
  render text: "#{price}"
end

maximum

Book.maximum(:publish_year)

minimum

Book.minimum(:publish_year)

sum

Book.sum(:publish_year)

explain

Debugging method Run EXPLAIN

Edit

  1. Finding the record and creating a corresponding instance
  2. Changing the attribute
  3. Saving the record via the method save
book = Book.where(name:"Change the world").first
book.name = 'Let it be'
book.save

changed?

– changed?
Check whether instance will be changed or not

book = Book.where(name:"Change the world").first
book.name = 'Timber'
book.changed?		# true
book.save  
book.changed?       # false

exists?

Example1

def exists
  flag = Item.where(name: 'new world').exists?
  render text: "Exist ? : #{flag}"
end

Example2



Item.exists?(1)

Item.exists?([‘price < ?', 50])
Item.exists?				# at least one item?
[/ruby]


  					

 			 
	
			 
					   					

										

						TOPICS