Yii 1.1 relationship search issue

Question

1. Table meeting

Model name CoreMeeting

Fields id, title, start time, created_by

Relationship:

'MeetingParticipants' => array(self::HAS_MANY, 'MeetingParticipants', 'meeting_id'),

2. Table core_meeting_participant

Model name is meeting_participant

Fields are id, meeting_id, participant_id, group_id

Relationship:

 'meeting' => array(self::BELONGS_TO, 'CoreMeeting', 'meeting_id'),
 'group' => array(self::BELONGS_TO, 'MeetingGroup', 'group_id'),

3. Table core_meeting_group

Model name is MeetingGroup

Fields are id, group_name

My search filter in the meeting model is:

public function search()
{
    
    $group=filter_var($_REQUEST['group'], FILTER_SANITIZE_STRING);//contain group name

    $criteria=new CDbCriteria;
    $user_id = Yii::app()->user->id;

    $criteria->compare('id',$this->id);
    $criteria->compare('title',$this->title,true);
    $criteria->with=array('MeetingParticipants'=>array("select"=>"*"),'MeetingParticipants.group'=>array('select'=>'id,group_name'));
    
    if(isset($this->start_time) && !empty($this->start_time))
    $criteria->compare('start_time',date("Y-m-d", strtotime($this->start_time)), true);

    $criteria->compare('created_by',$user_id);

    if(isset($group)&&!empty($group))
    $criteria->compare('group.group_name',$group);
    $criteria->together = true;


    return new CActiveDataProvider($this, array(
        'criteria'=>$criteria,

    ));
}

I have created 4 meetings, each meeting have at least 5 participants each participant belongs to a meeting group. I want to list all meetings with the following filed meeting title, groups and meeting time.

My problem is if I enable $criteria->together = true; if the meeting has more than 10 participants that will show only 1 meeting in grid view. If I disable this that will show all but I can't search with the meeting name. SQL fiddle link is http://sqlfiddle.com/#!9/fdaacf full SQL dump https://pastebin.com/NtpMuCpE

score 0 · Answer 1 · answered Apr 15 '20 at 16:57

0

Documentation for CDbCriteria->together property:

https://www.yiiframework.com/doc/api/1.1/CDbCriteria#together-detail

Whether the foreign tables should be joined with the primary table in a single SQL. This property is only used in relational AR queries for HAS_MANY and MANY_MANY relations.

As I can see your relation in CoreMeeting model:

'group' => array(self::BELONGS_TO, 'MeetingGroup', 'group_id')

Obviously you are trying to fetch one-to-one (BELONGS_TO) relation CoreMeeting -> MeetingGroup eagerly by globally setting to your CDbCriteria->together = true; which is not correct according to the documentation.

What I would suggest you, first of all get rid of this global together setting to CDbCriteria and try changing your with clause like this:

$criteria->with = [
    // this is your has-many relation (one meeting has several participants), thus we set here `together` to true
    'MeetingParticipants' => [
        'together' => true,
        'with' => [
            // this is your one-to-one relation (one participant has one group)
            'group'
        ]
    ]
];

answered Apr 15 '20 at 16:57

G.Spirov

198
9

not working no diffrence check sql fiddle link is http://sqlfiddle.com/#!9/fdaacf full sql dump https://pastebin.com/NtpMuCpE – Reneesh Kurian Apr 16 '20 at 05:28
In fact, I think that the issue is not related to SQL after I saw your database structure. Did you remove the global $criteria->together = true; ? What do you mean by no difference, are the results now aggregated or the search is not working? Please give more specific details what happened after you've changed your code. – G.Spirov Apr 16 '20 at 06:34
hi please visit the link for the out put when made changes https://imagebin.ca/v/5JIYEeb0Um3l – Reneesh Kurian Apr 16 '20 at 07:47
check this for if remove the criteria together this is the required out put but cant do search https://pasteboard.co/J42GnsT.png – Reneesh Kurian Apr 16 '20 at 07:48
$criteria->group = 't.id'; Try adding this line. It will group by CoreMeeting identifier primary key which will prevent aggregation from the HAS_MANY relation and guarantee uniqueness to the primary model. – G.Spirov Apr 16 '20 at 07:55
that show error CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #8 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'portalv2.MeetingParticipants.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. The – Reneesh Kurian Apr 16 '20 at 07:58
Yes, you should explicitly describe the columns in the group clause like you have describe them in the select clause, i have just pointed you that you should group by the primary key of the CoreMeeting model (core_meeting table). Just check how is defined in the select clause. Take a look here: https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql – G.Spirov Apr 16 '20 at 08:03
Hi check the sql t.id already exist there COUNT(*) FROM (SELECT `t`.`id` AS `t0_c0`, `. Im new to yii so i dont know how to explicitly specify id in yii .can you help me? – Reneesh Kurian Apr 16 '20 at 08:52
Behind the scenes, this is the SQL produced from the ActiveDataProvider in order to query the total count of the result set. In most cases in looks similar to the main query from the CDbCriteria wrapped in select count(*) from ( CDbCriteria->rawSql ). That is what i meant, if you group by t.id in your CDbCriteria query it will change the total count result set because group by will break down the aggregation which came from the join with the HAS_MANY relation. – G.Spirov Apr 16 '20 at 11:13
What do you mean by "new issue"? – G.Spirov Apr 16 '20 at 14:17
how to reslove thie error : SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #8 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'portalv2.MeetingParticipants.id? – Reneesh Kurian Apr 17 '20 at 04:53
$criteria->group = 't.id, core_meeting_participant.id'; – G.Spirov Apr 17 '20 at 06:14
not working like expected https://pasteboard.co/J4cMNDo.png checkout this – Reneesh Kurian Apr 17 '20 at 09:30
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/211860/discussion-between-g-spirov-and-reneesh-kurian). – G.Spirov Apr 17 '20 at 10:22

Yii 1.1 relationship search issue

1 Answers1