ikeikeikeike's unk blog.

http://github-awards.com/users/ikeikeikeike

beego ormとdjango orm

Beego ORMはDjango ORMは影響されているということらしいので

http://beego.me/docs/mvc/model/query.md#advanced-queries の内容をDjangoORMに置き換えようと思ってたけど途中で疲れて終わらしてます (またリンクのみの箇所は置き換える必要がないほど似ているため面倒で書いていません)

Basic Usage: Advanced Queries

Go
o := orm.NewOrm()

// Get a QuerySeter object. User is table name
qs := o.QueryTable("user")

// Can also use object as table name
user := new(User)
qs = o.QueryTable(user) // return a QuerySeter
Python
from django.contrib.auth import models

qs = models.User.objects.get_queryset()

expr

Go
qs.Filter("id", 1) // WHERE id = 1
qs.Filter("profile__age", 18) // WHERE profile.age = 18
qs.Filter("Profile__Age", 18) // key name and field name are both valid
qs.Filter("profile__age", 18) // WHERE profile.age = 18
qs.Filter("profile__age__gt", 18) // WHERE profile.age > 18
qs.Filter("profile__age__gte", 18) // WHERE profile.age >= 18
qs.Filter("profile__age__in", 18, 20) // WHERE profile.age IN (18, 20)

qs.Filter("profile__age__in", 18, 20).Exclude("profile__lt", 1000)
// WHERE profile.age IN (18, 20) AND NOT profile_id < 1000
Python
qs.filter(id=1)  # WHERE id = 1
qs.filter(profile__age=18)  # WHERE profile.age = 18
qs.filter(Profile__Age=18)  # key name and field name are both valid
qs.filter(profile__age=18)  # WHERE profile.age = 18
qs.filter(profile__age__gt=18)  # WHERE profile.age > 18
qs.filter(profile__age__gte=18)  # WHERE profile.age >= 18
qs.filter(profile__age__in=[18, 20])  # WHERE profile.age IN (18, 20)

qs.filter(profile__age__in=[18, 20]).exclude(profile__lt=1000)

# WHERE profile.age IN (18, 20) AND NOT profile_id < 1000

Operators

exact

Go
qs.Filter("name", "slene") // WHERE name = 'slene'
qs.Filter("name__exact", "slene") // WHERE name = 'slene'
// using = , case sensitive or not is depending on which collation database table is used
qs.Filter("profile", nil) // WHERE profile_id IS NULL
Python
qs.filter(name="slene")  # WHERE name = 'slene'
qs.filter(name__exact="slene")  # WHERE name = 'slene'
# using = , case sensitive or not is depending on which collation database table is used
qs.filter(profile=None)  # WHERE profile_id IS NULL

Advanced Query API

SetCond

Go
cond := NewCondition()
cond1 := cond.And("profile__isnull", false).AndNot("status__in", 1).Or("profile__age__gt", 2000)

qs := orm.QueryTable("user")
qs = qs.SetCond(cond1)
// WHERE ... AND ... AND NOT ... OR ...

cond2 := cond.AndCond(cond1).OrCond(cond.And("name", "slene"))
qs = qs.SetCond(cond2).Count()
// WHERE (... AND ... AND NOT ... OR ...) OR ( ... )
Python
from django.db.models import Q

cond1 = Q(profile__isnull=False) & ~Q(status__in=[1]) | Q(profile__age__gt=2000)

from django.contrib.auth import models
qs = models.User.objects.filter(cond1)

cond2 = Q(cond1) | Q(name="slene")
qs.filter(cond2).count()
# Output number

All

Go
var users []*User
num, err := o.QueryTable("user").Filter("name", "slene").All(&users)
fmt.Printf("Returned Rows Num: %s, %s", num, err)
Python
users = models.User.objects.filter(name="slene")
print("Returned Rows Num: %s, %s" % (len(users), None))
Go
type Post struct {
    Id      int
    Title   string
    Content string
    Status  int
}

// Only return Id and Title
var posts []Post
o.QueryTable("post").Filter("Status", 1).All(&posts, "Id", "Title")
Python
class Post(models.Model):
    title = models.CharField(null=False, max_length=255)
    content = models.CharField(null=False, max_length=255)
    status = models.IntegerField(null=False)

    class Meta:
        db_table = 'post'

# Only return Id and Title
Post.objects.filter(status=1).values("id", "title")

One

Go
var user User
err := o.QueryTable("user").Filter("name", "slene").One(&user)
if err == orm.ErrMultiRows {
    // Have multiple records
    fmt.Printf("Returned Multi Rows Not One")
}
if err == orm.ErrNoRows {
    // No result 
    fmt.Printf("Not row found")
}
Python
try:
    user = User.objects.get(name="slene")
except User.MultipleObjectsReturned as err: 
    # Have multiple records
    print("Returned Multi Rows Not One")
except User.DoesNotExist as err:
    # No result 
    print("Not row found")

Relational Query

User and Profile is OnToOne relation

