Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Как добавить поиск по связующим таблицам? #1581

Closed
mkomigor opened this issue Dec 20, 2013 · 66 comments
Closed
Assignees
Labels
Milestone

Comments

@mkomigor
Copy link

@mkomigor mkomigor commented Dec 20, 2013

Есть связка в моделе City

public function getCountry()
{
     return $this->hasOne(Country::className(), ['id' => 'country_id']);
}

И есть поиск сгенерированный крадом

public function search($params)
    {
        $query = City::find();
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        if (!($this->load($params) && $this->validate())) {
            return $dataProvider;
        }

        $this->addCondition($query, 'id');
        $this->addCondition($query, 'country_id');
        $this->addCondition($query, 'name', true);
        $this->addCondition($query, 'create_time');
        $this->addCondition($query, 'update_time');
        return $dataProvider;
    }

Как добавить поиск по названию страны (атрибут $name в моделе Country)?

@cebe
Copy link
Member

@cebe cebe commented Dec 20, 2013

You can add a join to the query:

$query->innerJoin('tbl_country c', 'country_id = c.id AND c.name LIKE :cname', [':cname' => '%'.$params['countryName'].'%']);

You need to add countryName to the $params before calling the method.

@qiangxue there was a question on IRC a few days ago that was about simplifying joins to relations.
Could be done by a method like joinRelation($relationName, $condition, $params) that would automatically call join internally by adding the table and link fields of the relation. What do you think?

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 20, 2013

Will joinRelation() cause eager loading of the relation or is it only for filtering purpose?

@cebe
Copy link
Member

@cebe cebe commented Dec 20, 2013

Not sure, we might add a flag to add a with() call to, not sure about that.

$query->joinRelation('myrel', '...')->with('myrel')->all();
vs.
$query->joinRelation('myrel', '...', [], true)->all();

last param in joinRelation could toggle with but not sure if that is good.

@klimov-paul
Copy link
Member

@klimov-paul klimov-paul commented Dec 20, 2013

@cebe, I have also thought about same thing.
We can create “joinRelation()” method, which can accept ActiveRelation object as an argument. This method should update the list of selected fields to include ones from related table. It also has to apply aliases for the involved tables. Joined relations should be eagerly populated.

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 20, 2013

This method should update the list of selected fields to include ones from related table.

Are you sure? This will require column name disambiguity.

@klimov-paul
Copy link
Member

@klimov-paul klimov-paul commented Dec 20, 2013

Are you sure? This will require column name disambiguity.

I think it is necessary. Although the exact implementation and approach is still “to be defined”.

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 20, 2013

I think we should avoid this. As you can see from Yii 1.1 implementation, disambiguity is not trivial at all. It also makes such query totally different from other queries in 2.0.

@klimov-paul
Copy link
Member

@klimov-paul klimov-paul commented Dec 20, 2013

Without disambiguity, it would be impossible to fetch main model with related one by single query in case they have at least one field with the same name for example “id”.

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 20, 2013

If columns from two tables don't appear in select at the same time, we can disambiguate them through table name prefix, which is much easier.

@klimov-paul
Copy link
Member

@klimov-paul klimov-paul commented Dec 20, 2013

Fine with me

@cebe
Copy link
Member

@cebe cebe commented Dec 20, 2013

I also think that this method should not try to do what we had in yii1.1 it should only be a shortcut that allows joining relations that are already defined. This way you avoid typing the fks again as they are already known by the relation.

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 20, 2013

i also would like to see this in the guide with different other examples, can this be done?

@cebe
Copy link
Member

@cebe cebe commented Dec 20, 2013

First it has to be implemented ;) docs will come of course.

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 20, 2013

sure) also is there any good example of this feature fo Yii1, i saw one but i dont like it because of messing model domain and some search. i think that for Yii1 it also should be in some guide, no?

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 20, 2013

I think we can let joinRelation() always cause eager loading of the corresponding related records.
I do need to introduce the join type parameter, which should default to inner join (because outer join doesn't help much for filtering purpose.)

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 20, 2013

Perhaps name the method as joinWith().

@cebe
Copy link
Member

@cebe cebe commented Dec 20, 2013

Perhaps name the method as joinWith().

when it always includes eager loading joinWith() is a good name as it combines what already exists: join and with.

@samdark
Copy link
Member

@samdark samdark commented Dec 20, 2013

I think join type may be useful. Inner join as default is fine though.

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 21, 2013

but join is slow, no?

@samdark
Copy link
Member

@samdark samdark commented Dec 21, 2013

Depends on the data.

@creocoder
Copy link
Contributor

@creocoder creocoder commented Dec 22, 2013

Why not just add something like together relation option like in Yii 1.1.x?

@klimov-paul
Copy link
Member

@klimov-paul klimov-paul commented Dec 22, 2013

Why not just add something like together relation option like in Yii 1.1.x?

In this case ActiveQuery have to build join tree automatically. This is not a trivial task as we have found out during Yii 1.x development. That is why this feature has been dropped in Yii 2.

Introduction of some “joinWith()” method is a compromise. It allows you eager finding of the related models, while the composition of the query and “joins” are fully under developer control and responsibility.

@qiangxue qiangxue closed this in 4f44bb2 Dec 24, 2013
@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 24, 2013

@qiangxue will you add docs in the guide about how to use it in gridview?

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 24, 2013

Already added docs in the guide about how to use joinWith(). There is no suitable place to talk about gridview. This may be addressed in a wiki article or another guide that is mainly about commonly used widgets.

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 24, 2013

yes, guide about widgets would be more suitable, but there is no such guide, so i think it is needed to be created) joinWith returns only records from main table that has relations in other table, but this can be not right for some gridview widgets, i mean that relation record can be not set, and in gridview we will not get right data, how to handle this situation (i mean it should be LEFT JOIN, or when display data we will loose data from main table that dont have relation on other table)?

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 24, 2013

