Zend Db Table Double JOIN

Question

1. plan_payment_type:
   id
   plan_id

2. plan
   id
   payment_period

It is Foreign key between plan_payment_type.plan_id and plan.id. In my code I know a $plan_payment_type_id.

My task is to select payment_period. Steps:

Select plan_id from plan_payment_type
Select payment_period from plan with selected plan_id

How can I do it with one query in Zend Framework? Thank you very much.

Tomalak · Accepted Answer · 2010-10-13 06:55:04Z

2

The following SQL statement and PHP code correspond to each other:

SELECT
  t.id AS plan_payment_type_id,
  p.id AS plan_id,
  p.payment_period
FROM
  plan_payment_type AS t
  INNER JOIN plan AS p ON p.id = t.plan_id
WHERE
  p.id = { SQL query param for $plan_payment_type_id }


$select = $db->select()
          ->from(array('t' => 'plan_payment_type'),
                 array('id' => 'plan_payment_type_id'))
          ->join(array('p' => 'plan'), 'p.id = t.plan_id',
                 array('id' => 'plan_id', 'payment_period'))
          ->where('p.id = ?', $plan_payment_type_id);

edited Oct 13, 2010 at 6:55

answered Oct 13, 2010 at 6:49

Tomalak

340k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Elzo Valugi · Accepted Answer · 2010-10-13 07:11:22Z

1

Before everything I would organize them differently. Is not the plan_types that should have a plan.id. This is not logical. You hava a table with plan_types which has an plan.id and a plan.name and the relation is in the plan table through the plan.type_id.

This dependencies are solved this way:

class Payment_Plan extends Zend_Db_Table_Abstract
{
    protected $_name            = 'plan';
    protected $_referenceMap    = array(
        'Plan' => array(
            'columns'           => 'plan_id',
            'refTableClass'     => 'Plan',
            'refColumns'        => 'id'
        )

}

class Plan_Type extends Zend_Db_Table_Abstract
{
    protected $_name            = 'plan_types';
    protected $_dependentTables = array('Plan_Type');
}

Later you can have in the PlanRow Class a function :

public function type() {
    return $this->findParentRow('Plan_Type');
}

and for getting all the plans of type x in the Plan_type row class

public function entries() {
    return $row->findDependentRowset('Plan');
}

answered Oct 13, 2010 at 7:11

Elzo Valugi

28.1k17 gold badges97 silver badges115 bronze badges

3 Comments

Alex Pliutau Over a year ago

Tomalak answer is better for me. Thanks, Elzo Valugi.

Tomalak Over a year ago

+1 because you are right with regard to DB layout. Having a plan_id on plan_payment_type makes no sense to me either.

b_dubb Over a year ago

i think you are missing a ')' in your $_referenceMap variable

Collectives™ on Stack Overflow

Zend Db Table Double JOIN

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related