多对多关联,在belongsToMany()后,接链式操作的bug

Open
#197 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
42/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
php
Domain
database

Research direction

Reproduce the reported many-to-many eager-loading case using the User, Book, and UserBook models, comparing belongsToMany() with and without chained order() and with() closures. Inspect the eager-loading path and generated SQL around these entry points; done means every user retains the correct books while ordering and append-style constraints still work.

Written by the indexing model from the issue text.

Description

user表 ( id, name )
数据
(1, 张三),
(2, 李四),
(3, 王五)

book表[ id, name ]
数据
(1, 《PHP》),
(2, 《Java》),
(3, 《Python》),
(4, 《Go》),

中间表 user_book [ id, user_id, book_id]
数据
(1, 1, 1),
(2, 2, 2),
(3, 2, 3),
(4, 3, 1),
(5, 3, 2),
(6, 3, 4)

                                +--------+
                      +-------> |1  PHP  | <------+
  +--------+          |         +--------+        |
1 |zhangsan| +--------+                           |
  +--------+                    +--------+        |
                      +-------> |2  Java | <---+  |
  +--------+          |         +--------+     |  |
2 |  lisi  | +--------+                        |  |
  +--------+          |         +--------+     |  |
                      +-------> |3 Python|     |  |
  +--------+                    +--------+     |  |
3 | wangwu | +------+                          |  |
  +--+--+--+        |           +--------+     |  |
     |  |           +---------> |4   Go  |     |  |
     |  |                       +--------+     |  |
     |  |                                      |  |
     |  +--------------------------------------+  |
     |                                            |
     +--------------------------------------------+

class User extends Model {
    protected $table = 'user';

    public function books(){
        return $this->belongsToMany(Book::class, UserBook::class)->order(['Book.id'=>'DESC']);
    }
}
class Book extends Model {
    protected $table = 'book';
}
class UserBook extends Pivot{
    protected $table = 'user_book';
}

查询代码

$users = User::where('id', '>', 0)->with(['books'])->select();

在think-orm v2.0.31中的结果【正确】

[
    {
        "id": 1,
        "name": "张三",
        "books": [
            {
                "id": 1,
                "name": "《PHP》"
            }
        ]
    },
    {
        "id": 2,
        "name": "李四",
        "books": [
            {
                "id": 3,
                "name": "《Python》"
            },
            {
                "id": 2,
                "name": "《Java》"
            }
        ]
    },
    {
        "id": 3,
        "name": "王五",
        "books": [
            {
                "id": 4,
                "name": "《Go》"
            },
            {
                "id": 2,
                "name": "《Java》"
            },
            {
                "id": 1,
                "name": "《PHP》"
            }
        ]
    }
]

而在think-orm v2.0.34中的结果【错误】

[
    {
        "id": 1,
        "name": "张三",
        "books": []    // 【丢失了books数据】
    },
    {
        "id": 2,
        "name": "李四",
        "books": []    // 【丢失了books数据】
    },
    {
        "id": 3,
        "name": "王五",
        "books": [     // 【books数据正常】
            {
                "id": 4,
                "name": "《Go》"
            },
            {
                "id": 2,
                "name": "《Java》"
            },
            {
                "id": 1,
                "name": "《PHP》"
            }
        ]
    }
]

对比发现,v2.0.31的结果正常,所有的user的books正确;
而v2.0.34 中的结果里,只有最后一个user中有books,其他user的books都是 [ ]

日志中的SQL对比
v2.0.34

SELECT `book`.*,`pivot`.`id` AS `pivot__id`,`pivot`.`user_id` AS `pivot__user_id`,`pivot`.`book_id` AS `pivot__book_id`,`pivot` FROM `book` INNER JOIN `user_book` `pivot` ON `pivot`.`book_id`=`book`.`id` 
WHERE  `pivot`.`user_id` IN (1,2,3)

v2.0.34

SELECT `book`.*,`pivot`.`id` AS `pivot__id`,`pivot`.`user_id` AS `pivot__user_id`,`pivot`.`book_id` AS `pivot__book_id` FROM `book` INNER JOIN `user_book` `pivot` ON `pivot`.`book_id`=`book`.`id` 
WHERE  `pivot`.`user_id` = 3 ORDER BY `Book`.`id` DESC

区别:前者 user_id IN (1, 2, 3),而后者 user_id = 3

不仅在模型中定义关联时,belongsToMany,后接其他函数有这个问题
return $this->belongsToMany(...)->order();

在查询时,with()中指定闭包也会出现此bug,如:

$users = User::where('id', '>', 0)->with(['books'=>function($query){
    $query->append(['pinyin']);    // append 拼音字段
}])->select();
Dominant language
PHP
Stars
437
Forks
188
PR merge metrics
No merged PRs in 30d

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from top-think/think-orm

All issues in top-think/think-orm

Similar issues

More PHP issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.