I don't quite get the problem you are describing here. Perhaps some examples? Just FYI, you can pass $eagerLoading and $joinType parameters to joinWith().

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 24, 2013

example:

  • users
  • users_profiles

not every user have a profile (just an example), how correctly display this situation in gridview? yes i read docs about additional params, but if i do joinWith('profile') i will not get in dataprovider users that dont have profiles, right? how to solve this problem (with left join of course but how to do it in this case)? Can you give an example maybe for girdvew in this case? i will submit docs on widgets then with this example too.

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 24, 2013

Are you sorting by some columns in user_profiles? You should use LEFT JOIN in your case because you are not filtering users by users_profiles (something like User::find()->joinWith('users_profiles', true, 'LEFT JOIN')->all().

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 25, 2013

yes, display and sort for some columns in user_profiles too. So i need to make it like this:

public function search($params)
    {
        $query = Users::find();
        $query->joinWith('users_profiles', true, 'LEFT JOIN'); #this added
        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        if (!($this->load($params) && $this->validate())) {
            return $dataProvider;
        }

        $this->addCondition($query, 'id');
        $this->addCondition($query,'last_name',true); #this added
        $this->addCondition($query, 'name', true);
        $this->addCondition($query, 'create_time');
        $this->addCondition($query, 'update_time');
        return $dataProvider;
    }

am i right? will try this one one basic app.

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 25, 2013

@qiangxue i did it exactly, but search (filter search input) does not work because of problems that i described in above message )

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 25, 2013

I'm confused as you are describing several problems with different statuses. Anyway, I just tried relational query with GridView, and everything worked fine.

sorting not working (was trying ti setup it in different ways even through sort property of provider);

You need to configure the sort property to include last_name as validate sort attributes.

search by related field working only once:...

You don't need to configure the filter option (you also configured it incorrectly because you set value to be empty).

What other problems did you meet?

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 25, 2013

yes, i encountered a lot of problems :D some of them i solved some not)

  1. Sorting. Can you show how to set it correctly? I spend about 30 minutes with browsing in yii dataprovider code and was upset) because i dont know how to set it, i was trying (like in Yii1):
sort=> [
     'attributes' => ['last_name'],
],

but that is wrong and not working.

  1. Yes,right, i forgot to add to value data from $_GET. But anyway how to set it correctly? I thought that gridview can handle this by itself, no? If so, can you show how to do it :)

Have no other problems for now.

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 25, 2013

  1. In your search model, add the following lines:
$dataProvider->sort->attributes['last_name'] = [
    'asc' => ['profile.last_name' => SORT_ASC],
    'desc' => ['profile.last_name' => SORT_DESC],
];
  1. You don't need to set filter option at all. The grid column will add one by itself.
@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 25, 2013

BTW, we are considering adding support for easier related field handling by GridView using dot syntax like in 1.1. There are already issues logged about this.

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 25, 2013

BTW, we are considering adding support for easier related field handling by GridView using dot syntax like in 1.1. There are already issues logged about this.

what features are you talking about exactly? Also can we create one big issue with checkboxes-tasks for Gridview? it will be related to all issues mentioned for gridview features, but it will be easy to track them and see what we have and we will have :)

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 25, 2013

It's about supporting dot syntax like profile.last_name, which will return the last_name value of the profile property.

What issues of gridview are you talking about specifically? So far, you were mainly talking about usage problems, not issues.

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 25, 2013

Still not working) http://gyazo.com/f028d899406cba6ce2fe54701d636b14.png

        $query = UserAR::find();
        $query->joinWith('profile');

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'sort' => [
                'attributes' => [
                    'asc' => ['profile.last_name' => SORT_ASC],
                    'desc' => ['profile.last_name' => SORT_DESC],
                ],
            ],
        ]);

        if (!($this->load($params) && $this->validate())) {
            return $dataProvider;
        }

        $this->addCondition($query, 'id');
        $this->addCondition($query, 'username', true);
        $this->addCondition($query, 'auth_key', true);
        $this->addCondition($query, 'password_hash', true);
        $this->addCondition($query, 'password_reset_token', true);
        $this->addCondition($query, 'email', true);
        $this->addCondition($query, 'role', true);
        $this->addCondition($query, 'status', true);
        $this->addCondition($query, 'create_time');
        $this->addCondition($query, 'update_time');
        $this->addCondition($query, 'last_name',true);
        return $dataProvider;

