0

i recently upgraded my 1.3-app to 2.4 with https://github.com/dereuromark/upgrade
But now some usages of $this->Model->find() doesn't work. Especially the joins to the associated models/tables.
Resulting in "Unknown column 'Bill.customer_id' in where clause".

My setup:

Tables and Associations: https://i.stack.imgur.com/bjOIz.png (Image)

Models:

App::uses('AppModel', 'Model');
class Customer extends AppModel {
public $actsAs = array('Logable', 'Containable');
public $hasMany = array(
    'Bill' => array(
        'className' => 'Bill',
        'foreignKey' => 'customer_id',
        'dependent' => false,
        'conditions' => '',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'exclusive' => '',
        'finderQuery' => '',
        'counterQuery' => ''
    )...
-----------------------------------------------------------------
App::uses('AppModel', 'Model');
class Bill extends AppModel {
public $actsAs = array('Containable', 'Logable', 'Lockable');

public $belongsTo = array(
    'Customer' => array(
        'className' => 'Customer',
        'foreignKey' => 'customer_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    )...

public $hasAndBelongsToMany = array(
    'Stage' => array(
        'className' => 'Stage',
        'joinTable' => 'bills_stages',
        'foreignKey' => 'bill_id',
        'associationForeignKey' => 'stage_id',
        'unique' => true,
        'conditions' => '',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'finderQuery' => '',
        'deleteQuery' => '',
        'insertQuery' => ''
    )...
--------------------------------------------------------------
App::uses('AppModel', 'Model');
class BillsStage extends AppModel {
public $actsAs = array('Containable', 'Logable', 'Lockable');

public $belongsTo = array(
    'Bill' => array(
        'className' => 'Bill',
        'foreignKey' => 'bill_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
    ),
    'Stage' => array(
        'className' => 'Stage',
        'foreignKey' => 'stage_id',
        'conditions' => '',
        'fields' => '',
        'order' => ''
)...
--------------------------------------------------------
App::uses('AppModel', 'Model');
class Stage extends AppModel {
public $displayField = 'name';
public $actsAs = array('Tree', 'Containable', 'Logable');

public $hasMany = array(
    'Bill' => array(
        'className' => 'Bill',
        'foreignKey' => 'stage_id',
        'dependent' => false,
        'conditions' => '',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'exclusive' => '',
        'finderQuery' => '',
        'counterQuery' => ''
    )...

public $hasAndBelongsToMany = array(
    'Bill' => array(
        'className' => 'Bill',
        'joinTable' => 'bills_stages',
        'foreignKey' => 'stage_id',
        'associationForeignKey' => 'bill_id',
        'unique' => true,
        'conditions' => '',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'finderQuery' => '',
        'deleteQuery' => '',
        'insertQuery' => ''
    )...

In 1.3 the joins are working. In 2.4 only 1-dimensional sql query.

Any idea?

--edit--

$billsStages = $this->Customer->Bill->BillsStage->find('all', array(
  'conditions' => array(
      'Bill.customer_id' => $this->Customer->id,
      'Stage.id' => array_keys($vk_stages)
    ),
  'order' => 'BillsStage.created DESC'
));
KommiZZar
  • 33
  • 7
  • How does your `$this->Model->find()` look like? – Carl0s1z Nov 15 '13 at 11:04
  • Sorry, added my find()-call now. Please have a look. @CTravel – KommiZZar Nov 15 '13 at 12:25
  • what's your recursive value for the models? are they containable? – Nunser Nov 15 '13 at 12:25
  • I tried all values for `$this->Customer->Bill->BillsStage->recursive`. Nothing changed. All models actAs containable. Everthing fine in 1.3. @Nunser – KommiZZar Nov 15 '13 at 12:30
  • set debug level to 2 and check the full sql statement which creates the error. Maybe just a typo. – rrd Nov 16 '13 at 16:18
  • At your table and association image there is no created field for BillsStage table. atually that is BillsStages in plural. – rrd Nov 16 '13 at 16:21
  • there is no typo. and i shortened my table image, there is a created field. now i made a hasMany association from `bills` to `bills_stages`. there is no error anymore. but i have to proof the results. – KommiZZar Nov 18 '13 at 07:56

1 Answers1

0

Try a join (example taken out from the docs)

$options['joins'] = array(
    array('table' => 'bills',
        'alias' => 'Bill',
        'type' => 'LEFT',
        'conditions' => array(
            'Bill.customer_id' => $this->Customer->id,
        )
    ),
    array('table' => 'stages',
        'alias' => 'Stage',
        'type' => 'LEFT',
        'conditions' => array(
            'Stage.id' => array_keys($vk_stages)
        )
    )
);

$options['conditions'] = array();

$items= $this->Customer->Bill->BillsStage->find('all', $options);

(adjust to your case).

According to this answer, a contain for conditions on the secondary model is not possible, since contain makes a different query. So use joins if you want to apply conditions on secondary models, or create a simple function in your model that does two separate queries and creates a macro array that returns the mixed results if you want to use contain.

Community
  • 1
  • 1
Nunser
  • 4,512
  • 8
  • 25
  • 37
  • 'conditions' with Stage.id is still a 'unknown column'. if i delete the conditions -> a left join will be done but results 1,6 mio entries. the whole table has only 20k. i'm facing this problem for 2 days know. can't imagine why it worked on 1.3 and not on 2.4. studied all changelogs of cakephp. – KommiZZar Nov 15 '13 at 13:11
  • well... that's why I said you need to adjust to your case. In Stage is another table, then a join is needed and not a condition. I updated. I don't know all the definitions of your table, so I assume you have something like `bills_stages` with a `bill_id` and `stage_id`, so why not try putting `stage_id => array_keys($vk_stages)` in `conditions` and see how it goes... – Nunser Nov 15 '13 at 13:22
  • Sure, i tried. But the left joins are bursting. too much results. my tables you see here: http://i.stack.imgur.com/bjOIz.png My general problem is not only this case. there are more of this kind in my old 1.3 app. i want to understand whats the change. fixing every single find-call will hurt. ;-) thanks. – KommiZZar Nov 15 '13 at 13:32
  • Mmmmmh, maybe i miss a hasMany association from `bills` to `bills_stages`? http://i.stack.imgur.com/bjOIz.png – KommiZZar Nov 15 '13 at 14:10