How to use WHERE in yii2 joinWith() that is doing eager loading

Question

I have tables: document and document_content. One document can have many contents.

I am using joinWith() method to get data from document_content table together with document using model relations.

The queries executed are these :

SELECT document.* FROM document INNER JOIN document_content ON document.id = document_content.document_id WHERE (lang='1') ORDER BY id DESC LIMIT 10

SELECT * FROM document_content WHERE document_id IN (665566, 665034, 664961, 664918, 664910, 664898, 664896, 664893, 664882, 664880)

I have a problem with this second query. I want it to include this WHERE clause from the first one: WHERE (lang='1')

So I want yii to generate this query:

SELECT * FROM document_content WHERE (lang='1') AND document_id IN (665566, 665034, 664961, 664918, 664910, 664898, 664896, 664893, 664882, 664880)

I have managed somehow to achieve this, but I have code repetition and I do not like it. There must be some better way to do this. This is my code that works, but it's not that good I think:

/**
 * Returns documents by params.
 * 
 * @param  array $params the query params.
 * @return ActiveDataProvider
 */
public function findDocuments($params)
{
    /** @var $query ActiveQuery */
    $query = Document::find();

    // store params to use in other class methods.
    self::$_params = $params;

    // build dynamic conditions for document table
    $this->buildDocumentQuery($query);

    // build dynamic conditions for document_content table
    $this->buildDocumentContentQuery($query);

    // add conditions that should always apply here
    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'sort'  => ['defaultOrder' => ['id' => SORT_DESC]],
        'pagination' => [
            'pageSize' => 10,
        ],
    ]);

    return $dataProvider;
}

/**
 * Relation with document_content table.
 * 
 * @return DocumentContent
 */
public function getDocumentContent()
{
    $query = $this->hasMany(DocumentContent::className(), ['document_id' => 'id']);

    if (isset(self::$_params['lang'])) {
        $query->andFilterWhere([
            'lang' => self::$_params['lang'],
        ]);
    }
}

/**
 * Method that is responsible for building query conditions for document_content table.
 * 
 * @param  object $query ActiveQuery instance.
 * @return ActiveQuery
 */
public function buildDocumentContentQuery($query)
{
    if (isset(self::$_params['lang'])) {
        $query->innerJoinWith('documentContent');
    }

    return $query;
}

As you can see I am checking for params['lang'] on two places. In my relation method and in buildDocumentContentQuery() method. So I am repeating same code on two places, and lang param is not going to be the only one that I want to test, there can be 10 or more.

Basically, I had to do all of this because I could not send any params through yii2 joinWith() method. I do not know what is the best way to add WHERE to query that is generated by eager loading of joinWith(). I made it work somehow, but I think this is dirty.

Does anyone have any idea for better/cleaner solution to this problem ?

score 0 · Answer 1 · answered Apr 07 '16 at 10:09

Model#Document

public function getDocuments($params)
{
    /** @var $query ActiveQuery */
    $query = Document::find();
    $query->getDocumentContentsByLanguage($params['lang']);
}

public function getDocumentContentsByLanguage($lang = null)
{
    return $this->hasMany(DocumentContent::className(), ['document_id' => 'id'])->where('lang = :lang', [':lang'=>$lang]);
}

score 0 · Answer 2 · answered Apr 07 '16 at 10:10

Try this:

$query = $this
   ->hasMany(DocumentContent::className(), ['document_id' => 'id']);

if (isset(self::$_params['lang']) && self::$_params['lang']==1) {
    $query
       ->joinWith('document')
       ->andWhere([
           Document::tablename().'.lang' => self::$_params['lang']
       ]);
}

How to use WHERE in yii2 joinWith() that is doing eager loading

2 Answers2