Composite foreign Key to single other key

[ragingdave]

I have a condition where I am in need of relating a table with 2 keys that end up relating to another table as one key. I saw there was an old issue about using DB::raw potentially and this might be a great use case for it. Specifically some way of being able to say this composite key when concatenated, relate to this single primary key in another table. Would this be something that this package could cover?

[yurii-github]

hello

can you please elaborate your question so we may probably find a solution.
As I understand now, you want something like

---table1---
col1
col2

--table2--
col <---  table1.col1+table1.col2

if so, then I may disappoint you, because this is something not easily achievable, at least for relational DBs. You may emulate such relation but in the end you won't build a single JOIN on such PKs. Such PK will work only on INSERT ... ON DUPLICATE KEY UPDATE statement, so in the end you kinda "must" to have 2 extra columns to make the join on searches during selections.

regards

[ragingdave]

You nailed what I'm looking to do, and I never said it had to apply to a relational db. My use case is a legacy (cobol) system that I'm connecting to and their data structures are too time intensive to change most of the time. Considering that this package already does manual manipulation to generate the relational queries, while yes I would agree it's an emulation only but I would disagree that it's not easily achievable. From a 10k foot view, it would be adding array vs string checks on either side of the relation. Maybe I'm missing something though as you seem confident that it either can't or would take a ton of work to achieve.

[yurii-github]

I see. thank you for your response.

I will clarify my point a bit more.
This package changes behavior for illuminate\database package, and illuminate\database supports next database engines

On other side, COBOL is a programming language, not a database engine.
Theoretically, we may emulate relation you are referring to by doing something like this

SELECT * FROM table1
JOIN table2 ON table2.col = CONCAT(table1.col1, table1.col2)

but this JOIN will not hit query optimizer and our indexes will be skipped.

As solution in MySQL or PostgreSQL, we may create new virtual column from those 2, thus we end up with 3 columns, and no need for any emulation.

And yes, I may be biased on the subject, because in my career I have failed to achieve similar thing that you are asking in this issue, ending up with 3 columns (2 PKs and 1 generated from those 2 PKs and used as main PK for some rare cases like INSERT ... ON DUPLICATE KEY UPDATE)

p.s. what do you mean by "From a 10k foot view", I did not understand it.

regards

[ragingdave]

First off, I do know that COBOL is a programming language and not a database, I was simply using cobol as the time reference to just how legacy the system I'm dealing with is.

So with that out of the way, I think the point you are talking about are complex clauses like a whereHas or a has where there is no direct data usage (like in eager loading) where the foreign keys are collected from available models and queried directly on the foreign key. In a has, the join would be an issue as you describe. I could make the argument that this functionality should not be the norm and should be few and far between but that's kind of this whole package right?

I would also say that I've been mostly working around this limitation of not allow 2-> 1 relation keys as I proposed, so maybe this is something that's not totally needed, or wanted.

"From a 10k foot view", I just meant from a very high level those were the changes I saw as being needed is all.

[erikn69]

maybe related #122