func QueryBooks(limit int, offset int) (books []Book) { db, err := GetBookDB() if err != nil { panic(err) } defer db.Close() qb, _ := orm.NewQueryBuilder(driverName) qb.Select("ID", "NAME", "TAGS").From(tableName).OrderBy("ID").Limit(limit).Offset(offset) logs.Logger.Info(qb.String()) rows, err := db.Query(qb.String()) if err != nil { panic(err) } defer rows.Close() books = []Book{} for rows.Next() { book := Book{} err = rows.Scan(&book.Id, &book.Name, &book.Tags) if err != nil { panic(err) } books = append(books, book) } return books }
func SearchUser(key string, limit int) *[]UserSearchModel { var users []UserSearchModel // 获取 QueryBuilder 对象. 需要指定数据库驱动参数。 // 第二个返回值是错误对象,在这里略过 qb, _ := orm.NewQueryBuilder("mysql") // 构建查询对象 qb.Select( "user.id user_id", "user.user_name", "user_profile.*", ). From( "user", ). InnerJoin( "user_profile", ). On( "user.user_profile_id = user_profile.id", ). Where("user_profile.identity like ? or user_profile.real_name like ? or user.user_name like ? or user_profile.phone like ?"). Limit(limit).Offset(0) // 导出SQL语句 sql := qb.String() // 执行SQL语句 o := orm.NewOrm() o.Raw(sql, key, key, key, key).QueryRows(&users) return &users }
func SearchGroup(key string, limit int) *[]GroupSearchModel { var groups []GroupSearchModel // 获取 QueryBuilder 对象. 需要指定数据库驱动参数。 // 第二个返回值是错误对象,在这里略过 qb, _ := orm.NewQueryBuilder("mysql") // 构建查询对象 qb.Select( "`group`.id group_id", "`group`.group_name", "group_profile.*", ). From( "`group`", ). InnerJoin( "group_profile", ). On( "`group`.group_profile_id = group_profile.id", ). Where("`group`.group_name like ? or group_profile.group_real_name like ? or group_profile.contact_phone like ? or group_profile.contact_name like ?"). Limit(limit).Offset(0) // 导出SQL语句 sql := qb.String() // 执行SQL语句 o := orm.NewOrm() o.Raw(sql, key, key, key, key).QueryRows(&groups) return &groups }
func (this *TrackingEvent) QueryEvent(cvid string, guid string, startDate time.Time, endDate time.Time) ([]TrackingEvent, int) { o := orm.NewOrm() qb, _ := orm.NewQueryBuilder("mysql") var conditions []interface{} conditions = append(conditions, startDate) conditions = append(conditions, endDate) qb.Select("*").From("tracking_event"). Where("createDate between ? and ?") if cvid != "" { qb.And("cvid = ?") conditions = append(conditions, cvid) } if guid != "" { qb.And("guid = ?") conditions = append(conditions, guid) } qb.Limit(1000) rows := []TrackingEvent{} o.Raw(qb.String(), conditions).QueryRows(&rows) return rows, len(rows) }
func UpdateTags(filePath string) { tags := ReadXlxs(filePath) logs.Logger.Info(tags) limit := 20 offset := 0 db, err := GetBookDB() if err != nil { panic(err) } defer db.Close() books := QueryBooks(limit, offset) leng := len(books) var buffer bytes.Buffer for leng == limit { for _, book := range books { // logs.Logger.Info("result:", book.Id, book.Name, book.Tags) tag := tags[book.Name] if tag != "" { qb, _ := orm.NewQueryBuilder(driverName) qb.Update(tableName).Set("TAGS = '" + tag + "'").Where("NAME = '" + book.Name + "'") // db.Exec(qb.String()) logs.Logger.Info(qb.String()) buffer.WriteString(qb.String() + ";") } } offset = offset + 1 books = QueryBooks(limit, offset*limit) leng = len(books) } write2File(buffer.String(), "d:\\updatetag.sql") }
// 根据某个字查询统计字段值的求和 func (t *Transaction) Sum(table string, field string, where string, value interface{}) (int64, bool) { qb, _ := orm.NewQueryBuilder("mysql") qb.Select(fmt.Sprintf("SUM(%s)", field)).From(table).Where(where + " = ?") var sum int64 = 0 if err := t.o.Raw(qb.String(), value).QueryRow(&sum); err != nil { return 0, false } return sum, true }
func QueryEndpointidbyNames(endpoints []string, limit int) (enp []Endpoint, err error) { q := orm.NewOrm() q.Using("graph") q.QueryTable("endpoint") qb, _ := orm.NewQueryBuilder("mysql") qt := qb.Select("*").From("endpoint").Where("endpoint").In(endpoints...).Limit(limit) _, err = q.Raw(qt.String()).QueryRows(&enp) return }
func OrmGetAll() (entity []Entity) { qb, _ := orm.NewQueryBuilder("mysql") qb.Select("id", "title", "content", "created").From("entity").Limit(10).Offset(0) sql := qb.String() ormer := orm.NewOrm() ormer.Raw(sql).QueryRows(&entity) logs.Logger.Info("results:", entity) return entity }
func ReadTags(limit int, offset int) (books []Book) { qb, _ := orm.NewQueryBuilder(driverName) qb.Select("NAME", "TAGS").From(tableName).OrderBy("ID").Limit(limit).Offset(offset) qbStr := qb.String() ormer := orm.NewOrm() ormer.Raw(qbStr).QueryRows(&books) logs.Logger.Info("results:", books) return books }
// 统计分类总数 func GetClassifyCount() (classifyCount []*ClassifyCount, err error) { qb, _ := orm.NewQueryBuilder("mysql") qb.Select("t.class_i_d, count(1) num", "c.title").From("topic t").LeftJoin("classify c").On("t.class_i_d = c.id").Where("t.class_i_d in (c.id)").GroupBy("t.class_i_d").OrderBy("num desc") sql := qb.String() o := orm.NewOrm() _, err = o.Raw(sql).QueryRows(&classifyCount) return classifyCount, err }
func QueryUserById(userId int64) *POIUser { var user *POIUser qb, _ := orm.NewQueryBuilder(libs.GetDBDriverName()) qb.Select("id,nickname,avatar,gender,access_right,status,balance,phone,last_login_time").From("users").Where("id = ?") sql := qb.String() o := orm.NewOrm() err := o.Raw(sql, userId).QueryRow(&user) if err != nil { return nil } return user }
func QueryUsers(start, pageCount int) POIUsers { users := make(POIUsers, 0) qb, _ := orm.NewQueryBuilder(libs.GetDBDriverName()) qb.Select("id,nickname,avatar,gender,access_right,status,balance,phone,last_login_time"). From("users").OrderBy("id").Desc().Limit(pageCount).Offset(start) sql := qb.String() o := orm.NewOrm() _, err := o.Raw(sql).QueryRows(&users) if err != nil { return nil } return users }
func QuerySessionById(sessionId int64) *POISession { o := orm.NewOrm() qb, _ := orm.NewQueryBuilder(utils.DB_TYPE) qb.Select("id,order_id, creator, tutor,create_time, plan_time, length, status, rating, comment, time_from, time_to"). From("sessions").Where("id = ?") sql := qb.String() session := POISession{} err := o.Raw(sql, sessionId).QueryRow(&session) if err != nil { seelog.Error("sessionId:", sessionId, " ", err.Error()) return nil } session.Creator = QueryUserById(session.Created) session.Teacher = QueryUserById(session.Tutor) return &session }
func (this *S2sActiveLog) QueryS2sActiveLog(campaignId string, guid string, startDate time.Time, endDate time.Time) ([]S2sActiveLog, int) { var rows []S2sActiveLog o := orm.NewOrm() qb, _ := orm.NewQueryBuilder("mysql") qb.Select("*"). From("s2s_activeLog"). Where("createTime between ? and ?") if campaignId != "" { qb.And("offerId = ?") } if guid != "" { qb.And("guid = ?") } qb.OrderBy("createTime").Desc(). Limit(1000) // param := struct { // CampaignId string // Guid string // StartDate time.Time // EndDate time.Time // } { // CampaignId: campaignId, // Guid: guid, // StartDate: startDate, // EndDate: endDate, // } var vals []interface{} vals = append(vals, startDate) vals = append(vals, endDate) if campaignId != "" { vals = append(vals, campaignId) } if guid != "" { vals = append(vals, guid) } o.Raw(qb.String(), vals).QueryRows(&rows) beego.Debug("Search s2s log count: ", len(rows)) return rows, len(rows) }
func QuerySessions(start, pageCount int) POISessions { o := orm.NewOrm() qb, _ := orm.NewQueryBuilder(utils.DB_TYPE) qb.Select("id,order_id, creator, tutor,create_time ,plan_time, length, status, rating, comment, time_from, time_to"). From("sessions").OrderBy("id").Desc().Limit(pageCount).Offset(start) sql := qb.String() sessions := make(POISessions, 0) _, err := o.Raw(sql).QueryRows(&sessions) if err != nil { return nil } for _, session := range sessions { session.Creator = QueryUserById(session.Created) session.Teacher = QueryUserById(session.Tutor) } return sessions }
func QueryOrderById(orderId int64) *POIOrder { order := POIOrder{} o := orm.NewOrm() db, _ := orm.NewQueryBuilder(utils.DB_TYPE) db.Select("id,creator,grade_id,subject_id,date,period_id,length,type,status,price_per_hour,real_price_per_hour,teacher_id"). From("orders").Where("id = ?") sql := db.String() err := o.Raw(sql, orderId).QueryRow(&order) if err != nil { seelog.Error("orderId:", orderId, " ", err.Error()) return nil } order.Type = OrderTypeRevDict[order.OrderType] creator := QueryUserById(order.Created) order.Creator = creator return &order }
func QueryOrders(start, pageCount int) POIOrders { orders := make(POIOrders, 0) o := orm.NewOrm() db, _ := orm.NewQueryBuilder(utils.DB_TYPE) db.Select("orders.id,orders.creator,orders.grade_id,orders.subject_id,orders.date,orders.period_id,orders.create_time," + "orders.length,orders.type,orders.status,orders.price_per_hour,orders.real_price_per_hour,orders.teacher_id"). From("orders").InnerJoin("users").On("orders.creator = users.id").OrderBy("orders.id").Desc().Limit(pageCount).Offset(start) sql := db.String() _, err := o.Raw(sql).QueryRows(&orders) if err != nil { return nil } for _, order := range orders { order.Type = OrderTypeRevDict[order.OrderType] creator := QueryUserById(order.Created) order.Creator = creator } return orders }
func QueryOrderDispatchByOrderId(orderId int64) POIOrderDispatchs { o := orm.NewOrm() qb, _ := orm.NewQueryBuilder(utils.DB_TYPE) qb.Select("id,order_id,teacher_id,dispatch_time,reply_time,plan_time,result").From("order_dispatch"). Where("order_id = ?") sql := qb.String() orderDispatchs := make(POIOrderDispatchs, 0) _, err := o.Raw(sql, orderId).QueryRows(&orderDispatchs) for _, orderDispatch := range orderDispatchs { orderDispatch.Order = QueryOrderById(orderDispatch.OrderId) orderDispatch.Teacher = QueryUserById(orderDispatch.TeacherId) } if err != nil { seelog.Error("orderId:", orderId, " ", err.Error()) return nil } return orderDispatchs }
func SearchTerminal(key string, limit int) *[]TerminalSearchModel { var terminals []TerminalSearchModel // 获取 QueryBuilder 对象. 需要指定数据库驱动参数。 // 第二个返回值是错误对象,在这里略过 qb, _ := orm.NewQueryBuilder("mysql") // 构建查询对象 qb.Select( "terminal.id terminal_id", "terminal.offline_on", "terminal.online_on", "terminal_profile.*", "terminal_carrier.*", "terminal.terminal_sn", ). From( "terminal", ). InnerJoin( "terminal_profile", ). On( "terminal.terminal_profile_id = terminal_profile.id", ). InnerJoin( "terminal_carrier", ). On( "terminal.terminal_carrier_id = terminal_carrier.id", ). Where("terminal.terminal_sn like ?"). Limit(limit).Offset(0) // 导出SQL语句 sql := qb.String() // 执行SQL语句 o := orm.NewOrm() o.Raw(sql, key).QueryRows(&terminals) return &terminals }
func (this *PostController) ListPosts() { page, err := strconv.Atoi(this.Ctx.Input.Param(":page")) if err != nil { beego.Error(err) } var posts []models.Post qb, _ := orm.NewQueryBuilder("mysql") qb.Select("id", "title", "tag", "publish_at"). From("post"). OrderBy("publish_at").Desc(). Limit(PAGE_SIZE).Offset((page - 1) * PAGE_SIZE) sql := qb.String() o := orm.NewOrm() o.Raw(sql).QueryRows(&posts) this.Data["json"] = posts this.ServeJson() return }
func (this *MainController) ListMessage() { page, err := strconv.Atoi(this.Ctx.Input.Param(":page")) if err != nil { beego.Error(err) } var messages []models.Message qb, _ := orm.NewQueryBuilder("mysql") qb.Select("id", "guest_name", "reply", "content", "post_title", "created_at"). From("message"). OrderBy("created_at").Desc(). Limit(PAGE_SIZE + 10).Offset((page - 1) * PAGE_SIZE) sql := qb.String() o := orm.NewOrm() o.Raw(sql).QueryRows(&messages) this.Data["json"] = messages this.ServeJson() return }
/** groupFields might be: 1. nil, 2. ["0"] 3. ["1"] 4 ["0", "1"] 0 表示 PDB广告位 1 表示 日期 */ func GetGroupedPmpDemandDailyReport(groupFields []string, medias []string, startDate time.Time, endDate time.Time, sortby string, order string, offset int, limit int) (ml []PdbDemandReportVo, count int, err error) { o := orm.NewOrm() qb, _ := orm.NewQueryBuilder("mysql") selectFields := []string{ "ddr.ad_date", "pda.demand_id as demand_id", "dpd.name as demand_name", "pda.id as demand_adspace_id", "pda.name as demand_adspace_name", "pmp_media.id as pmp_media_id", "pmp_media.name as pmp_media_name", "pmp_adspace.id as pmp_adspace_id", "pmp_adspace.name as pmp_adspace_name", "sum(ddr.req_success) as req_success", "sum(ddr.req_noad) as req_noad", "sum(ddr.req_timeout) as req_timeout", "sum(ddr.req_error) as req_error", "sum(pdr.imp) as imp", "sum(pdr.clk) as clk", } possibleGroupFields := map[string]string{ "0": "pda.demand_id", "1": "pda.id", "2": "pmp_media.id", "3": "pmp_adspace.id", } groupby := "ddr.ad_date" if groupFields != nil && len(groupFields) > 0 { for _, fldIdx := range groupFields { groupby += "," + possibleGroupFields[fldIdx] } } qb.Select(strings.Join(selectFields, ", ")). From("pmp_demand_daily_report ddr"). InnerJoin("pmp_daily_report pdr").On("ddr.demand_adspace_id=pdr.demand_adspace_id"). InnerJoin("pmp_adspace").On("pdr.pmp_adspace_id=pmp_adspace.id"). InnerJoin("pmp_media").On("pmp_adspace.media_id=pmp_media.id"). InnerJoin("pmp_demand_adspace pda").On("pda.id=ddr.demand_adspace_id"). InnerJoin("pmp_demand_platform_desk dpd").On("pda.demand_id=dpd.id") qb.Where("1=1") // if medias != nil { // qb.And("pmp_media.id in (" + strings.Join(medias, ",") + ")") // } qb.And("ddr.ad_date >= ?") qb.And("ddr.ad_date <= ?") qb.GroupBy(groupby) // order by: if sortby != "" { qb.OrderBy(sortby) if order == "desc" { qb.Desc() } else { qb.Asc() } } // TODO default order by ??? qbCount, _ := orm.NewQueryBuilder("mysql") qbCount.Select("count(*) as cnt"). From("(" + qb.String() + ") as sub") var countResult []orm.Params o.Raw(qbCount.String(), startDate, endDate).Values(&countResult) count, _ = strconv.Atoi(countResult[0]["cnt"].(string)) qb.Limit(limit) qb.Offset(offset) report := []PdbDemandReportVo{} o.Raw(qb.String(), startDate, endDate).QueryRows(&report) return report, count, err }
func NewQueryBuilder() (orm.QueryBuilder, error) { result, err := orm.NewQueryBuilder(beego.AppConfig.String(DB_DRIVER)) return result, err }