Is there a way to create a custom relationship (custom column name linking to a specific field in the relationship's table)?

[Sam Walsh]

A system I'm building synchronises content from an API as Silverstripe DataObjects. The source already has it's own means for linking between objects using codes (e.g. EventCode on a Registration DataObject links to an Event DataObject that has that EventCode).

The problem I'm having is that I need to maintain this relationship schema (using the API's codes, instead of Silverstripe's generated IDs).

Is there a way to create a custom relationship like this? To help explain what I mean, here's the Laravel equivalent: `return $this->belongsTo('App\Post', 'foreign_key', 'other_key');`

Thanks, Sam

[Sam Minnée]

Hi Sam,

Your best bet is probably to not use the SilverStripe relation system at and instead just make a function that returns the related object or list of objects.

Something like this:

function Post() {

   if(!$this->OtherKey) return null;

   return Post::get()->filter([ "ForeignKey" => $this->OtherKey ])->First();

}

--
You received this message because you are subscribed to the Google Groups "SilverStripe Core Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to silverstripe-d...@googlegroups.com.
To post to this group, send email to silverst...@googlegroups.com.
Visit this group at https://groups.google.com/group/silverstripe-dev.
For more options, visit https://groups.google.com/d/optout.

--

Sam Minnée

CEO

SilverStripe Limited

[Sam Walsh]

Thanks for your reply Sam.

Unfortunately, a technique like that means that a lot of the functionality in the CMS (e.g. Filtering, Grid sorting and adding existing items via search) doesn't work, because it relies on IDs as foreign keys, not the custom methods e.g. your Post method.

[Patrick Nelson]

Think of it as just that -- synchronizing. You just have redundant relationship identifiers (foreign keys). So, in this case, SilverStripe has it's own, then you will need to setup your own custom one and synchronize that. So, I'd suggest setting up your own static methods to perform manual lookups of existing objects based on some other field (e.g. "OtherKey"), such as ::findByOtherKey($otherKey) and then that returns null or an instance of YourDataObject. Also of course be sure to setup an index on "OtherKey" for faster lookups :)

Then, maintain that key. Then you use the SilverStripe ORM to start attaching/detaching relationships based on your newly discovered object, etc. How about that? Just basically setting up middle-ware to connect between "Original System's way" and then the "SilverStripe way".

--

[Sam Walsh]

Thanks Patrick,

That's exactly my backup plan. I've given that a go, but unfortunately, this extra relationship linking done by onBeforeWrite, means that the synchronisation process time increases tenfold, as each save needs to look up and find the Event that it relates to, using the EventCode. Even finding the EventID using raw sql takes too long.

If I can't find a way of supporting custom foreign keys then that'll be my backup plan.

On Wednesday, 8 June 2016 12:02:06 UTC+12, Sam Walsh wrote:

[Patrick Nelson]

Why do the lookup every time? I think approach is fairly sound (i.e. it's solid, at least, and saves YOU time which is more valuable than computer time). If it's too slow, the next step in my opinion is optimizing that. So, maybe cache something somewhere or just attempt a lookup if the key changed on write. So, maybe use ->isChanged() or ->getChangedFields()?

Then again I'm not particularly familiar with the details of the implementation.

--

[Sam Walsh]

Sorry, I misread your message. The problem with this solution, as mentioned to Sam Minnée is that the a lot of the Silverstripe CMS functionality doesn't work with this type of relationship (using functions to fetch relations).

The lookup would have to be done every time the EventCode changes (with isChanged() like you said). Problem is though that even with caching (I might be wrong, because I haven't benchmarked yet), trying to create this Silverstripe relationship means that it needs to do the lookup on hundreds of thousands of records PER relationship. I just feel like there must be a better way.

[Patrick Nelson]

I don't get it, because if you're fetching by ID (an indexed column) then you're also, still, filtering by hundreds of thousands of records. It doesn't matter if it's "ID" or EventCode, there's nothing particularly special about ID except it's an index and (as it turns out) deduplicated primary key. So, if you're sure to index the new "EventCode" field, it's not a problem like you think it is. You're working with an RDBMS (MySQL or something MySQL-esque, like Percona, etc) so it won't matter. That lookup overhead shouldn't be something you would worry about per se.

Give it a shot -- index that field and see how long a lookup takes and compare that lookup time against the base ID field. If it's the same, but still takes too long, you've got a more fundamental architectural issue with the app at large that is outside of the scope of SilverStripe itself :)

[Marcus Nyeholt]

I've got an experimental module that does between-instance syncronisation of data objects

https://github.com/nyeholt/silverstripe-syncrotron

Each content item is assigned a GUID in addition to its standard SS assigned ID. When mapping relationships, these are converted into an item that just references the Giud and type of the object being related to. So in the list of items being sync'd, the raw object appears, then comes a list of 'relations' that need syncing in a structure like

{

  "SYNCROREL": true,

  "Type": "TimesheetRecord",

  "ContentID": "23a78c73-64d0-43e4-aad3-0038a836ef69",

  "MasterNode": "74451b1a-aa04-4ddc-b1b4-dc517ad9e712",

  "has_one": {

    "Project": {

      "ContentID": "107b5149-ccf5-4653-ac58-9d144cda9513",

      "Type": "Project"

    },

    "WorkItem": {

      "ContentID": "5e03894a-71f6-445b-bee8-b57f0e7faa4f",

      "Type": "WorkItem"

    }

  }

}

Many many items can be sync'd in a similar manner just by pointing at the relevant objects in an array.