CODEWITHSUNDEEP
sqlyiiyii2
Lukas
Lukas

Reputation: 47

Yii: Executing the where condition before joining

I'm currently having a problem with my query

TableA::find()
->select('*')
->joinWith(['TableB'])
->joinWith(['TableC'])
->joinWith(['TableD'])
->where([TableA.attribute1=1 or TableA.attribute2=1])
->andWhere([(further possible conditions on the other Tables)])
->all()

The usual execution order for SQL Queries is From (with joins) and then where.

Is there a way to execute the first where condition before the joins, to reduce the amount of joined lines? Something like

TableA::find()
->where([TableA.attribute1=1 or TableA.attribute2=1])
->select('*')
->joinWith(['TableB'])
->joinWith(['TableC'])
->joinWith(['TableD'])
->andWhere([(further possible conditions on the other Tables)])
->all()

Upvotes: 0

Views: 49

Answers (2)

Michal Hynčica
Michal Hynčica

Reputation: 6144

You can modify the condition that is used for joining the tables.

In SQL it would look like this:

SELECT 
  * 
FROM 
  `tableA`
  JOIN `tableB` ON (
    `tableA`.`FK` = `tableB`.`PK` 
    AND `tableA`.`attr1` = 'someValue'
  ) 
  JOIN tableC ON (`tableB`.`FK` = `tableC`.`PK`)

To do that in Yii you can use ActiveQuery::onCondition() method. If you want to apply this condition only for this one query you can use callback in joinWith() method to modify the query used for join.

$query = TableA::find()
    ->joinWith([
        'tableB' => function(\yii\db\ActiveQuery $query) {
            $query->onCondition(['tableA.attr1' => 'someValue']);
        }
    ])
    //... the rest of query

Other option would be using a subquery in FROM part of sql query like this:

SELECT 
  * 
FROM 
  (
    SELECT 
      * 
    FROM 
      `tableA` 
    WHERE 
      `attr1` = 'someValue'
  ) AS `tableA` 
  JOIN `tableB` ON (`tableA`.`FK` = `tableB`.`PK`)

In yii:

$subQuery = TableA::find()
    ->select('*')
    ->where(['attr1' => 'someValue']);

$query = TableA::find()
    ->select('*')
    ->from(['tableA' => $subQuery])
    ->joinWith('tableB')
    // ... the rest of query

The main weakness of this approach is that temporary table from your subquery won't have any indexes so the join and other conditions will be slower. It might still be worth to use this approach if the tableA has a lot of rows and the condition you want to apply before join will reduce the number of rows significantly.

Upvotes: 1

vvpanchev
vvpanchev

Reputation: 577

Well, I think that's the best way at all. But if you don't print data from relation TableB or TabelC (you just get them only about where condition), you can set the relation like that:

TableA::find()
->joinWith('TableB', false) //dont load data from this relational table
->joinWith('TableC', false) //dont load data from this relational table
->joinWith(['TableD']) //load data from this relational table
->where([TableA.attribute1=1 or TableA.attribute2=1])
->andWhere([(further possible conditions on the other Tables)])
->all()

Upvotes: 0

Related Questions