Better support for composite keys, or am I doing something wrong?
Developer Preview
This is an issue in EE4 and EE3, though I’ve never encountered it personally until testing EE4. It was previously been reported by 2 customers but I thought I fixed the issue, but it seems there is a deeper issue here. If you’re not familiar with how Publisher saves data it basically has an exp_publisher_titles and exp_publisher_data table that are near clones of the native exp_channel_titles and exp_channel_data tables. I referenced the ChannelEntry model along with its Gateways for the variable content table (e.g. channel_data). EE’s models set the primary key to the “entry_id” column which works fine b/c there is always a 1 to 1 relationship since EE doesn’t have drafts or translations. But Publisher maintains up to 3 additional versions of an entry for each language added to the site (the default language open status, language x open, and language x draft status).
We’ve chatted about this before but apparently its still an issue.
The issue I’m having is the id column value may not always match up. For example in the screenshots below it shows that the German version of entry #1 has the id value of 77 for both tables, but in some cases it may not always be 77. If for some reason the db assigns the id 78 to the row in the publisher_data table, my EntryTranslation model can’t properly query the data. It’ll use the id 77 when finding the row from publisher_data, which may be assigned to an entirely different entry, or sometimes the very confusing thing the draft status of the same entry. It also gets really out of whack when it goes to save an entry. Since the primary key values do not match it’ll sometimes create an entirely new row in the publisher_data table, thus I have duplicate rows for an entry’s data.
Is there a better way to support composite keys? Ideally I’d like entry_id|lang_id|status to be my key and id can just be an auto-incriminator that the model doesn’t really care about. Or can I change my models (below) so this isn’t an issue?
class EntryTranslation extends VariableColumnModel
{
const NAME = 'publisher:EntryTranslation';
protected static $_primary_key = 'id';
protected static $_gateway_names = ['EntryTranslationTitleGateway', 'EntryTranslationDataGateway'];
}
class EntryTranslationTitleGateway extends Gateway
{
protected static $_table_name = 'publisher_titles';
protected static $_primary_key = 'id';
}
class EntryTranslationDataGateway extends VariableColumnGateway
{
protected static $_table_name = 'publisher_data';
protected static $_primary_key = 'id';
}Saving an entry example code… as you can see I’m using my EntryTranslation model.
$entry = $this->find($entryData['entry_id'], $languageId, $status);
if (!$entry) {
$entry = ee('Model')->make(EntryTranslation::NAME);
}
$entry
->set($entryData)
->setEntryId($entryId)
->setChannelId($entryData['channel_id'])
->setLanguageId($languageId)
->setAuthorId($authorId)
->setStatus($status)
->setTitle($entryData['title'])
->setEditDate(ee()->localize->now)
->setEntryDate($entryData['entry_date'])
->setEditBy($editBy)
->setIsNewEntry($this->requestCache->get('isNewEntry'))
;
$entry->save();DB Screenshots:
It looks like my publisher_data table has the id column set to auto_increment, where as the channel_data column is not set to auto_increment. Is the fix as simple as removing the auto_increment from publisher_data?
I tried removing the auto_increment and it doesn’t make a difference. The issue does come down to that ID column. If row id 50 in publisher_titles is the English draft version of an entry, its expecting that id 50 in publisher_data to also be the English draft version of an entry, but in some cases row 51 may be the English draft version, so its not pulling back all of the correct content for an entry. I think it becomes an issue when the model saves the entry and doesn’t update the correct row in the publisher_data table… it updates whatever row 50 is, even if the entry_id, lang_id, and status do not match the values of publisher_titles.
I hope I’m describing this so it makes sense 😊
I think the issue boils down to this method: https://gist.github.com/litzinger/14be9d957e5d3e5c8d782f3d615eca5f
It doesn’t have a concept of primary_key being an array, thus a composite key.
One more update. I think I was able to work around the problem by overloading the save() method in my EntryTranslation model.
/**
* Overload the save method so we can work with composite keys better.
*/
public function save()
{
$values = $this->toArray();
$md = new MetaDataReader(self::NAME, 'BoldMinded\Publisher\Model\EntryTranslation');
$gateways = $md->getGateways();
/** @var Gateway $gateway */
foreach ($gateways as $gateway) {
$gatewayFields = $gateway->getFieldList();
$data = array_intersect_key($values, array_flip($gatewayFields));
// Remove ID from the insert, we don't care about its value.
unset($data['id']);
if ($this->isNew()) {
$this->forwardEventToHooks('insert');
ee()->db->insert($gateway->getTableName(), $data);
} else {
$this->forwardEventToHooks('update');
ee()->db->update($gateway->getTableName(), $data, [
'entry_id' => $data['entry_id'],
'lang_id' => $data['lang_id'],
'status' => $data['status']
]);
}
}
$this->forwardEventToHooks('save');
// update relationships
foreach ($this->getAllAssociations() as $assoc) {
if (isset($assoc)) {
$assoc->save();
}
}
return $this;
}
Hey Brian, sorry for the delay, been on vacation. Yeah we still just don’t have good support for composite keys, we haven’t needed it much except for member groups which we just work around. Did you arrive at a solution that’s working for you? I know it’s not ideal to override save() but I think adding composite key support to the models would be quite a bit more work to do at this stage. One thing I’ll mention is I’m not sure your calls to forwardEventToHooks()are doing anything as nothing is being called to trigger those events since your overriding save(). But if you don’t need those events, no big deal.
Yep, overloading the save method seems to work fine for me. I think its an acceptable solution since it does play nice with the models… I didn’t have to refactor anything else, just overloaded save().
Yeah, I ended up removing those forwardEventToHooks calls 😊