CODEWITHSUNDEEP
paginationyiirelation
martskins
martskins

Reputation: 2940

Using with and together in Yii

I have a grid with paging which displays client data. Let's say I have a table Client with name, lastName and address in it, a table Phone_Number which has phone numbers for each of the rows in Client and a table Adress which has adresses for each client. So each Client HAS MANY Phone_Numbers and HAS MANY Adresses.

The point is I'm trying to set a limit to the grid's store read. So let's say I set limit = 2. The grid should display only 2 rows per page (2 clients). The problem is that if, for example, client1 has two phone numbers, the query will bring two rows, making the grid display only one client. I am aware that setting together=>false in the query will solve this. But I'm getting an unknown column error whenever I set together=>false.

Here's the code I'm using....

Client::model()->with(
      'clientPhoneNumbers',
      'clientPhoneNumbers'.'PhoneNumber',
      'clientAddresses',
      'clientAddresses'.'Address'
)->findAll(array(condition=>(('Address.s_street_name LIKE :queryString') OR ('PhoneNumber.n_number LIKE :queryString')), params=>$params, order=>$order, limit=>$limit,together=>false));

If I do this, I get an error like: Column not found: Address.s_street_name . However, if I set together=>true, it works just "fine".

I found a solution to this problem by doing the query like this....

$with = array(
      'clientPhoneNumbers',
      'clientPhoneNumbers'.'PhoneNumber'=>array(condition=>('PhoneNumber.n_number LIKE :queryString'), params=>array(':queryString'=>$queryString)),
      'clientAddresses',
      'clientAddresses'.'Address'=>array(condition=>('Address.s_street_name LIKE :queryString'), params=>array(':queryString'=>$queryString))
);

Client::model()->findAll(array(with=>$with, order=>$order, limit=>$limit,together=>false));

The problem is that if I do it like this, the condition is something like this

(('Address.s_street_name LIKE :queryString') AND ('PhoneNumber.n_number LIKE :queryString'))

and I need it to be like this

(('Address.s_street_name LIKE :queryString') OR ('PhoneNumber.n_number LIKE :queryString')).

Any ideas ?

Keep in mind that the names of the relations and tables are not the actual names. The models and relations where created using Gii

Upvotes: 0

Views: 2436

Answers (2)

Orlymee
Orlymee

Reputation: 2357

use criteria with join statement. 

public function test()
{
    $criteria=new CDbCriteria;
    $criteria->alias = 'i';
    $criteria->compare('id',$this->id);     
    .........
            .........

    $criteria->join= 'JOIN phoneNUmbers d ON (i.id=d.id)';

    return new CActiveDataProvider($this, array(
        'criteria'=>$criteria,
        'sort'=>array(
            'defaultOrder'=>'clients ASC',
        ),
    ));
}

Alternately use DAO to prepare and execute your own query and return data in the format you wish.

Or you can use CSQLDataProvier. the last 2 options give you more control over the SQL statement

Upvotes: 0

adamors
adamors

Reputation: 2656

Hmm.. this is how I would try it.

<?php
$criteria=new CDbCriteria;
$criteria->with = array('clientPhoneNumbers', 'clientAddresses');
$criteria->together = true;
$criteria->addCondition(array('Address.s_street_name LIKE :queryString', 'PhoneNumber.n_number LIKE :queryString'), 'OR');    
$criteria->params = array(':queryString' => $queryString);
$criteria->limit = $limit;
$criteria->order = $order;

$result = Client::model()->findAll($criteria);
?>

Also, I think this describes a case similar to yours. Note that he/she is updating the condition parameter explicitly, but IMO doing it with the addCondition method is more readable.

Upvotes: 0

Related Questions