多表join和with组合使用生成sql错误

[Marber-seven]

easyswoole框架版本号、orm组件版本号 [Version]

easyswoole版本：3.4.1 "easyswoole/orm": "^1.4"

问题描述和截图 [Question]

使用多个join和with的情况，生成的sql错误

SELECT SQL_CALC_FOUND_ROWS *,

FROM fa_test_order AS orders JOIN fa_test_order_goods AS order_goods ON order_goods.order_id = orders.id JOIN fa_test_order_group AS order_group ON order_group.order_id = orders.id JOIN fa_test_order_log AS order_log ON order_log.order_id = orders.id LIMIT 0,20

多生成了一个逗号

排查情况和最小复现脚本 [Tests and Recurrence]

代码： $orderModel = new TestOrder(); $orderModel->alias("orders"); $with[] = ["orderLog", "orderGoods"]; $orderModel->with($with); $orderModel->join("fa_test_order_goods as order_goods", "order_goods.order_id = orders.id"); $orderModel->join("fa_test_order_group as order_group", "order_group.order_id = orders.id"); $orderModel->join("fa_test_order_log as order_log", "order_log.order_id = orders.id");

    $pagenaition = [1, 20];
    $field = "*";
    $order = null;
    $where = [];
    $data = $orderModel->field($field)
        ->getOrderBy($order)
        ->withTotalCount()
        ->page(intval($pagenaition[0]), $pagenaition[1])
        ->all($where);
    return $this->send(1, "获取成功", $data);

模型代码：

namespace App\Model; class TestOrder extends AbstractModel { protected $tableName = "fa_test_order";

public function orderLog()
{
    return $this->hasOne(TestOrderLog::class, function (QueryBuilder $query) {
    }, "order_id", "id");
}

public function orderGoods()
{
    return $this->hasOne(TestOrderGoods::class, function (QueryBuilder $query) {
    }, "order_id", "id");
}

}

数据库：

1、数据库1 CREATE TABLE fa_test_order_goods ( order_id int(11) NOT NULL COMMENT '订单id', goods_id int(11) NOT NULL COMMENT '商品id', name varchar(255) NOT NULL DEFAULT '' COMMENT '商品名称', num int(11) DEFAULT NULL COMMENT '数量' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO fa_test_order_goods VALUES (1, 1, '测试11', 1); INSERT INTO fa_test_order_goods VALUES (2, 1, '测试11', 1); INSERT INTO fa_test_order_goods VALUES (3, 1, '测试11', 1); INSERT INTO fa_test_order_goods VALUES (4, 1, '测试11', 1);

2、数据库2 CREATE TABLE fa_test_order_group ( order_id int(11) NOT NULL COMMENT '订单id', is_join tinyint(1) NOT NULL COMMENT '是否加入？1:是，0:否', join_time int(11) NOT NULL COMMENT '加入时间' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO fa_test_order_group VALUES (1, 1, 1610434018); INSERT INTO fa_test_order_group VALUES (2, 1, 1610434062); INSERT INTO fa_test_order_group VALUES (3, 1, 1610434073); INSERT INTO fa_test_order_group VALUES (4, 1, 1610434684);

3、数据库3 CREATE TABLE fa_test_order_log ( order_id int(11) NOT NULL COMMENT '订单id', title varchar(255) NOT NULL COMMENT '名称', content varchar(255) NOT NULL COMMENT '内容' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO fa_test_order_log VALUES (4, '测试标题1', '测试内容11');

4、数据库4 CREATE TABLE fa_test_order ( id int(11) NOT NULL AUTO_INCREMENT COMMENT 'id', order_no varchar(100) NOT NULL COMMENT '订单号', create_time int(11) NOT NULL, update_time int(11) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

INSERT INTO fa_test_order VALUES (1, '111111', 1610434018, 1610434018); INSERT INTO fa_test_order VALUES (2, '111111', 1610434062, 1610434062); INSERT INTO fa_test_order VALUES (3, '111111', 1610434073, 1610434073); INSERT INTO fa_test_order VALUES (4, '111111', 1610434684, 1610434684);

[Player626]

因为你的代码编写不正确,造成一系列问题,按以下去编写:

    class TestOrderLog extends \EasySwoole\ORM\AbstractModel{
        protected $tableName = 'fa_test_order_log';
    }


    class TestOrderGoods extends \EasySwoole\ORM\AbstractModel{
        protected $tableName = 'fa_test_order_goods';
    }

    class TestOrder extends \EasySwoole\ORM\AbstractModel
    {
        protected $tableName = "fa_test_order";

        public function orderLog()
        {
            return $this->hasOne(TestOrderLog::class, function (QueryBuilder $query) {
            },  "id","order_id");
        }

        public function orderGoods()
        {
            return $this->hasOne(TestOrderGoods::class, function (QueryBuilder $query) {
            },  "id","order_id");
        }
    }

    $orderModel = new TestOrder();
    $orderModel->alias("orders");
    $orderModel->with(["orderLog", "orderGoods"]);
    $orderModel->join("fa_test_order_goods as order_goods", "order_goods.order_id = orders.id");
    $orderModel->join("fa_test_order_group as order_group", "order_group.order_id = orders.id");
    $orderModel->join("fa_test_order_log as order_log", "order_log.order_id = orders.id");

    $pagenaition = [1, 20];
    $field = "*";
    $order = null;
    $where = [];
    $data = $orderModel->field($field)
        ->withTotalCount()
        ->page(intval($pagenaition[0]), $pagenaition[1])
        ->all($where);

    var_dump($orderModel->lastQuery()->getLastQuery());