Relations with non identical columns

I’d like to define a relation with 2 tables based on 2 common columns that are not identical, but contains common informations.

The first contain a client number (‘client_code’) like ‘123’ and the second contain the same client number (‘client’) but some of them are prefixes withs zeros like ‘00123’

I’ve tried this without succes:

public function getclient() {

  return $this->hasMany(Clientcode::className(), ['LIKE', 'client' => 'client_code']);

  return $this->hasMany(Clientcode::className(), ['client' => ltrim($this->client_code, '0')]);


  return $this->hasOne(Clientcode::className(), ['client' => 'clienttrim']);

  } **

** public function getClienttrim() {


      return ltrim($this->client_code, '0');


In MySQl, I guess it would be something like:

SELECT * FROM client cl

INNER JOIN clientcode co

ON cl.client_code LIKE co.client

This query works, but is not fast. Neither of the two columns are the primary of their respective tables.

So the question is what would be the most effective way to establish this kind of relation in Yii2 active record?

Thanks a lot.

I don’t think AR’s relational getters can do this.

But you may specify any condition when you use one of ActiveQuery’s join methods, e.g. innerJoin().

Thanks fsb! I’ll check that.