关联查询

简介

Cabal-DB 的关联数据查询主要用了两个方法:->has('table')->belongs('table')
因为其实数据库关联关系只有两种,一种是拥有(has)另一种是属于(belongs),如:

  • 用户拥有更多个文章
  • 文章拥有多个标签
  • 文章属于一个用户
  • 文章标签关联记录属于一个标签和一篇文章

语法

拥有关系查询语法:

$row->has($name, $foreignKeyOrCallback = null, $callback = null, $storeKey = null)

  • name 目标表名
  • foreignKeyOrCallback 外键名称或者回调函数,如果不传或传入的是一个函数则外键默认为:表名+_id user_id
  • callback 回调函数,可以自己追加一些查询条件
  • storeKey 存储键名 默认为表明,同一个关联表多次查询但是条件不同需要自定义存储键名

属于关系查询语法:

$row->belongs($name, $foreignKeyOrCallback = null, $callback = null, $storeKey = null)

  • name 目标表名
  • foreignKeyOrCallback 外键名称或者回调函数,如果不传或传入的是一个函数则外键默认为:表名+_id user_id
  • callback 回调函数,可以自己追加一些查询条件
  • storeKey 存储键名 默认为表明,同一个关联表多次查询但是条件不同需要自定义存储键名

返回值说明:

->has('table') 方法返回的是一个 Cabal\DB\Rows 对象里,也是一个所有元素都是 Cabal\DB\Row 的数组。

->belongs('table') 方法返回的是一个 Cabal\DB\Row 对象。

示例

单个用户的文章查询示例:

  1. $result = $db->table('user')->select(['id', 'username'])->first();
  2. $articleList = [];
  3. foreach ($result->has('article') as $article) {
  4. $tagList = [];
  5. foreach ($article->has('article_tag') as $articleTag) {
  6. $tag = $articleTag->belongs('tag');
  7. $tagList[] = $tag->name;
  8. }
  9. $article->tagList = $tagList;
  10. $articleList[] = $article->jsonSerialize();
  11. }
  12. $result->articleList = $articleList;
  13. return $result;

返回:

  1. {
  2. "id": 1,
  3. "username": "Cabal",
  4. "articleList": [
  5. {
  6. "id": 1,
  7. "user_id": 1,
  8. "status": 1,
  9. "title": "PHP 教程",
  10. "content": "...",
  11. "created_at": "2018-01-02 246:01:0",
  12. "tagList": [
  13. "PHP",
  14. "教程"
  15. ]
  16. },
  17. {
  18. "id": 2,
  19. "user_id": 1,
  20. "status": 1,
  21. "title": "PHP 简介",
  22. "content": "...",
  23. "created_at": "2018-01-03 199:00:0",
  24. "tagList": [
  25. "PHP",
  26. "简介"
  27. ]
  28. },
  29. {
  30. "id": 3,
  31. "user_id": 1,
  32. "status": 1,
  33. "title": "PHP 安装",
  34. "content": "...",
  35. "created_at": "2018-01-04 30:00:00",
  36. "tagList": [
  37. "PHP",
  38. "安装"
  39. ]
  40. },
  41. {
  42. "id": 4,
  43. "user_id": 1,
  44. "status": 0,
  45. "title": "草稿",
  46. "content": "...",
  47. "created_at": "2018-01-01 05:00:00",
  48. "tagList": [
  49. ]
  50. }
  51. ]
  52. }

上面的代码调用了以下的查询语句:

  1. [
  2. {
  3. "sql": "SELECT id,username FROM `user` LIMIT 1",
  4. "params": [
  5. ]
  6. },
  7. {
  8. "sql": "SELECT `article`.* FROM `article` WHERE `user_id` = ?",
  9. "params": [
  10. 1
  11. ]
  12. },
  13. {
  14. "sql": "SELECT `article_tag`.* FROM `article_tag` WHERE article_id IN (?, ?, ?, ?)",
  15. "params": [
  16. 1,
  17. 2,
  18. 3,
  19. 4
  20. ]
  21. },
  22. {
  23. "sql": "SELECT `tag`.* FROM `tag` WHERE id IN (?, ?, ?, ?)",
  24. "params": [
  25. 1,
  26. 4,
  27. 3,
  28. 2
  29. ]
  30. }
  31. ]

