Cakephp中自定义查询的分页问题，解决方法
时间：2010-07-16 来源：zhb1208
转载 Cakephp中自定义查询的分页问题，解决方法 收藏
Cakephp的默认分页功能是基于内置的表关联模型的，所以，如果我们有更多复杂的要求，比如：
联合两张表的查询分页
这样Cakephp的默认分页就达不到我们的要求，解决的方法是：
使用自定义的查询分页
使用自定义查询分页需要在模型中重载两个方法，paginate()和paginateCount()
1. paginate()
提供分页的数据支持，原型如下：
1. function paginate( $conditions , $fields , $order , $limit , $page = 1, $recursive = null);
参数由Cakephp自定传递，使用方法和find方法是一样的，参数通过在Controller中定义paginate变量传递，例如：
1. $limit = 5;
2. $this ->paginate = array (
3. 'SavedNote' => compact( 'limit' )
4. );
注意：
如果重载paginate()的函数使用参数，请自己格式化参数为自己定义的格式，Cakephp不会自动格式化参数。
2. paginateCount()
提供分页的数据数量统计支持，原型如下：
1. function paginateCount( $conditions = null, $recursive = 0);
带来的问题
通过上述的方法确实能够给我们的自定义查询分页，但是问题是，这样做之后我们无法再使用原有的模型的分页。
解决方法：
使用额外的模型完成我们的自定义查询任务
我们新建一个模型，/models/saved_note.php ，代码如下：
view source
< id="highlighter_464972_clipboard" title="copy to clipboard" type="application/x-shockwave-flash" width="16" height="16" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" id="highlighter_464972_clipboard" type="application/x-shockwave-flash" title="copy to clipboard" allowscriptaccess="always" wmode="transparent" flashvars="highlighterId=highlighter_464972" menu="false" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" width="16" height="16">
print ?
01. <?php
02. class SavedNote extends AppModel {
03. var $name = 'SavedNote' ;
04.
05. //这个模型不使用任何表，它仅仅为我们提供自定义分页支持
06. var $useTable = false;
07. var $user_id = 0;
08.
09. /*
10. * Paginate Function
11. */
12. function paginate( $conditions , $fields , $order , $limit , $page = 1, $recursive = null) {
13. $offset = ((int) $page - 1) * (int) $limit ;
14. $conditions = "
15. //这里省略的SQL会在附录中列出
16. ";
17. return $this ->query( $conditions );
18. }
19.
20. /*
21. * Paginate Count Function
22. */
23. function paginateCount( $conditions = null, $recursive = 0) {
24. $conditions = "
25. //这里省略的SQL会在附录中列出
26. ";
27. $result = $this ->query( $conditions );
28. return (int) $result [0][0][ 'NUMS' ];
29. }
30. }
31. ?>
我们可以在控制器中使用这个自定义查询了：
view source
< id="highlighter_859578_clipboard" title="copy to clipboard" type="application/x-shockwave-flash" width="16" height="16" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" id="highlighter_859578_clipboard" type="application/x-shockwave-flash" title="copy to clipboard" allowscriptaccess="always" wmode="transparent" flashvars="highlighterId=highlighter_859578" menu="false" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" width="16" height="16">
print ?
01. class NotesController extends AppController{
02. var $name = 'Notes' ;
03. var $layout = 'notes' ;
04.
05. //这里引入我们需要的模型
06. var $uses = array ( 'Note' , 'SavedNote' , 'TrashNote' );
07.
08. function saved(){
09. $user_id = $this ->Auth->User( 'id' );
10. $limit = 5;
11.
12. //这里为我们的自定义查询，传递一些参数
13. $this ->paginate = array (
14. 'SavedNote' => compact( 'limit' )
15. );
16. $this ->SavedNote->user_id = $user_id ;
17.
18. //控制器的paginate方法，会自定寻找模型的相应方法，并调用
19. $notes = $this ->paginate( 'SavedNote' );
20. $this ->set(compact( 'notes' ));
21. }
22.
23. }
最后，希望大家看到的是，自定义模型中的paginate参数的使用是需要自己手动解析和分配的，如果要使用Cakephp的默认查询样式，还需要自己解析一下。
附录：
1. 模型中paginate方法用到的SQL
view source
< id="highlighter_998076_clipboard" title="copy to clipboard" type="application/x-shockwave-flash" width="16" height="16" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" id="highlighter_998076_clipboard" type="application/x-shockwave-flash" title="copy to clipboard" allowscriptaccess="always" wmode="transparent" flashvars="highlighterId=highlighter_998076" menu="false" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" width="16" height="16">
print ?
01. SELECT
02. DISTINCT (SavedNote.note_id),
03. SavedNote.*,
04. Note.subject,
05. Note.body,
06. Note.created,
07. SentUser. name ,
08. ReceivedUser. name
09. FROM (
10. SELECT
11. ReceivedNote.note_id AS note_id,
12. ReceivedNote.receive_user_id AS receive_user_id,
13. ReceivedNote.send_user_id AS send_user_id,
14. ReceivedNote.is_deleted AS is_deleted,
15. ReceivedNote.is_saved AS is_saved,
16. ReceivedNote.read_date AS read_date,
17. 'received' as src
18. FROM
19. received_notes AS ReceivedNote
20. WHERE
21. receive_user_id = {$this->user_id}
22. UNION ALL
23. SELECT
24. SentNote.note_id AS note_id,
25. SentNote.receive_user_id AS receive_user_id,
26. SentNote.send_user_id AS send_user_id,
27. SentNote.is_deleted AS is_deleted,
28. SentNote.is_saved AS is_deleted,
29. SentNote.read_date AS read_date,
30. 'sent' as src
31. FROM
32. sent_notes AS SentNote
33. WHERE
34. send_user_id = {$this->user_id}
35. ) as SavedNote
36. LEFT JOIN notes AS Note ON Note.id = SavedNote.note_id
37. LEFT JOIN users AS SentUser ON SentUser.id = SavedNote.send_user_id
38. LEFT JOIN users AS ReceivedUser ON ReceivedUser.id = SavedNote.receive_user_id
39. WHERE
40. SavedNote.is_saved = 1
41. ORDER BY
42. Note.created DESC
43. LIMIT {$offset},{$limit}
2. 模型中paginateCount方法用到的SQL
view source
< id="highlighter_96965_clipboard" title="copy to clipboard" type="application/x-shockwave-flash" width="16" height="16" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" id="highlighter_96965_clipboard" type="application/x-shockwave-flash" title="copy to clipboard" allowscriptaccess="always" wmode="transparent" flashvars="highlighterId=highlighter_96965" menu="false" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" width="16" height="16">
print ?
01. SELECT
02. COUNT ( DISTINCT (SavedNote.note_id)) AS NUMS
03. FROM (
04. SELECT
05. ReceivedNote.note_id AS note_id,
06. ReceivedNote.receive_user_id AS receive_user_id,
07. ReceivedNote.send_user_id AS send_user_id,
08. ReceivedNote.is_saved AS is_saved,
09. 'received' as src
10. FROM
11. received_notes AS ReceivedNote
12. WHERE
13. receive_user_id = {$this->user_id}
14. UNION ALL
15. SELECT
16. SentNote.note_id AS note_id,
17. SentNote.receive_user_id AS receive_user_id,
18. SentNote.send_user_id AS send_user_id,
19. SentNote.is_saved AS is_saved,
20. 'received' as src
21. FROM
22. sent_notes AS SentNote
23. WHERE
24. send_user_id = {$this->user_id}
25. ) as SavedNote
26. WHERE
27. SavedNote.is_saved = 1
Cakephp的默认分页功能是基于内置的表关联模型的，所以，如果我们有更多复杂的要求，比如：
联合两张表的查询分页
这样Cakephp的默认分页就达不到我们的要求，解决的方法是：
使用自定义的查询分页
使用自定义查询分页需要在模型中重载两个方法，paginate()和paginateCount()
1. paginate()
提供分页的数据支持，原型如下：
1. function paginate( $conditions , $fields , $order , $limit , $page = 1, $recursive = null);
参数由Cakephp自定传递，使用方法和find方法是一样的，参数通过在Controller中定义paginate变量传递，例如：
1. $limit = 5;
2. $this ->paginate = array (
3. 'SavedNote' => compact( 'limit' )
4. );
注意：
如果重载paginate()的函数使用参数，请自己格式化参数为自己定义的格式，Cakephp不会自动格式化参数。
2. paginateCount()
提供分页的数据数量统计支持，原型如下：
1. function paginateCount( $conditions = null, $recursive = 0);
带来的问题
通过上述的方法确实能够给我们的自定义查询分页，但是问题是，这样做之后我们无法再使用原有的模型的分页。
解决方法：
使用额外的模型完成我们的自定义查询任务
我们新建一个模型，/models/saved_note.php ，代码如下：
view source
< id="highlighter_464972_clipboard" title="copy to clipboard" type="application/x-shockwave-flash" width="16" height="16" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" id="highlighter_464972_clipboard" type="application/x-shockwave-flash" title="copy to clipboard" allowscriptaccess="always" wmode="transparent" flashvars="highlighterId=highlighter_464972" menu="false" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" width="16" height="16">
print ?
01. <?php
02. class SavedNote extends AppModel {
03. var $name = 'SavedNote' ;
04.
05. //这个模型不使用任何表，它仅仅为我们提供自定义分页支持
06. var $useTable = false;
07. var $user_id = 0;
08.
09. /*
10. * Paginate Function
11. */
12. function paginate( $conditions , $fields , $order , $limit , $page = 1, $recursive = null) {
13. $offset = ((int) $page - 1) * (int) $limit ;
14. $conditions = "
15. //这里省略的SQL会在附录中列出
16. ";
17. return $this ->query( $conditions );
18. }
19.
20. /*
21. * Paginate Count Function
22. */
23. function paginateCount( $conditions = null, $recursive = 0) {
24. $conditions = "
25. //这里省略的SQL会在附录中列出
26. ";
27. $result = $this ->query( $conditions );
28. return (int) $result [0][0][ 'NUMS' ];
29. }
30. }
31. ?>
我们可以在控制器中使用这个自定义查询了：
view source
< id="highlighter_859578_clipboard" title="copy to clipboard" type="application/x-shockwave-flash" width="16" height="16" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" id="highlighter_859578_clipboard" type="application/x-shockwave-flash" title="copy to clipboard" allowscriptaccess="always" wmode="transparent" flashvars="highlighterId=highlighter_859578" menu="false" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" width="16" height="16">
print ?
01. class NotesController extends AppController{
02. var $name = 'Notes' ;
03. var $layout = 'notes' ;
04.
05. //这里引入我们需要的模型
06. var $uses = array ( 'Note' , 'SavedNote' , 'TrashNote' );
07.
08. function saved(){
09. $user_id = $this ->Auth->User( 'id' );
10. $limit = 5;
11.
12. //这里为我们的自定义查询，传递一些参数
13. $this ->paginate = array (
14. 'SavedNote' => compact( 'limit' )
15. );
16. $this ->SavedNote->user_id = $user_id ;
17.
18. //控制器的paginate方法，会自定寻找模型的相应方法，并调用
19. $notes = $this ->paginate( 'SavedNote' );
20. $this ->set(compact( 'notes' ));
21. }
22.
23. }
最后，希望大家看到的是，自定义模型中的paginate参数的使用是需要自己手动解析和分配的，如果要使用Cakephp的默认查询样式，还需要自己解析一下。
附录：
1. 模型中paginate方法用到的SQL
view source
< id="highlighter_998076_clipboard" title="copy to clipboard" type="application/x-shockwave-flash" width="16" height="16" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" id="highlighter_998076_clipboard" type="application/x-shockwave-flash" title="copy to clipboard" allowscriptaccess="always" wmode="transparent" flashvars="highlighterId=highlighter_998076" menu="false" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" width="16" height="16">
print ?
01. SELECT
02. DISTINCT (SavedNote.note_id),
03. SavedNote.*,
04. Note.subject,
05. Note.body,
06. Note.created,
07. SentUser. name ,
08. ReceivedUser. name
09. FROM (
10. SELECT
11. ReceivedNote.note_id AS note_id,
12. ReceivedNote.receive_user_id AS receive_user_id,
13. ReceivedNote.send_user_id AS send_user_id,
14. ReceivedNote.is_deleted AS is_deleted,
15. ReceivedNote.is_saved AS is_saved,
16. ReceivedNote.read_date AS read_date,
17. 'received' as src
18. FROM
19. received_notes AS ReceivedNote
20. WHERE
21. receive_user_id = {$this->user_id}
22. UNION ALL
23. SELECT
24. SentNote.note_id AS note_id,
25. SentNote.receive_user_id AS receive_user_id,
26. SentNote.send_user_id AS send_user_id,
27. SentNote.is_deleted AS is_deleted,
28. SentNote.is_saved AS is_deleted,
29. SentNote.read_date AS read_date,
30. 'sent' as src
31. FROM
32. sent_notes AS SentNote
33. WHERE
34. send_user_id = {$this->user_id}
35. ) as SavedNote
36. LEFT JOIN notes AS Note ON Note.id = SavedNote.note_id
37. LEFT JOIN users AS SentUser ON SentUser.id = SavedNote.send_user_id
38. LEFT JOIN users AS ReceivedUser ON ReceivedUser.id = SavedNote.receive_user_id
39. WHERE
40. SavedNote.is_saved = 1
41. ORDER BY
42. Note.created DESC
43. LIMIT {$offset},{$limit}
2. 模型中paginateCount方法用到的SQL
view source
< id="highlighter_96965_clipboard" title="copy to clipboard" type="application/x-shockwave-flash" width="16" height="16" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" id="highlighter_96965_clipboard" type="application/x-shockwave-flash" title="copy to clipboard" allowscriptaccess="always" wmode="transparent" flashvars="highlighterId=highlighter_96965" menu="false" src="http://www.thinkly.cn/wp-content/plugins/syntaxhighlighter/syntaxhighlighter/scripts/clipboard.swf" width="16" height="16">
print ?
01. SELECT
02. COUNT ( DISTINCT (SavedNote.note_id)) AS NUMS
03. FROM (
04. SELECT
05. ReceivedNote.note_id AS note_id,
06. ReceivedNote.receive_user_id AS receive_user_id,
07. ReceivedNote.send_user_id AS send_user_id,
08. ReceivedNote.is_saved AS is_saved,
09. 'received' as src
10. FROM
11. received_notes AS ReceivedNote
12. WHERE
13. receive_user_id = {$this->user_id}
14. UNION ALL
15. SELECT
16. SentNote.note_id AS note_id,
17. SentNote.receive_user_id AS receive_user_id,
18. SentNote.send_user_id AS send_user_id,
19. SentNote.is_saved AS is_saved,
20. 'received' as src
21. FROM
22. sent_notes AS SentNote
23. WHERE
24. send_user_id = {$this->user_id}
25. ) as SavedNote
26. WHERE
27. SavedNote.is_saved = 1
相关阅读 更多 +
