lundi 27 juin 2016

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);

above code return nothing.

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 return empty.


Aucun commentaire:

Enregistrer un commentaire