in index view

    <?php echo GridView::widget([
        'dataProvider' => $dataProvider,
        'filterModel' => $searchModel,
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],

            'id',
            'username',
            'auth_key',
            'password_hash',
            // 'email:email',
            // 'role',
            // 'status',
            // 'create_time:datetime',
            // 'update_time:datetime',
            [
                'header' => 'Last name',
                'value' => function($data){
                    return is_null($data->profile) ? 'Not set' : $data->profile->last_name;
                },
                #'filter' => '<input type="text" class="form-control" name="User[last_name]" value=""/>',
            ],
            ['class' => 'yii\grid\ActionColumn'],
        ],
    ]); ?>
@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 25, 2013

i guess i did something wrong because of even sorting now not working >_< god)

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 25, 2013

You should add my code after creating $dataProvider. Otherwise, you are overwriting the default attributes configuration.

Also, in your column specification about last_name, you need to add 'attribute' => 'last_name', because otherwise the column won't know how to bind the search model with the search field.

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 25, 2013

right, search now working, but sort not) http://gyazo.com/9666db3bbf1fb83f935b0f25c6968039.png
i added as you said:

        $query = UserAR::find();
        $query->joinWith('profile');

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        $dataProvider->sort->attributes['last_name'] = [
            'asc' => ['profile.last_name' => SORT_ASC],
            'desc' => ['profile.last_name' => SORT_DESC],
        ];


        if (!($this->load($params) && $this->validate())) {
            return $dataProvider;
        }

        $this->addCondition($query, 'id');
        $this->addCondition($query, 'username', true);
        $this->addCondition($query, 'auth_key', true);
        $this->addCondition($query, 'password_hash', true);
        $this->addCondition($query, 'password_reset_token', true);
        $this->addCondition($query, 'email', true);
        $this->addCondition($query, 'role', true);
        $this->addCondition($query, 'status', true);
        $this->addCondition($query, 'create_time');
        $this->addCondition($query, 'update_time');
        $this->addCondition($query, 'last_name',true);
        return $dataProvider;

view

    <?php echo GridView::widget([
        'dataProvider' => $dataProvider,
        'filterModel' => $searchModel,
        'columns' => [
            ['class' => 'yii\grid\SerialColumn'],

            'id',
            'username',
            'auth_key',
            'password_hash',
            // 'email:email',
            // 'role',
            // 'status',
            // 'create_time:datetime',
            // 'update_time:datetime',
            [
                'attribute' => 'last_name',
                'header' => 'Last name',
                'value' => function($data){
                    return is_null($data->profile) ? 'Not set' : $data->profile->last_name;
                },
                #'filter' => '<input type="text" class="form-control" name="User[last_name]" value=""/>',
            ],
            ['class' => 'yii\grid\ActionColumn'],
        ],
    ]); ?>
@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 25, 2013

You should add my code after creating $dataProvider. Otherwise, you are overwriting the default attributes configuration.

strange behavior, in Yii1 when i specify it, it works well, maybe it should be like in Yii1?

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 25, 2013

What issues of gridview are you talking about specifically? So far, you were mainly talking about usage problems, not issues.

true, i almost forgot them while trying to make this one work)) but i remember also that big issue with long discussion about gridview features, so i though that issue-task is a good solution to once again summarize developers needs/suggestions.

@qiangxue
Copy link
Member

@qiangxue qiangxue commented Dec 26, 2013

Remove header (otherwise it would overwrite the sort link).

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 26, 2013

Ok,now working. Always was misunderstanding header/label options of column.

@qiangxue many thanks for your detailed explanations, will submit PR on widget usage, also maybe will add SqlDataProvider usage with gridview. Sorry for being such a newbie :)

@samdark
Copy link
Member

@samdark samdark commented Dec 26, 2013

Reopening this one for documentation purposes.

@samdark samdark reopened this Dec 26, 2013
@cebe
Copy link
Member

@cebe cebe commented Dec 26, 2013

Isn't this enough documentation? https://github.com/yiisoft/yii2/blob/master/docs/guide/active-record.md#joining-with-relations

Or do you want to add the gridview questions to the docs?

@Ragazzo
Copy link
Contributor

@Ragazzo Ragazzo commented Dec 26, 2013

@cebe yes, i will make a PR with widgets-gridview.md for ActiveDataProvider/SqlDataProvider for using with gridview.

@samdark
Copy link
Member

@samdark samdark commented Dec 27, 2013

It's gridview part that is tricky.

@samdark samdark removed this from the 2.0 Beta milestone Mar 7, 2014
qiansen1386 pushed a commit to qiansen1386/yii2 that referenced this issue Mar 9, 2014
@cebe cebe added this to the 2.0 Beta milestone Apr 11, 2014
@cebe cebe self-assigned this Apr 11, 2014
@cebe
Copy link
Member

@cebe cebe commented Apr 11, 2014

working on this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Linked pull requests

Successfully merging a pull request may close this issue.

7 participants
You can’t perform that action at this time.