updateOrInsert duplicate entry on update

[iniznet]

Got WordPress database error Duplicate entry '2248' for key 'PRIMARY' for query UPDATE ... when using the function due to the attributes being merged when updating an entry.

I think we shouldn't merge them when updating an entry when we use a primary key for lookup conditions. https://github.com/gin0115/pixie-wpdb/blob/13bb95d314d7bfd8bd057715aacb4b78af1e2126/src/QueryBuilder/QueryBuilderHandler.php#L649-L651

[gin0115]

Hmm do you mind sharing the query you was doing and I can look at patching.

Cheers

[iniznet]

Sure,

Complete error log:

WordPress database error Duplicate entry '1053' for key 'PRIMARY' for query UPDATE wp_group_data SET url='https://dummy.com/',status='1',series='1051',feed='https://dummy.com/feed/',group_id=1053 made by edit_post, wp_update_post, wp_insert_post, do_action('save_post_group'), WP_Hook->do_action, WP_Hook->apply_filters, App\Providers\GroupProvider->updateGroupData, Pixie\QueryBuilder\QueryBuilderHandler->updateOrInsert, Pixie\QueryBuilder\QueryBuilderHandler->update

In code:

$this->DB->table('group_data')
            ->updateOrInsert([
                'group_id' => $post_ID, // 1053 - primary key
            ], $groupData);

Table consisting of:

• group_id, Primary + Foreign reference to ID in wp_posts.
• status
• series
• url
• feed

[iniznet]

Currently, I do a workaround by checking if the entry exists then do insert/update.

$row = $this->DB->table('group_data')
    ->where('group_id', '=', $post_ID)
    ->get();

if ( count($row) === 0 ) {
    $groupData['group_id'] = $post_ID;
    $this->DB->table('group_data')
        ->insert($groupData);
} else {
    $this->DB->table('group_data')
        ->where('group_id', '=', $post_ID)
        ->update($groupData);
}

[gin0115]

This has been changed

Now we check if the row exists as before, but now rather than merging the values and conditions together we only update or insert with the values passed.

Let me know if that solves the issue.

Only caveat now all values that could be inserted or updated must be in the values array.

[gin0115]

and thanks again for using this lib, I will be honest I didn't expect anyone to ;)

[iniznet]

Thanks! it's fixed the issue.

I stumbled across this lib when looking for a WordPress query builder, it's really made me easier to fetch/store something quickly.

[gin0115]

yeah ive wanted one for a while too, tried writing my own from scratch a few times in the past, but never got very far. Life would be a lot simpler if WP used PDO, well life would be a lot simpler if WP wasn't as old as it is with the backwards compatibility promise.

Any ideas feel free to put them in :)