عثمان غني عثمان غني - 5 months ago 16
SQL Question

Joining tables CakePHP

I have a table

batch_detail_process
has foreign key
batch_detail_id
and it is joined with belongsTo in model. Now I want to get the ArticleRatio on the basis of article_id & item_id.

My tables foreign keys are as follows:

batch_details_processes = id, batch_detail_id

batch_details = id, batch_id, item_id

batch = id, article_id,

article_ratios = id, article_id, item_id,ratio

$conditions = array();

$conditions['joins'] = array(
array(
'table' => 'batches',
'alias' => 'Batch',
'type' => 'LEFT',
'conditions' => array(
'Batch.id = BatchDetail.batch_id'
)
),
array(
'table' => 'article_ratios',
'alias' => 'ArticleRatio',
'type' => 'LEFT',
'conditions' => array(
'ArticleRatio.article_id = Batch.article_id',
'ArticleRatio.item_id = BatchDetail.item_id'
)
)
);

$bdp = $this->BatchDetailsProcess->find('all',$conditions);

pr($bdp);


returns following:

Array
(
[0] => Array
(
[BatchDetailsProcess] => Array
(
[id] => 1
[batch_detail_id] => 1
[qty_in] => 40
[qty_out] =>
[process_step_id] => 1
[qc_test] =>
[qc_note] =>
[qc_accept] =>
[qc_reject] =>
[moved_further] =>
[assigned_to] => 3
[receive_confirmation] => 0
[created_by] => 2
[updated_by] =>
[created] => 2016-06-18 17:39:21
[updated] => 0000-00-00 00:00:00
)

[BatchDetail] => Array
(
[id] => 1
[batch_id] => 1
[po_transaction_id] => 1
[current_position] => 1
[out_date] => 2016-06-21
[item_id] => 36
)

[ProcessStep] => Array
(
[id] =>
[article_id] =>
[production_step_id] =>
[sequence] =>
[rate_per] =>
[amount_per] =>
)

)

[1] => Array
(
[BatchDetailsProcess] => Array
(
[id] => 4
[batch_detail_id] => 4
[qty_in] => 1
[qty_out] =>
[process_step_id] => 4
[qc_test] =>
[qc_note] =>
[qc_accept] =>
[qc_reject] =>
[moved_further] =>
[assigned_to] => 3
[receive_confirmation] => 0
[created_by] => 1
[updated_by] =>
[created] => 2016-06-22 00:24:25
[updated] => 0000-00-00 00:00:00
)

[BatchDetail] => Array
(
[id] => 4
[batch_id] => 3
[po_transaction_id] => 2
[current_position] => 4
[out_date] => 2016-06-30
[item_id] => 36
)

[ProcessStep] => Array
(
[id] =>
[article_id] =>
[production_step_id] =>
[sequence] =>
[rate_per] =>
[amount_per] =>
)

)

[2] => Array
(
[BatchDetailsProcess] => Array
(
[id] => 2
[batch_detail_id] => 2
[qty_in] => 50
[qty_out] =>
[process_step_id] => 1
[qc_test] =>
[qc_note] =>
[qc_accept] =>
[qc_reject] =>
[moved_further] =>
[assigned_to] => 3
[receive_confirmation] => 0
[created_by] => 2
[updated_by] =>
[created] => 2016-06-18 17:39:21
[updated] => 0000-00-00 00:00:00
)

[BatchDetail] => Array
(
[id] => 2
[batch_id] => 1
[po_transaction_id] => 3
[current_position] => 1
[out_date] => 2016-06-30
[item_id] => 37
)

[ProcessStep] => Array
(
[id] =>
[article_id] =>
[production_step_id] =>
[sequence] =>
[rate_per] =>
[amount_per] =>
)

)

[3] => Array
(
[BatchDetailsProcess] => Array
(
[id] => 3
[batch_detail_id] => 3
[qty_in] => 11
[qty_out] =>
[process_step_id] => 1
[qc_test] =>
[qc_note] =>
[qc_accept] =>
[qc_reject] =>
[moved_further] =>
[assigned_to] => 3
[receive_confirmation] => 0
[created_by] => 2
[updated_by] =>
[created] => 2016-06-18 17:44:42
[updated] => 0000-00-00 00:00:00
)

[BatchDetail] => Array
(
[id] => 3
[batch_id] => 2
[po_transaction_id] => 3
[current_position] => 1
[out_date] => 2016-07-15
[item_id] => 37
)

[ProcessStep] => Array
(
[id] =>
[article_id] =>
[production_step_id] =>
[sequence] =>
[rate_per] =>
[amount_per] =>
)

)

[4] => Array
(
[BatchDetailsProcess] => Array
(
[id] => 5
[batch_detail_id] => 5
[qty_in] => 9
[qty_out] =>
[process_step_id] => 5
[qc_test] =>
[qc_note] =>
[qc_accept] =>
[qc_reject] =>
[moved_further] =>
[assigned_to] => 4
[receive_confirmation] => 0
[created_by] => 2
[updated_by] =>
[created] => 2016-06-22 13:05:56
[updated] => 0000-00-00 00:00:00
)

[BatchDetail] => Array
(
[id] => 5
[batch_id] => 4
[po_transaction_id] => 38
[current_position] => 5
[out_date] => 2016-07-20
[item_id] => 17
)

[ProcessStep] => Array
(
[id] =>
[article_id] =>
[production_step_id] =>
[sequence] =>
[rate_per] =>
[amount_per] =>
)

)

[5] => Array
(
[BatchDetailsProcess] => Array
(
[id] => 6
[batch_detail_id] => 6
[qty_in] => 85
[qty_out] =>
[process_step_id] => 3
[qc_test] =>
[qc_note] =>
[qc_accept] =>
[qc_reject] =>
[moved_further] =>
[assigned_to] => 4
[receive_confirmation] => 0
[created_by] => 1
[updated_by] =>
[created] => 2016-06-22 14:36:42
[updated] => 0000-00-00 00:00:00
)

[BatchDetail] => Array
(
[id] => 6
[batch_id] => 5
[po_transaction_id] => 39
[current_position] => 3
[out_date] => 2016-06-30
[item_id] => 17
)

[ProcessStep] => Array
(
[id] =>
[article_id] =>
[production_step_id] =>
[sequence] =>
[rate_per] =>
[amount_per] =>
)

)

)