多个用户的文章查询示例:

  1. $userList = $db->table('user')->select(['id', 'username'])->rows();
  2. foreach ($userList as $user) {
  3. foreach ($user->has('article') as $article) {
  4. $tagList = [];
  5. foreach ($article->has('article_tag') as $articleTag) {
  6. $tag = $articleTag->belongs('tag');
  7. $tagList[] = $tag->name;
  8. }
  9. $article->tagList = $tagList;
  10. $articleList[] = $article->jsonSerialize();
  11. }
  12. $user->articleList = $articleList;
  13. }
  14. // return $userList->getRows()->getTable()->getConnection()->getQueryLogs();
  15. return $userList;

返回:

  1. [
  2. {
  3. "id": 1,
  4. "username": "Cabal",
  5. "articleList": [
  6. {
  7. "id": 1,
  8. "user_id": 1,
  9. "status": 1,
  10. "title": "PHP 教程",
  11. "content": "...",
  12. "created_at": "2018-01-02 246:01:0",
  13. "tagList": [
  14. "PHP",
  15. "教程"
  16. ]
  17. },
  18. {
  19. "id": 2,
  20. "user_id": 1,
  21. "status": 1,
  22. "title": "PHP 简介",
  23. "content": "...",
  24. "created_at": "2018-01-03 199:00:0",
  25. "tagList": [
  26. "PHP",
  27. "简介"
  28. ]
  29. },
  30. {
  31. "id": 3,
  32. "user_id": 1,
  33. "status": 1,
  34. "title": "PHP 安装",
  35. "content": "...",
  36. "created_at": "2018-01-04 30:00:00",
  37. "tagList": [
  38. "PHP",
  39. "安装"
  40. ]
  41. },
  42. {
  43. "id": 4,
  44. "user_id": 1,
  45. "status": 0,
  46. "title": "草稿",
  47. "content": "...",
  48. "created_at": "2018-01-01 39:00:00",
  49. "tagList": [
  50. ]
  51. }
  52. ]
  53. },
  54. {
  55. "id": 2,
  56. "username": "User2",
  57. "articleList": [
  58. {
  59. "id": 5,
  60. "user_id": 2,
  61. "status": 0,
  62. "title": "User2 的文章",
  63. "content": "...",
  64. "created_at": "2018-01-05 05:00:00",
  65. "tagList": [
  66. ]
  67. }
  68. ]
  69. }
  70. ]

上面的代码调用了以下的查询语句:

  1. [
  2. {
  3. "sql": "SELECT id,username FROM `user`",
  4. "params": [
  5. ],
  6. },
  7. {
  8. "sql": "SELECT `article`.* FROM `article` WHERE user_id IN (?, ?)",
  9. "params": [
  10. 1,
  11. 2
  12. ],
  13. },
  14. {
  15. "sql": "SELECT `article_tag`.* FROM `article_tag` WHERE article_id IN (?, ?, ?, ?, ?)",
  16. "params": [
  17. 1,
  18. 2,
  19. 3,
  20. 4,
  21. 5
  22. ],
  23. },
  24. {
  25. "sql": "SELECT `tag`.* FROM `tag` WHERE id IN (?, ?, ?, ?)",
  26. "params": [
  27. 1,
  28. 4,
  29. 3,
  30. 2
  31. ],
  32. }
  33. ]

总结说明

  1. 没有使用 JOIN 查询,因为 JOIN 查询性能可能没有简单查询效率高,尤其是数据表大了之后,
  2. 合并了查询,查询多篇文章的标签时,自动将多条查询转化成 WHERE IN 查询,减少查询次数,提高查询效率。
  3. 即使多次调用 ->has('table')->belongs('table') 获取关联数据也不会产生多次的数据查询!