You are not logged in.

Important Note: This forum is being archived and will be completely locked down on the 7th of February. The new forum can be found at http://laravel.io.

Announcement

    Laravel 4 Advanced Architecture: Book Now On Sale

#1 2012-12-29 21:50:34

Rob
Apprentice
Registered: 2012-12-20
Posts: 4

Insert ignore and insert on duplicate

I'm not sure how to submit these to the core or even if they are appropriate to be submitted. It may also have been better to override the core files rather than edit them, but er... yeah not 100% on that either. Okay so this isn't going to well, so i'll just get on with the code:

The first change just adds the grammar for the queries, very simple:

laravel\database\query\grammars\grammar.php

	/**
	 * Compile a SQL INSERT statement but ignore errors thrown by duplicate unique columns
	 *
	 * @param  Query   $query
	 * @param  array   $values
	 * @return string
	 */
	public function insert_ignore(Query $query, $values)
	{
		return 'INSERT IGNORE'.substr($this->insert($query, $values), 6);
	}

	/**
	 * Compile a SQL INSERT statement but ignore errors thrown by duplicate unique columns
	 *
	 * @param  Query   $query
	 * @param  array   $values
	 * @return string
	 */
	public function insert_with_update(Query $query, $values, $on_update)
	{
		return $this->insert($query, $values).'  ON DUPLICATE KEY UPDATE '.$on_update;
	}

The next just duplicates the insert function and uses the duplicate function instead. It would be best if this code wasn't duplicated and perhaps put as options to the insert() function.

laravel\database\query.php

/**
	 * Insert an array of values into the database table.
	 *
	 * Ignores any errors thrown by duplicates in unique columns.
	 *
	 * @param  array  $values
	 * @return bool
	 */
	public function insert_ignore($values)
	{
		// Force every insert to be treated like a batch insert to make creating
		// the binding array simpler since we can just spin through the inserted
		// rows as if there/ was more than one every time.
		if ( ! is_array(reset($values))) $values = array($values);

		$bindings = array();

		// We need to merge the the insert values into the array of the query
		// bindings so that they will be bound to the PDO statement when it
		// is executed by the database connection.
		foreach ($values as $value)
		{
			$bindings = array_merge($bindings, array_values($value));
		}

		$sql = $this->grammar->insert_ignore($this, $values);

		return $this->connection->query($sql, $bindings);
	}

	/**
	 * Insert an array of values into the database table.
	 *
	 * Ignores any errors thrown by duplicates in unique columns.
	 *
	 * @param  array  $values
	 * @return bool
	 */
	public function insert_with_update($values, $on_update)
	{
		// Force every insert to be treated like a batch insert to make creating
		// the binding array simpler since we can just spin through the inserted
		// rows as if there/ was more than one every time.
		if ( ! is_array(reset($values))) $values = array($values);

		$bindings = array();

		// We need to merge the the insert values into the array of the query
		// bindings so that they will be bound to the PDO statement when it
		// is executed by the database connection.
		foreach ($values as $value)
		{
			$bindings = array_merge($bindings, array_values($value));
		}

		$sql = $this->grammar->insert_on_update($this, $values, $on_update);

		return $this->connection->query($sql, $bindings);
	}

Okay so usage. This is very simple, imagine you have a table that saves each term entered into the search box. Of course you only want to save each term once, so now you can do an insert which will insert a new row if the term doesn't exist, or do nothing if it does.

You will need a unique index on 'term'

DB::table('search_terms')->insert_ignore(array(
    'term'  => 'My Search Term'
));

The on duplicate function performs an update on the duplicate row, this may be useful if we want to increment a count of how many times a term has been searched for. When doing the insert we set this to 1, to denote the fact that the term has been searched for once. If the term already exists then we update the existing count by 1 instead.

DB::table('links')->insert_with_update(array(
    'term'  => 'My Search Term',
    'hits' => 1
), 'hits=hits+VALUES(hits)');

We could have just used "hits=hits+1", but by using VALUES(hits) we can get the value that we were originally trying to insert. Thus if we tried to insert a hits count of 2 the hits column would be incremented by 2 rather than 1.

Full documentation of how to use "ON DUPLICATE KEY UPDATE" can be found here: dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Offline

#2 2013-01-10 12:29:21

rixsta
Artisan
Registered: 2012-11-12
Posts: 38

Re: Insert ignore and insert on duplicate

Hi Rob,

I've raised this as an issue:
http://laravel.uservoice.com/forums/175 … pdate-such-

Please feel free to vote that link, with any luck we'll get official support for this smile

Offline

#3 2013-01-10 13:42:29

Phill Sparks
Quality Team
From: Leicester, UK
Registered: 2011-09-29
Posts: 1,480
Website

Re: Insert ignore and insert on duplicate

Guys, to increase the chance that something like this gets in please do search around for methods of implementing functionality in the other databases.  If it's not possible (even by an awkward method) then it's unlikely to get supported.  A little research from requesters helps us very busy team members a lot smile

Offline

#4 2013-01-10 16:02:51

rixsta
Artisan
Registered: 2012-11-12
Posts: 38

Re: Insert ignore and insert on duplicate

Hi Phill & team,

Hope this helps, though I'm only working with MySQL so can only confirm actual testing of this function in MySQL. But I'm hoping someone more experienced in using all 4 databases can confirm these methods and see if a wrapper function can be provided in the Laravel core.

taken straight from Wiki: http://en.wikipedia.org/wiki/Upsert

MySQL
MySQL, for example, supports the use of INSERT ... ON DUPLICATE KEY UPDATE syntax[1] which can be used to achieve the a similar effect with the limitation that the join between target dans source has to be made only on PRIMARY KEY or UNIQUE constraints, which is not required in the ANSI/ISO standard. It also supports REPLACE INTO syntax,[2] which first attempts an insert, and if that fails, deletes the row, if exists, and then inserts the new one. There is also an IGNORE clause for the INSERT statement,[3] which tells the server to ignore "duplicate key" errors and go on (existing rows will not be inserted or updated, but all new rows will be inserted).

SQLite
SQLite's INSERT OR REPLACE INTO works similarly. It also supports REPLACE INTO as an alias for compatibility with MySQL.[4]

SQL Server"
Microsoft SQL extends with supporting guards and also with supporting Left Join via WHEN NOT MATCHED BY SOURCE clauses.

Postgres SQL - taken from http://www.postgresql.org/docs/current/ … RT-EXAMPLE
This approach seems to rely on a) first attempting an UPDATE and if successful returning to the next item in the batch of records, otherwise it will attempt to do an INSERT and if it fails it is caught and just passed to the next item in the loop.

Offline

#5 2013-03-21 04:09:53

voidman
Apprentice
Registered: 2013-02-18
Posts: 1

Re: Insert ignore and insert on duplicate

@Rob, thanks, it works for me.

Offline

Board footer

Powered by FluxBB

');