SQL Generated by it:

SELECT `BatchDetailsProcess`.`id`, `BatchDetailsProcess`.`batch_detail_id`,
`BatchDetailsProcess`.`qty_in`, `BatchDetailsProcess`.`qty_out`,
`BatchDetailsProcess`.`process_step_id`, `BatchDetailsProcess`.`qc_test`,
`BatchDetailsProcess`.`qc_note`, `BatchDetailsProcess`.`qc_accept`,
`BatchDetailsProcess`.`qc_reject`, `BatchDetailsProcess`.`moved_further`,
`BatchDetailsProcess`.`assigned_to`, `BatchDetailsProcess`.`receive_confirmation`,
`BatchDetailsProcess`.`created_by`, `BatchDetailsProcess`.`updated_by`,
`BatchDetailsProcess`.`created`, `BatchDetailsProcess`.`updated`,
`BatchDetail`.`id`, `BatchDetail`.`batch_id`, `BatchDetail`.`po_transaction_id`,
`BatchDetail`.`current_position`, `BatchDetail`.`out_date`,
`BatchDetail`.`item_id`, `ProcessStep`.`id`, `ProcessStep`.`article_id`,
`ProcessStep`.`production_step_id`, `ProcessStep`.`sequence`,
`ProcessStep`.`rate_per`, `ProcessStep`.`amount_per`
FROM `star_impact`.`batch_details_processes` AS `BatchDetailsProcess`
LEFT JOIN `star_impact`.`batch_details` AS `BatchDetail`
ON (`BatchDetailsProcess`.`batch_detail_id` = `BatchDetail`.`id`)
LEFT JOIN `star_impact`.`process_steps` AS `ProcessStep`
ON (`BatchDetailsProcess`.`process_step_id` = `ProcessStep`.`id`)
LEFT JOIN `star_impact`.`batches` AS `Batch`
ON (`Batch`.`id` = `BatchDetail`.`batch_id`)
LEFT JOIN `star_impact`.`article_ratios` AS `ArticleRatio`
ON (`ArticleRatio`.`article_id` = `Batch`.`article_id`
AND `ArticleRatio`.`item_id` = `BatchDetail`.`item_id`
)
WHERE 1 = 1


I want to select article ratios on basis of matching article_id & item_id. But results doesn't return the ArticleRatio but only BatchDetailProcess, BatchDetail and ProcessStep.

Answer

results doesn't return the ArticleRatio

Joins are not associations.

CakePHP's ORM does not issue SELECT * queries, fields are always specified so from the question:

SELECT 
    ... <- No ArticleRatio here 
FROM 
    `star_impact`.`batch_details_processes` AS `BatchDetailsProcess` 
...
WHERE 
    1 = 1

If there is no mention in select, there will be no fields returned from the joined table, and no data for CakePHP from those tables.

Either:

Specify the fields in the query

Instead of letting CakePHP decide which fields to return, specify them:

$conditions['fields'] = '*';
$bdp = $this->BatchDetailsProcess->find('all',$conditions);

SELECT 
    * <- Lazy, all fields. Or be more specific with 'fields'
FROM 
    `star_impact`.`batch_details_processes` AS `BatchDetailsProcess` 
...
WHERE 
    1 = 1

Note that the second parameter to find is parameters not conditions.

OR:

Use associations.

Instead of joins, use temporary associations:

$this->BatchDetailsProcess->bindModel('hasOne', [
    'Batch' =>  [
        'conditions' => [
            'Batch.id = BatchDetail.batch_id'
        ]
    ],
    'ArticleRatio' => [
        'conditions' => [
            'ArticleRatio.article_id = Batch.article_id',
            'ArticleRatio.item_id = BatchDetail.item_id'
        ]
    ]
]);
$this->BatchDetailsProcess->find();

This will create a left join, and add populate the fields/entities in the response; adapt the parameters passed to the association definitions (and use containable) to suit.