Yii2 active record self join taking forever to load

Yii 2.0
1

My query takes forever, here is the code:

$auditFilter = AuditTrail::find();
$auditFilter->select([new Expression('MAX(id) as m_id')]);
$auditFilter->andFilterWhere(['model' => 'modalClass']);
$auditFilter->groupBy('model_id'); //unique id's of modalClass

$audit = AuditTrail::find();
$audit->leftJoin(['auditFilter'=>$auditFilter], 'auditFilter.m_id = id');

I think this generates

SELECT * FROM `audit_trail` LEFT JOIN( SELECT MAX(id) AS m_id FROM `audit_trail` WHERE (`model`='modalClass') GROUP BY `model_id`) `auditFilter` ON id = auditFilter.m_id

which takes forever.

The only way the query worked faster when testing was like this:

SELECT * FROM `audit_trail` JOIN (SELECT MAX(id) AS m_id FROM `audit_trail` WHERE (`model`='modalClass')  GROUP BY `model_id` ) `auditFilter` ON id = auditFilter.m_id

But I don’t know how to just “JOIN” in Yii query way, instead of “LEFT JOIN” which is default in JoinWith and most Join in Yii in Yii2. Would really appreciate your help.

2

I think this generates

It’s better to know for sure. The query should be in the debug toolbar.

The only way the query worked faster when testing was like this:

Unlikely that’s the only way. What did you want to achieve with the query you’ve posted?

3

@samdark basically I join this query to another multi-join query for report. Purpose of this part of query is to find the last person (which is also a field in audit trail) who made changes to a field in “modalClass (pseudonym)” which is recorded in audit_trail table. audit_trail basically records all DB changes.

So I am trying to get the last/max ID from audit_trail table grouping with model_id (PK) where model field is "modalClass ". And then joining result with itself to get only rows of last/max ID from the table so I can get the user_id of only max id rows.

Not sure if I make sense and yes… Above query does generate “Left Join” as I have indicated and I tried raw sql in my database directly and query above query worked when i changes “LEFT JOIN” to “JOIN”

4

LEFT JOIN will give you different result compared to JOIN.

What does EXPLAIN give you?

5

The result should be same for both query, the problem is:

LEFT JOIN doesn’t bring any result… query runs for very long time and gets timed out… and when i just run with JOIN, the result displays within 5 - 7 Sec. So I want to know how to just “JOIN” in Yii2.

6 
$audit->innerJoin(['auditFilter'=>$auditFilter], 'auditFilter.m_id = id');
1 Like
7

@samdark “INNER JOIN” worked. Thanks! Really appreciate your work in Yii…Big fan :slight_smile: