model.go

func (m NodeModel) List(page int, pagesize int, catalog int) ([]NodeModel, int, error) {
	result := []NodeModel{}
	tx, err := db.Begin()
	if err != nil {
		return nil, 0, err
	}
	defer tx.Rollback()
	var params, countParams []interface{}
	querySelect := `select 
	node.id,node.title,node.created_time,node.updated_time,node.url,node.picture,node.category,node.nodetype,node.keyword,node.content,
	nodecategory.id,nodecategory.keyword,nodecategory.title,nodecategory.nodetype,nodecategory.comment 
	FROM node,nodecategory `
	queryWhere := "where node.category=nodecategory.id "
	if catalog > -1 {
		queryWhere = queryWhere + " AND node.category= ? "
		params = append(params, catalog)
		countParams = params
	}
	queryOther := " ORDER by node.created_time DESC LIMIT ? OFFSET ?"
	offset := pagesize * (page - 1)
	params = append(params, pagesize, offset)
	stmt, err := tx.Prepare(querySelect + queryWhere + queryOther)
	if err != nil {
		return nil, 0, err
	}
	defer stmt.Close()
	var rows *sql.Rows
	rows, err = stmt.Query(params...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	for rows.Next() {
		nm := NodeModel{}
		nm.CategoryModel = CategoryModel{}
		err = rows.Scan(
			&nm.Id, &nm.Title, &nm.CreatedTime, &nm.UpdatedTime, &nm.Url, &nm.Picture, &nm.Category, &nm.Nodetype, &nm.Keyword, &nm.Content,
			&nm.CategoryModel.Id, &nm.CategoryModel.Keyword, &nm.CategoryModel.Title, &nm.CategoryModel.Nodetype, &nm.CategoryModel.Comment)
		if err != nil {
			return nil, 0, err
		}
		result = append(result, nm)
	}
	queryCount := `SELECT count(*) FROM node,nodecategory `
	stmt2, err := tx.Prepare(queryCount + queryWhere)
	if err != nil {
		return nil, 0, err
	}
	defer stmt2.Close()
	var count int
	row := stmt2.QueryRow(countParams...)
	err = row.Scan(&count)
	if err != nil {
		return nil, 0, err
	}
	return result, count, nil
}