Using hasMany() without a direct link


I want to make a relation between 2 tables which don’t have an exact identical field for primary key and foreign key. They are like this:

  • table cats with field id_cat (PK)
  • table foods with field food_for, string with data like “cat18”, “cat19”, “dog3” (it’s indexed!)

Here is what i have tried:

    public function getFoods(): ActiveQuery
        return $this->hasMany(Rewards2::class, [])
            ->onCondition("`food_for` = CONCAT('cat', `id_cat`)");

I tried with orOnCondition(), with where(), i always get an empty list on $model_cat->foods

I’ve tried with a false link that can’t work ([‘food_for’=>‘id_cat’]) + onOrCondition() , empty list.

i’ve googled for days now and I’m not sure it’s possible. If it’s not, at least this topic will clear it out (and maybe suggest an alternative!)

Thanks for your help!

I’m unsure what you’re attempting to do with that FK but your database should simply be:

'id' (cat table primary key)
'cat_id' (food table foreign key to cat)

Then your getter will retrieve the food models associated with the cat:

public function getFoods()
    return $this->hasMany(Food::class, ['cat_id' => 'id']);

Hi @Chibi, welcome to the forum.

I have tried to decipher the source code of Yii’s relational ActiveRecord which is a little too complicated for me to understand clearly. But it seems to me that it’s impossible to achieve your goal with Yii’s hasMany().

What do you say, @cebe and @samdark ?

First to make clear what you want to do, I assume you also have a dog table? So do I understand correctly what you want is a relation from food to either cat or dog dependent on the prefix of the food_for column?

For Yii relations in general you need at least one key to build a relation, so you’d need a field in foods that specifies the type of animal. You could split up the food_for column into two columns: food_for (which would be “cat” or “dog”) and food_for_id which contains the primary key of the cat or dog table.

Your relation would be like this:

// inside the Cat model
    public function getFoods(): ActiveQuery
        return $this->hasMany(Foods::class, ['food_for_id' => 'id_cat'])
            ->onCondition("`food_for` = 'cat'");

I can’t change the tables, sadly. This confirms it can’t be done with a hasMany().

I’m going to search something with populateRelation() but that means i can’t use $model->foods, or i override __get() which scares me a little… (and i can say goodbye to ->with(‘foods’) )

this is definitively the case, you can not do eager loading if there is no key.

But you can implement it as a getter without using hasMany.

    public function getFoods(): ActiveQuery
        return Foods::find()->andWhere(["food_for" => 'cat' . $this->id_cat]);

You can access it like $cat->foods to get the records and also $cat->getFoods() to get the query, same behavior as if it would be a relation.

You can not use eager loading with() or joinWith().

1 Like

This is great! It populates the relation in the model, and it’s the same syntax.
Of course, if I list 100 models, there will be 100 queries…

There is just one thing. I tested it and “$model->foods;” was only one model. How to tell I want a list of models?