个人站点页面设计(ORM跨表与分组查询)
日期归档查询(知识点--个人站点页面的日期查询)
1 date_format
======================date,time,datetime===============
create table t_mul_new (d date,t time,dt datetime);
insert into t_mul values(now(),now(),now());
insert * from t_mul;
mysql > select * from t_mul;
mysql> use test2 Database changed mysql> mysql> create table t_mul_new(d date, t time, dt datetime); Query OK, 0 rows affected (2.11 sec) mysql> insert into t_mul_new values(now(), now(), now()); Query OK, 1 row affected, 1 warning (0.49 sec) mysql> select * from t_mul_new; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2018-08-02 | 22:59:15 | 2018-08-02 22:59:15 | +------------+----------+---------------------+ 1 row in set (0.00 sec) mysql> select dt from t_mul_new; +---------------------+ | dt | +---------------------+ | 2018-08-02 22:59:15 | +---------------------+ 1 row in set (0.00 sec) mysql> select date_format(dt, "%Y-%m") from t_mul_new; +--------------------------+ | date_format(dt, "%Y-%m") | +--------------------------+ | 2018-08 | +--------------------------+ 1 row in set (0.05 sec) mysql> select date_format(dt, "%Y-%m-%d") from t_mul_new; +-----------------------------+ | date_format(dt, "%Y-%m-%d") | +-----------------------------+ | 2018-08-02 | +-----------------------------+ 1 row in set (0.00 sec) mysql> select date_format(dt, "%Y/%m/%d") from t_mul_new; +-----------------------------+ | date_format(dt, "%Y/%m/%d") | +-----------------------------+ | 2018/08/02 | +-----------------------------+ 1 row in set (0.00 sec) mysql>
2.extra
models.Article.objects(拿到所有的文章,等同于objects.all).extra(循环Article里边的每一个对象,给它加一个键值,键是“is_recent”,值是每一个Article对应执行的sql语句,写ORM的时候从来没这么写过,这个sql语句会对应每一个Article对象进行执行,执行一次统计出来一个结果如果大于了就返回1小于了就返回0;处理完的结果依然是Queryset,不同的是每一个Article对象多了个属性叫is_recent)
TruncMonth函数,例子:Sales商品表,按照之前就进行分组统计了,但它并没有,timestamp把它理解为create_time,TruncMonth把它截断只截到年月,然后赋值给了month字段;跟方式一是一样的,方式一用date_format来截断赋给了一个叫y_m_date这样子一个键。Sales里边就多加了一个叫month的属性。.values就相当于Group By month,再annotate统计哪个字段的数量,再最终显示month和c值。
1.个人站点信息的查询
views.py
def home_site(request, username): ''' 个人站点视图 :param request: :param username: :return: ''' print("username", username) user = UserInfo.objects.filter(username = username).first() #过滤,判断是否有这个名字 #判断用户是否存在 if not user: return render(request, "not_found.html") #查询当前站点对象blog blog = user.blog #个人站点页面的文章查询 #当前用户或者当前站点对应所有文章 (article找user是正向查询) #基于对象查询 #articile_list = user.article_set.all() #基于__ articile_list = models.Article.objects.filter(user=user) #user_id,你写user其实是过滤user_id是否等于你刚传进来的user_id
#个人站点页面的标签与分类查询 #每一个后的模型.objects.values("pk").annotate(聚合函数(关联表__统计字段)).values("表模型的所在字段") #查询每一个分类名称以及对应的文章数 ret = models.Category.objects.values("pk").annotate(c = Count("article__title")).values("title", "c") print(ret) #<QuerySet [{'title': 'egon的web', 'c': 2}, {'title': 'egon的sql', 'c': 1}, {'title': 'alex的微信小程序', 'c': 1}]> #查询当前站点的每一个分类名称以及对应的文章数 (你写blog=blog, blog_id=blog_id它们是等效的;你已经有了个blog对象) cate_list = models.Category.objects.filter(blog=blog).annotate(c = Count("article__title")).values_list("title", "c") #values是存元组.values("title", "c"),values_list是存字典 print(cate_list) #<QuerySet [{'title': 'egon的web', 'c': 2}, {'title': 'egon的sql', 'c': 1}]>
#查询当前站点的每一个标签名称以及对应文章数 .annotate(Count("article__name")) 反向查询 tag_list = models.Tag.objects.filter(blog=blog).values("pk").annotate(c = Count("article")).values_list("title","c") #每个标签自己的名字以及统计出来的文章数 print(tag_list) #<QuerySet [('web', 3), ('数据库', 1), ('服务器', 1)]>;
#个人站点页面的日期查询 #查询当前站点每一个年月的名称以及对应的文章数 (新加一个字段的单表查询;你如果按create_time,它是年月日时分秒,根本没法分类分在一块) #ret = models.Article.objects.extra(select={"is_recent": "create_time > '2018-09-05'"}).values("title", "is_recent") #print(ret) #<QuerySet [{'is_recent': 0, 'title': '猫眼电影App抓包获取评论数据接口'}, {'is_recent': 0, 'title': 'RESTful源码笔记之RESTful Framework的Mixins小结'}, {'is_recent': 0, 'title': '微信小程序开发04-打造自己的UI库'}, {'is_recent': 0, 'title': 'redis系列--主从复制以及redis复制演进'}]>
#“y_m_date”只是自己起的名字;mysql里边是date_format;sqlite里边是strftime #ret = models.Article.objects.extra(select={"y_m_d_date": "date_format(create_time,'%%Y-%%m-%%d')"}).values("title","y_m_d_date") #print(ret)#<QuerySet [{'y_m_d_date': '2018-08-01', 'title': '猫眼电影App抓包获取评论数据接口'}, {'y_m_d_date': '2018-08-01', 'title': 'RESTful源码笔记之RESTful Framework的Mixins小结'}, {'y_m_d_date': '2018-08-01', 'title': '微信小程序开发04-打造自己的UI库'}, {'y_m_d_date': '2018-08-02', 'title': 'redis系列--主从复制以及redis复制演进'}]> #方式一 date_list = models.Article.objects.filter(user=user).extra(select={"y_m_date": "date_format(create_time,'%%Y-%%m-%%d')"}).values("y_m_date").annotate(c=Count("nid")).values("y_m_date","c") print(date_list) #<QuerySet [{'y_m_date': '2018-08-01', 'c': 2}, {'y_m_date': '2018-08-02', 'c': 1}]> #方式二(导入TruncMonth函数) from django.db.models.functions import TruncMonth ret = models.Article.objects.filter(user=user).annotate(month = TruncMonth("create_time")).values("month").annotate(c=Count("nid")).values_list("month","c") print("ret---->", ret) #ret----> <QuerySet [(datetime.datetime(2018, 8, 1, 0, 0), 3)]> 要把settings里边改成USE_TZ = False;不然会报错 return render(request, "home_site.html")
在个人站点的视图函数中构建数据--->>传到home_site模板中进行渲染
2.个人站点页面的渲染布局
home_site.html
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> <style> *{ //加一些样式 margin:0; padding:0; } .header{ width:100%; height: 60px; background-color: #369; } .header .title{ font-size: 18px; font-weight: 100; line-height: 60px; color: white; margin-left: 15px; margin-top: -10px;; } .backend{ float:right; color: white; text-decoration: none; font-size: 16px; margin-right:10px; margin-top: 10px;; } .pub_info{ margin-top: 10px; color: darkgrey; } </style> <link rel="stylesheet" href="/static/blog/bootstrap/css/bootstrap.css"> </head> <body> <div class="header"> <div class="content"> <p class="title"> <span>{{ blog.title }}</span> <a href="" class="backend">管理</a> </p> </div> </div> <div class="container"> <div class="row"> <div class="col-md-3"> <div class="panel panel-warning"> <div class="panel-heading">我的标签</div> <div class="panel-body"> {% for tag in tag_list %} <p>{{ tag.0 }}({{ tag.1 }})</p> {% endfor %} </div> </div> <div class="panel panel-danger"> <div class="panel-heading">随笔分类</div> <div class="panel-body"> {% for cate in cate_list %} <p>{{ cate.0 }}({{ cate.1 }})</p> {% endfor %} </div> </div> <div class="panel panel-success"> <div class="panel-heading">随笔归档</div> <div class="panel-body"> {% for date in date_list %} <p>{{ date.0 }}({{ date.1 }})</p> {% endfor %} </div> </div> </div> <div class="col-md-9"> <div class="article_list"> {% for article in article_list %} <div class="article-item clearfix"> <h5><a href="">{{ article.title }}</a> </h5> <div class="article-desc"> {{ article.desc }} </div> <div class="small pub_info pull-right"> <span>发布于 {{ article.create_time|date:"Y-m-d H:i" }}</span> <span class="glyphicon glyphicon-comment"></span>评论({{ article.comment_count }}) <span class="glyphicon glyphicon-thumbs-up"></span>点赞({{ article.up_count }}) </div> </div> <hr> {% endfor %} </div> </div> </div> </div> </body> </html>
views.py
#方式一 #date_list = models.Article.objects.filter(user=user).extra(select={"y_m_date": "date_format(create_time,'%%Y-%%m-%%d')"}).values("y_m_date").annotate(c=Count("nid")).values("y_m_date","c") date_list = models.Article.objects.filter(user=user).extra(select={"y_m_date": "date_format(create_time,'%%Y-%%m-%%d')"}).values("y_m_date").annotate(c=Count("nid")).values_list("y_m_date","c") #取字典必须按照字典的取法进行渲染 print(date_list) #<QuerySet [{'y_m_date': '2018-08-01', 'c': 2}, {'y_m_date': '2018-08-02', 'c': 1}]> return render(request, "home_site.html", {"blog": blog,"article_list":article_list,
"cate_list": cate_list, "tag_list": tag_list, "date_list": date_list}) #视图层要传
3.个人站点页面的跳转过滤功能的实现
urls.py (路由设置)
urlpatterns = [ path('admin/', admin.site.urls), path('login/',views.login), path('index/',views.index), path('logout/', views.logout), re_path('^$',views.index), #在首页不用index也可以显示出 path('get_validCode_img/',views.get_validCode_img), path('register/',views.register), #media配置 re_path(r"media/(?P<path>.*)$", serve, {"document_root":settings.MEDIA_ROOT}), #个人站点的跳转 根路径+直接加username,w+包含数字和字母,不包含特殊符号;有名分组; .* 传的时候让它能够识别出特殊符号 / ,任何符号都可以匹配的参数param组(如2018/05.html) re_path('^(?P<username>\w+)/(?P<condition>tag|category|archive)/(?P<param>.*)/$', views.home_site), #home_site(request,username="egon",condition="tag",param="python") #路由设置 #个人站点url re_path('^(?P<username>\w+)$', views.home_site), #home_site(request,username="egon") 上边home_site要接收4个参数,匹配到它的接收2个参数 ]
views.py(视图部分)
def home_site(request, username, **kwargs): #一个是接收2个参数,一个是接收4个参数;先接收2个,如果再有就放到**kwargs; ''' 个人站点视图 :param request: :param username: :return: ''' #区分访问的是站点页面还是站点下的跳转页面 print("kwargs", kwargs) #kwargs {'condition': 'tag', 'param': 'web'} #访问: 127.0.0.1:8000/egon/tag/web/ print("username", username) #username egon user = UserInfo.objects.filter(username = username).first() #过滤 #判断用户是否存在 if not user: return render(request, "not_found.html") #查询当前站点对象 blog = user.blog #当前用户或者当前站点对应所有文章 #基于对象查询 #articile_list = user.article_set.all() #基于__ if kwargs: #如果有值,就走那个跳转。 condition = kwargs.get("condition") param = kwargs.get("param") #2012-12 if condition == "category": #http://127.0.0.1:8000/egon/category/egon的web/ article_list = models.Article.objects.filter(user=user).filter(category__title=param) elif condition == "tag": #http://127.0.0.1:8000/egon/tag/web/ article_list = models.Article.objects.filter(user=user).filter(tags__title=param) else: year, month = param.split("-") #http://127.0.0.1:8000/egon/archive/2018-07/ article_list = models.Article.objects.filter(user=user).filter(create_time__year=year, create_time__month=month) #在settings里边要把改成:USE_TZ=False,时区问题 else: #如果没值就走个人站点 article_list = models.Article.objects.filter(user=user) #user_id
优化下
实现点击跳转功能(点击标签、分类可以进行跳转)
home_site.html
<div class="container"> <div class="row"> <div class="col-md-3"> <div class="panel panel-warning"> <div class="panel-heading">我的标签</div> <div class="panel-body"> {% for tag in tag_list %} <p><a href="/{{ username }}/tag/{{ tag.0 }}">{{ tag.0 }}({{ tag.1 }})</a></p> {% endfor %} </div> </div> <div class="panel panel-danger"> <div class="panel-heading">随笔分类</div> <div class="panel-body"> {% for cate in cate_list %} <p><a href="/{{ username }}/category/{{ cate.0 }}"> {{ cate.0 }}({{ cate.1 }})</a></p> {% endfor %} </div> </div> <div class="panel panel-success"> <div class="panel-heading">随笔归档</div> <div class="panel-body"> {% for date in date_list %} <p><a href="/{{ username }}/archive/{{ date.0 }}"> {{ date.0 }}({{ date.1 }})</a></p> {% endfor %} </div> </div> </div> <div class="col-md-9"> <div class="article_list"> {% for article in article_list %} <div class="article-item clearfix"> <h5><a href="">{{ article.title }}</a> </h5> <div class="article-desc"> {{ article.desc }} </div> <div class="small pub_info pull-right"> <span>发布于 {{ article.create_time|date:"Y-m-d H:i" }}</span> <span class="glyphicon glyphicon-comment"></span>评论({{ article.comment_count }}) <span class="glyphicon glyphicon-thumbs-up"></span>点赞({{ article.up_count }}) </div> </div> <hr> {% endfor %} </div> </div> </div> </div>