Go
user := &User{Id: 1}
o.Read(user)
if user.Profile != nil {
    o.Read(user.Profile)
}
Python
user = User.objects.get(id=1)
if user.profile:
    pass
Go
user := &User{}
o.QueryTable("user").Filter("Id", 1).RelatedSel().One(user)
// Get Profile automatically
fmt.Println(user.Profile)
// Because In Profile we defined reverse relation User, Profile's User is also auto assigned. Can directly use:
fmt.Println(user.Profile.User)
Python
user = User.objects.get(id=1)
println(user.profile.user)

Post and User are ManyToOne relation. i.e.: ForeignKey is User

Go
type Post struct {
    Id    int
    Title string
    User  *User  `orm:"rel(fk)"`
    Tags  []*Tag `orm:"rel(m2m)"`
}
Python
class Post(models.Model):
    title = models.CharField(null=False, max_length=255)
    user = models.ForeignKey("User", related_name='posts', null=False)
    tags = models.ManyToManyField("Tag", related_name='posts', null=False)

    class Meta:
        db_table = 'post'
Go
var posts []*Post
num, err := o.QueryTable("post").Filter("User", 1).RelatedSel().All(&posts)
if err == nil {
    fmt.Printf("%d posts read\n", num)
    for _, post := range posts {
        fmt.Printf("Id: %d, UserName: %d, Title: %s\n", post.Id, post.User.UserName, post.Title)
    }
}
Python
posts = Post.objects.filter(user=1)
if posts:
    print("%d posts read\n" % len(posts))
    for post in posts:
        print("Id: %d, UserName: %d, Title: %s\n" % (post.id, post.user.username, post.title))
Go
var user User
err := o.QueryTable("user").Filter("Post__Title", "The Title").Limit(1).One(&user)
if err == nil {
    fmt.Printf(user)
}
Python
try:
    user = User.objects.get(posts__title="The Title")
    print(user)
except User.DoesNotExist:
    pass

Post and Tag are ManyToMany relation

M2MはDjangoRailsの間の子みたいな感じ

Djangoぽく書いた場合
Go
type Post struct {
    Id    int
    Title string
    User  *User  `orm:"rel(fk)"`
    Tags  []*Tag `orm:"rel(m2m)"`
}
type Tag struct {
    Id    int
    Name  string
    Posts []*Post `orm:"reverse(many)"`
}
Python
class Post(models.Model):
    title = models.CharField(null=False, max_length=255)
    user = models.ForeignKey("User", related_name='posts', null=False)
    tags = models.ManyToManyField("Tag", related_name='posts', null=False)

    class Meta:
        db_table = 'post'
class Tag(models.Model):
    name = models.CharField(null=False, max_length=255)

    class Meta:
        db_table = 'tag'
Railsぽく書いた場合

中間テーブルでIndexとか自由に付けられる

Go
type Post struct {
    Id    int64  `orm:"auto"`
    Title string `orm:"size(255)"`
    User  *User  `orm:"rel(fk);index"`
    Tags  []*Tag `orm:"rel(m2m);index;rel_through(example.com/ikeikeikeike/unk/models.PostTag)"`
}
type PostTag struct {
    Id    int64  `orm:"auto"`
    Tag   *Tag   `orm:"rel(fk);index"`
    Post  *Post  `orm:"rel(fk);index"`
}
type Tag struct {
    Id    int64
    Name  string
    Posts []*Post `orm:"reverse(many)"`
}
Ruby(On Rails)
class Post < ActiveRecord::Base
  belongs_to :user, touch: true

  has_many :post_tags
  has_many :tags, through: :post_tags
end
class PostTag < ActiveRecord::Base
  belongs_to :post
  belongs_to :tag
end
class Tag < ActiveRecord::Base
  has_many :post_tags
  has_many :posts, through: :post_tags
end
Go
var posts []*Post
num, err := dORM.QueryTable("post").Filter("Tags__Tag__Name", "golang").All(&posts)
var tags []*Tag
num, err := dORM.QueryTable("tag").Filter("Posts__Post__Title", "Introduce Beego ORM").All(&tags)
Python
Post.objects.filter(tags__tag__name="golang")
Tag.objects.filter(posts__post__title="Introduce Beego ORM")
Ruby(On Rails)
Post.joins(:tags).where("tags.name" => "golang")
Tag.joins(:posts).where("posts.title" => "Introduce Beego ORM")

Load Related Field

Handling ManyToMany relation

Go
o := orm.NewOrm()
post := Post{Id: 1}
m2m := o.QueryM2M(&post, "Tags")
// In the first param object must have primary key
// The second param is the M2M field will work with
// API of QueryM2Mer will used to Post with id equals 1
Python
post = Post.objects.get(id=1)
m2m = post.tags

QueryM2Mer Add

Go
tag := &Tag{Name: "golang"}
o.Insert(tag)

num, err := m2m.Add(tag)
if err == nil {
    fmt.Println("Added nums: ", num)
}
Python
tag = Tag(name="golang")
tag.save()

m2m.add(tag)  # return None
TODO: 各例に一言説明文を書くように努力する

続きはまた明日