Contain or join?

Question

Contain or join?

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I have the following code:

    $contacts = $this->Contact->find('all', array(
        'limit' => 20,
        'contain' => array('Address', 'Phone' => array('fields' => 'Phone.phone' ), 'Campaign'),
        'conditions' => array('Contact.campaign_id is not null', $conditions_leads),
        'order' => array('Contact.created' => 'DESC')
        ));

It brings me the following array:

    array(
(int) 0 => array(
    'Contact' => array(
        'id' => '24136',
        'source_contacts_id' => null,
        'user_id' => '1',
        'campaign_id' => '62',
        'name' => 'teste PECCIN',
        'document' => null,
        'email' => '[email protected]',
        'gender' => null,
        'birthday_date' => null,
        'observation' => null,
        'contacted' => '0',
        'scheduled' => '0',
        'invalid_reg' => '0',
        'is_active' => '1',
        'import_id' => '19',
        'dropout_date' => null,
        'interested_course' => 'DIREITO',
        'parents' => null,
        'lead_contacted_date' => null,
        'created' => '2014-12-04 16:52:51',
        'modified' => '2014-12-04 16:52:51'
    ),
    'Campaign' => array(
        'id' => '62',
        'name' => 'LEADS - Colégio Estadual Julio De Castilhos',
        'enrollment_type' => '1',
        'campaign_type' => '1',
        'campaign_degree' => '1',
        'close_campaign' => '0',
        'campaign_entity' => '2',
        'import_key' => null,
        'created' => '2014-12-04 16:16:33',
        'modified' => '2014-12-04 16:16:33'
    ),
    'Address' => array(),
    'Phone' => array(
        (int) 0 => array(
            'phone' => '51111111',
            'contact_id' => '24136'
        )
    )
)

)

I have a condition that verifies that the email or phones are already registered in the system:

    $conditions_leads['OR'] = array(
        'Contact.email =' =>  $searchEmail, 
        'Phone.phone =' => $searchTelefone1,
        'Phone.phone =' => $searchTelefone2,
        );

With the email works, more when I put the Phone.phone of the following error:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Phone.phone' in 'where clause'

SQL Query:

SELECT

    'Contact'.'id', 'Contact'.'source_contacts_id', 'Contact'.'user_id',
    'Contact'.'campaign_id', 'Contact'.'name', 'Contact'.'document',
    'Contact'.'email', 'Contact'.'gender', 'Contact'.'birthday_date',
    'Contact'.'observation', 'Contact'.'contacted', 'Contact'.'scheduled',
    'Contact'.'invalid_reg', 'Contact'.'is_active', 'Contact'.'import_id',
    'Contact'.'dropout_date', 'Contact'.'interested_course',
    'Contact'.'parents', 'Contact'.'lead_contacted_date', 'Contact'.'created',
    'Contact'.'modified', 'Campaign'.'id', 'Campaign'.'name', 'Campaign'.'enrollment_type',
    'Campaign'.'campaign_type', 'Campaign'.'campaign_degree', 'Campaign'.'close_campaign',
    'Campaign'.'campaign_entity', 'Campaign'.'import_key', 'Campaign'.'created',
    'Campaign'.'modified'

FROM

     'ipa'.'contacts' AS 'Contact'

LEFT JOIN

    'ipa'.'campaigns' AS 'Campaign' ON ('Contact'.'campaign_id' = 'Campaign'.'id')

WHERE

    'Contact'.'campaign_id' is not null AND (
        ('Contact'.'email' = '[email protected]') OR ('Phone'.'phone' IS NULL)
    )

ORDER BY 'Contact'.'created' DESC LIMIT 20

Does anyone here know how to tell me why I made this mistake or should I do it differently?

cakephp cakephp-2

asked by anonymous 14.01.2015 / 16:47

2 answers

Page Calendar By Week Reference to styles and script's in Master.Page using ResolveUrl

score 1 · Answer 1

1

The error message says that the Phone.phone column is unknown.

At line FROM , this table is not quoted.

14.01.2015 / 17:06

score 1 · Answer 2

I was able to do this using join:

    $contacts = $this->Contact->find('all', array(
        // 'fields' => array('campaign_id'),
        'conditions' => array(
            'Contact.campaign_id is not NULL',
            $conditions_search
        ),
        'contain' => array('Campaign'),
        'joins' => array(
            array(
                'table' => 'phones',
                'alias' => 'Phone',
                'type' => 'LEFT',
                'conditions' => array('Contact.id = Phone.contact_id'),                         
            )      
        )
    ));