Cakephp 3：contains（）函数结果嵌套数据

Question

Problem:

I want to run this query in cakephp 3

SELECT 
    medicine_records.*, 
    medicines.name AS medicine_name, 
    medicines.subname AS `medicine_subname` 
FROM medicine_records 
INNER JOIN medicines ON medicines.id = medicine_records.medicine_id

Controller:

$medicines = $this->MedicineRecords->find()->contain(['Medicines'])->all();

Cakephp Queries log:

2017-02-14 09:58:47 Debug: duration=1 rows=1 SELECT MedicineRecords.id AS `MedicineRecords__id`, MedicineRecords.user_id AS `MedicineRecords__user_id`, MedicineRecords.medicine_id AS `MedicineRecords__medicine_id`, MedicineRecords.times_pre_day AS `MedicineRecords__times_pre_day`, MedicineRecords.created AS `MedicineRecords__created`, MedicineRecords.modified AS `MedicineRecords__modified`, Medicines.id AS `Medicines__id`, Medicines.name AS `Medicines__name`, Medicines.subname AS `Medicines__subname`, Medicines.type AS `Medicines__type`, Medicines.created AS `Medicines__created`, Medicines.modified AS `Medicines__modified` FROM medicine_records MedicineRecords INNER JOIN medicines Medicines ON Medicines.id = (MedicineRecords.medicine_id)

Result data:

"data": [
    {
        "id": 1,
        "user_id": 1,
        "medicine_id": 3,
        "times_pre_day": 2,
        "created": "2017-02-14T04:55:48+00:00",
        "modified": "2017-02-14T04:55:48+00:00",
        "medicine": {
            "id": 3,
            "name": "Name",
            "subname": "Subname",
            "type": 1,
            "created": "2017-02-13T09:38:48+00:00",
            "modified": "2017-02-13T09:38:48+00:00"
        }
    }
]

This result has medicine in child level. I want all data in top level like this:

"data": [
  {
    "id": 1,
    "user_id": 1,
    "medicine_id": 3,
    "times_pre_day": 2,

    "medicine_name": "Name",
    "medicine_subname": "Subname",

    "created": "2017-02-14T04:55:48+00:00",
    "modified": "2017-02-14T04:55:48+00:00",
  }
]

I already try to use public $recursive = -1; but I think it not working on Cakephp 3.

Update

Remove WHERE medicine_records.id = 1

Answer 1

Obviously that is the expected result,contain come in nested form and it should come this way.

No worries you can select fields within contain as you need:

$medicines = $this->MedicineRecords->find()
        ->contain(['Medicines' => function ($q) {
           return $q
                ->select(['medicine_name'=>'name','medicine_subname'=>'subname']);
        }])->all();

See Here (select within contain CakePHP 3).