Search code examples
mysqlcakephpjoincakephp-2.8

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.


Solution

  • 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.