Phinx: Support for UPDATE & DELETE methods?

[gms8994]

Rob, have you given any thought to creating insert, update, and delete methods instead of having queries written inline?

I don't know that they'd add any specific functionality, but something like the way Zend_Db (v1, not v2) does would be nice to have.

[robmorgan]

Hi @gms8994, yes I've thought about this, but potentially in the next major release (not any time soon) as Phinx is approaching an API freeze and stability is paramount. I'll leave the issue open for now.

[joshribakoff]

Also need a last insert ID. Work around currently is:

$id = $this->fetchRow("select LAST_INSERT_ID()")[0]

[niekoost]

Or use
$this->adapter->getConnection()->lastInsertId();

For better readable source code:
$con = $this->adapter->getConnection();
and then use
$id = $con->lastInsertId();
where needed

[rjmackay]

Any update on this? particularly a method for last insert id?

[maxgalbu]

I'm interested in this too

[lorenzo]

@robmorgan for CakePHP 3.0 we started using phinx as our migrations tool and we'd like to see this feature happen. We are planing on releasing a low level database set of classes that could fit well for providing this feature with very low difficulty. Would you be interested in talking about this at some point?

[maxgalbu]

This pull request could help executing insert or update: https://github.com/robmorgan/phinx/pull/256

It would be as simple as this:

class SomeMigration extends AbstractMigration
{
    public function up()
    {
        $sql = 'INSERT INTO test(intCol, strCol) VALUE(:intColVal, :strColVal)';
        $this->execute($sql, array(
            'strColVal' => 'foo',
        )); 
    }
}

[Keloo]

+1 for lastInsertId()

[trinhhunganh]

+1 for lastInsertId()

[cballou]

+1 for lastInsertId()

[atnartur]

+1 for lastInsertId()

[robmorgan]

I'm pushing this to 0.6.0 in order to get 0.5.2 out the door - it requires a a lot more planning and effort.

[WillGibson]

I'm just working on migrations to remove a whole load of redundant features from a legacy app before we start refactoring in earnest.

Would be great to have a delete() method in \Phinx\Db\Table.

[mbrodala]

I wonder how the API for an update() method could look like? Maybe like this:

$table = $this->table('foo');
$row = ['id' => 1, 'title' => 'Bar'];
$matchFields = ['id'];
$table->update($row, $matchFields);

Which would result in the following SQL query:

UPDATE `foo` SET `title` = "Bar" WHERE `id` = 1

A similar API for delete() could also work.

[salmander]

Any updates on lastInsertId feature?

[arielallon]

+1 for lastInsertId. following.

[salmander]

My current work around:

SELECT currval(pg_get_serial_sequence('{$this->table}', 'id')) AS id

[dellert]

+1 for lastInsertId()

[assada]

+1 for lastInsertId()

[strategy]

+1 for lastInsertId()

[skaos]

+1 for lastInsertId()

[draobrehtom]

$con = $this->adapter->getAdapter()->getConnection();
$con = $con->lastInsertId();

[WGPSenshi]

The above became necessary because adapters are now (0.9+) wrapped by adapterwrappers (usually TimedOutputAdapter), which do not have a getConnection() function.

For most cases,
$this->getAdapter()->getAdapter()->getConnection();
will suffice, but if you for some reasons have multiple wrappers, you'll need to iterate through them.

[josuamarcelc]

+1 for lastInsertId() + LOVE

[aegrey]

+1 for lastInsertId()

[lorenzo]

implemented in v0.10.0

[treadmillian]

Sorry, what was implemented? insert() and delete() methods or the lastInsertId()

If insert() and delete() methods, in which class?

[lorenzo]

@treadmillian look for queryBuilder I’m the documentation. Sorry I cannot be more specific, on mobile right now

[lorenzo]

Through the query builder you can do insert, update, delete and also get the lastInsertId

[treadmillian]

@lorenzo Thanks for that. I noticed, however, that the documentation isn't complete, easy enough to understand how to perform a delete() operation in fairness: http://docs.phinx.org/en/latest/migrations.html#using-the-query-builder

[lorenzo]

@treadmillian would you like to help improve the docs? I can guide you with some examples and the you can add them there :)

[lorenzo]

@treadmillian actually I just realised that the docs are not rendering some examples correctly. I'll go and fix that

[treadmillian]

@lorenzo More than happy to update the docs, and provide examples. I think some examples on transaction based actions would be good, which I can add as well.

[lorenzo]

@treadmillian fixed http://docs.phinx.org/en/latest/migrations.html#using-the-query-builder let me know if it is more clear now

[treadmillian]

@lorenzo Yep, that's much better, thank you. Let me know how I go about updating the documentation.

[lorenzo]

@treadmillian that page should have an "edit in GitHub" button you can use to propose changes to the examples or explanations :)

[treadmillian]

@lorenzo Completely missed that link! Thanks.