10.2 我的礼物（赠送清单）

1.业务逻辑分析

赠送清单的业务逻辑如下

image.png

其中复杂在于第二点，实现有以下两种思路

image.png

如果循环次数可以控制，比如10次，100次，那么我们还可以接受，但是这个循环次数是掌握在用户手里的，所以第一种方案是不能够接受的。我们采取第二种方案

2.代码编写

models/gift.py 对原始数据的获取

    @classmethod
    def get_user_gifts(cls, uid):
        gifts = Gift.query \
            .filter_by(uid=uid, launched=False) \
            .order_by(desc(Gift.create_time)) \
            .all()
        return gifts

    @classmethod
    def get_wish_counts(cls, isbn_list):
        # 根据传入的一组isbn编号，到Wish表中计算出某个礼物的Wish心愿数量
        # select count(id),isbn from wish
        # where launched = false and isbn in ('','') and status =1 group by isbn
        count_list = db.session.query(func.count(Wish.id), Wish.isbn).filter(
            Wish.launched == False,
            Wish.isbn.in_(isbn_list),
            Wish.status == 1).group_by(
            Wish.isbn).all()
        # 不要将tuple返回到外部，应该返回有意义的字典或者对象
        count_list = [{'count': w[0], 'isbn':w[1]} for w in count_list]
        return count_list

view_model/gift.py 对原始数的裁剪包装

class Gifts:

    def __init__(self, gifts_of_mine, wish_count_list):
        self.gifts = []
        self.__gifts_of_mine = gifts_of_mine
        self.__wish_count_list = wish_count_list

        self.gifts = self.__parse()

    def __parse(self):
        temp_gifts = []
        for gift in self.__gifts_of_mine:
            my_gift = self.__matching(gift)
            temp_gifts.append(my_gift)
        # 不应该在函数内部对对象的属性进行修改，应该返回给外部，在外部进行复制
        # 这是因为如果函数比较复杂，我们根本不知道对象的属性是在哪个函数中修改的
        return temp_gifts

    def __matching(self, gift):
        count = 0
        for wish_count in self.__wish_count_list:
            if gift.book == wish_count['isbn']:
                count = wish_count.count
        r = {
            'wishes_count': count,
            'book': BookViewModel(gift.book),
            'id': gift.id
        }
        return r

web/gift.py 对视图函数进行组装

@web.route('/my/gifts')
@login_required
def my_gifts():
    uid = current_user.id
    gifts_of_mine = Gift.get_user_gifts(uid)
    isbn_list = [gift.isbn for gift in gifts_of_mine]
    wish_count_list = Gift.get_wish_counts(isbn_list)
    view_model = Gifts(gifts_of_mine, wish_count_list)
    return render_template('my_gifts.html', gifts=view_model.gifts)

上面获取原始数据，是对两张表分别查询，再组装，我们也可以进行连表查询，下面是两种方式

    # 直接进行sql查询
    @classmethod
    def get_user_gifts_by_sql(cls, uid):
        sql = 'select a.id,a.isbn,count(b.id)' \
              'from gift a left join wish b on a.isbn = b.isbn ' \
              'where b.uid = %s and a.launched = 0 and b.launched = 0 ' \
              'and a.status = 1 and b.status = 1 ' \
              'group by a.id,a.isbn order by a.create_time desc'.replace('%s', str(uid))
        gifts = db.session.execute(sql)
        gifts = [{'id': line[0], 'isbn': line[1], 'count':line[2]} for line in gifts]
        return gifts

    # 使用SQLAlchemy提供的多表查询的方式
    @classmethod
    def get_user_gifts_by_orm(cls, uid):
        gifts = db.session\
            .query(Gift.id, Gift.isbn, func.count(Wish.id))\
            .outerjoin(Wish, Wish.isbn == Gift.isbn)\
            .filter(
                Gift.launched == False,
                Wish.launched == False,
                Gift.status == 1,
                Wish.status == 1,
                Gift.uid == uid)\
            .group_by(Gift.id, Wish.isbn)\
            .order_by(desc(Gift.create_time))\
            .all()
        gifts = [{'id': line[0], 'isbn': line[1], 'count':line[2]} for line in gifts]
        return gifts