How can I join two tables on multiple keys?

Need Help
1

I’m working in cakephp 3.3. I have two tables, let’s say they’re called Products and Events.

Products have an id, and may have a name & tag or both, or neither

Events have an id, timestamp, and may have either a name or a tag, both or neither.

I want to get each event along with the product associated with that event. In EventsTable.php I can do this:

        $this->hasOne('Products',[
            'foreignKey' => 'name',
            'bindingKey' => 'product_name'
        ]);

and I can do this:

        $this->hasOne('Products',[
            'foreignKey' => 'tag',
            'bindingKey' => 'product_tag'
        ]);

But what I’d like is to use tag, and if it’s not present, use name. Is this possible?

2

This sounds like a data disaster waiting to happen. You’re using strings as foreign keys?

Could you live with two associations, ProductsByName and ProductsByTag, and then a virtual field that looks for which one is set?

3

Yeah I don’t like it much either! It’s hard because the events come from disparate sources. Where in the docs does it describe how to add associations like that? I’ve never worked with virtual fields.

4

https://book.cakephp.org/3/en/orm/entities.html#creating-virtual-fields