CGridview filtering and sorting on HAS_MANY relations

Yii 1.1 General Discussion
1

Hi guys

I’ve been searching around for a while and tried a few different things but I’m getting nowhere fast.

I’m building and issue tracker in which the list of issues will be displayed in a gridview. One of the columns in this grid should display the names of the users that the issue has been assigned to. The users are assigned to an issue through an intermediate table (as there could be many users assigned to an issue), called issue_assignees, which has a foreign key to the users table. Something like this:

issues -> issues_assignees -> users

In the gridview I am able to display the users assigned to an issue by calling a method which just returns a formatted string of assigned users.

What I’d like to be able to do is filter by user. Sorting is not such a big deal as I don’t really see how it could be beneficial when there could be multiple users.

I’m able to get a drop down list of users but the filtering doesn’t work.

Here is my code so far




// public field for assignees in Issue model

public $search_assignees;


// the relations method in Issue model

public function relations()

	{

		return array(

                     ...

                     'assignees' => array(self::HAS_MANY, 'IssueAssignee', array('issue_id'=>'issue_tracker_id')),

                     ...

        }


// search method in Issue model

public function search() {

        ...

        $criteria->compare('assignees', $this->search_assignees);

        $criteria->with = array('assignees');

        ...

So the filter in the ‘Assigned To’ column should contain the users that are present in the issue_assignee table. On selecting a user, the gridview should be filtered to should issues assigned to that user, even though the issues may also be assigned to other users.

I hope this makes sense to you. Feel free to request more information/code.

Thanks

2

try the links

http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview/

http://www.yiiframework.com/forum/index.php?/topic/24280-gridview-filtering-of-relational-data/

3

Thank you thank you thank you! The second link worked a treat.

I missing




$criteria->together  =  true;

Now I have a drop down menu as a filter which filters perfectly on a HAS_MANY relation column

I was soooo close! :slight_smile:

Thanks again

4

Just another thing regarding this issue.

I just noticed that pagination in the gridview is a bit weird.

It’ll show something like 1-7 of 11 results depending on what I filter on.

The filtering is correct, but it should show something more like 1-10 of 11 results.

When there are no filters applied, it will show 1-8 of 30 results. I checked the database and there are 30 results alright. If I delete some records so that there are only 9 records, It will only show 8 records, with no way of paging to the 9th!

When I removed that HAS_MANY filter, everything works fine again, i.e., I see 1-10 of 30.

Anyone have any ideas of whats going on here? :-/

5

Check this https://github.com/yiisoft/yii/issues/2360

6

The reason you’re seeing page counts like that is because of the “together” property, which when set to true forces yii to do eager loading. This means that your related table of “IssueAssignee” gets included in 1 query and yii starts counting those records as well. Using your example of “1-8 or 30 results”, this is one example of how that would happen:


"issue table"           "issue_assignee table"

Issue 1                 Assignee Name 1

Issue 2                 Assignee Name 2

Issue 3			Assignee Name 3

			Assignee Name 4

Issue 4			Assignee Name 5

Issue 5			Assignee Name 6

Issue 6			Assignee Name 7

		        Assignee Name 8

Issue 7			Assignee Name 9

Issue 8			Assignee Name 10

Due to some issue records having more than 1 assignee, the counting stopped at issue #8. Hopefully that makes sense

7

As usual, I recommend checking my RelatedSearchBehavior extension.

You should define your search on ‘issues_assignees’; that table seems to have ‘HAS_ONE’ relations.

8

It’s just like magic! :slight_smile:

I was missing the $criteria->group part. Now it displays the correct amount of issues, also when I filter.

Thank you very much!!!

@Georaldc - makes total sense now, especially when you put $criteria->group into the mix. Thank you for your time and the information. Learned something new about Yii again. :slight_smile:

@Le_top - I’ll definitely be checking out your extension on my test system. Thanks for the link!