|
我正在尝试为博客文章生成一个存档列表。存档列表应按相反的时间顺序显示年份和日期,如下所示:0 ?" V2 a$ X8 n* ^
2013 (21)1 K' q) i- W, ]2 Q! g
- May (2): e& Z6 R% Y2 M+ A2 |' v4 q, b
- April (3)
3 v# B$ |( A0 L( { - March (5)9 B1 _8 e g/ j) M+ q6 N
- February (1)1 g; \$ h2 J& U1 Y0 P; A1 p
- January (10)
& i9 x R0 t- B- h' x+ p4 \2012 (10); {1 {: p- j+ b o3 V
- December (6)& O- e( p* D( H3 U, ^$ Q
- November (4)
7 {3 S3 D% q F$ s4 ^里面()的数字是该时间段内的帖子数。选择年份或月份后,仅应显示该选定时间段内的博客文章。 u' v' d3 F* v; b2 F0 h
到目前为止,我只能通过执行以下操作来找出每篇博客文章的年份和月份:
' O" z( I6 R* G) h# z8 N2 E! T$posts = Post::all();. z- \ `$ d: ^! C
$archive = array();) X$ n6 u& r) _8 q1 k, C
foreach ($posts as $post) {
" O& I- q5 {4 C$ U5 v2 K( n7 i $year = date('Y', strtotime($post->created_at));9 P% f) a" K0 |5 r0 ~. X
$month = date('m', strtotime($post->created_at));
' P3 c, E) f% r) P8 i5 J}
$ M% {7 z( g. i# n$ b我如何实现上述目标?0 T: M& g& T7 p4 [1 ^; g% S9 V4 A
' @ d. V1 H! j& I$ K! L- f+ G
解决方案:; E# p. w4 p" x3 ]6 \& p6 f
5 H' b/ H2 }9 a
* G- w# z5 ]2 ]! Q- O+ B6 R
; U3 q6 j, G! @( b; x4 t
为了在某??种导航面板中生成链接,您可以在数据库端进行大部分处理,而不用这样的查询来获取所有博客文章记录
^; Q4 c h+ M8 Y1 Q* L) i0 x* f+ ]: MSELECT YEAR(created_at) year,
/ [; S! y/ H+ ^& V MONTH(created_at) month,- Z, L! Y- e# n: c
MONTHNAME(created_at) month_name,
' m* v1 |$ o! q& a COUNT(*) post_count
0 Y. W. E6 X- M7 `! d6 _0 O FROM post
/ f! g C4 d! g* B( H, u6 Q GROUP BY year, MONTH(created_at)
6 r s7 X; J: t/ t ORDER BY year DESC, month DESC;
8 g' ~& [8 Z# C输出:
6 F+ d0 y P, B+ j2 Q" M5 l| YEAR | MONTH | MONTH_NAME | POST_COUNT |
$ S5 ]8 @/ d* N* X* k7 |8 r------------------------------------------
& o! n0 Y. F7 v$ H4 j: w- ^| 2013 | 5 | May | 5 |
" }( }3 T( d3 `+ M% c$ X) u| 2013 | 4 | April | 3 |- O% I) y& F- Z. \. F$ M. W6 G
| 2013 | 3 | March | 4 |( x" g/ R1 k' [# P. O+ h# N
| 2013 | 2 | February | 3 |$ n* g8 T7 s2 L
| 2013 | 1 | January | 2 |
$ ]! O0 A6 m! s- C| 2012 | 12 | December | 2 |
" k0 e9 M1 M+ V+ u) Q| 2012 | 11 | November | 3 |
5 w+ p& A; C" Y' i7 T我不是laravel的专家,但是应该使用类似的方法来实现
* n/ V6 b) q$ T( S0 L$links = DB::table('post')+ n% q. |/ B2 N. j5 ^
->select(DB::raw('YEAR(created_at) year, MONTH(created_at) month, MONTHNAME(created_at) month_name, COUNT(*) post_count'))
6 L( ^9 n- s/ f3 x3 C* ^3 ? ->groupBy('year')
, c0 t' P2 T/ J" ` ->groupBy('month')2 Z- [) _1 f7 Q7 O5 D( E
->orderBy('year', 'desc')
* Q+ `9 _+ a# X9 J. b: h2 D ->orderBy('month', 'desc')
d2 {9 A/ Q4 [3 s/ L ->get();
3 R& _; q% ~; L" T; D如果需要,您可以像这样将小计添加到年行中
6 t2 H% ^4 l9 \ S" QSELECT YEAR(created_at) year,+ G, \7 V7 c/ q0 c/ F
MONTH(created_at) month,, W- M4 c J- V7 u# v. l L
MONTHNAME(created_at) month_name,
) ^) ?5 f$ A- A; |( q COUNT(*) post_count3 {7 M1 @( o3 v4 F( p
FROM post* c+ U* P) ?2 e n
GROUP BY year, MONTH(created_at)
/ V$ p, l) ?8 ]: O. LUNION ALL: @+ K' r$ a9 P( i: t( B& T I
SELECT YEAR(created_at) year,
% j0 {( @ }& {) R8 E 13 month,: R/ o3 l1 Y# G! ^" E; x' K- R$ r- _
NULL month_name,: f7 T% ?! Z% v% }: n
COUNT(*) post_count
Q* n% f6 c Y" o FROM post
- o7 ^0 Q! |; x' m$ P GROUP BY year, y' [! J2 D) V Y
ORDER BY year DESC, month DESC;4 E- I( ?, i W5 Y/ d
输出:
9 K5 ^6 G5 h, t: E$ l3 y| YEAR | MONTH | MONTH_NAME | POST_COUNT |
1 e d$ _' M- @, X( o0 E4 | Q P% Y------------------------------------------
6 L! K# M2 n2 k3 K3 D0 k- T% w/ }8 || 2013 | 13 | (null) | 17 |
# v' f) `* f% f' D* F# j' L# {: V| 2013 | 5 | May | 5 |- J" E0 F9 X; p! ?
| 2013 | 4 | April | 3 | n3 ~4 u. l8 j+ ?1 ~# S: |
| 2013 | 3 | March | 4 |
1 U7 N2 Y9 H" ?6 C| 2013 | 2 | February | 3 |1 @. ~1 R1 K: K) y# S
| 2013 | 1 | January | 2 |
5 g+ D. l" z6 E7 `6 u/ s| 2012 | 13 | (null) | 5 |
) @1 J' n% Y, B l) \/ W% a| 2012 | 12 | December | 2 |
/ `! l1 S1 [) C$ D+ ]/ s9 v$ i| 2012 | 11 | November | 3 |
" y: N R# s! Z% ]4 MSQLFiddle |
|