Clever Geek Handbook

Laravel order items in database

Hi I am using Laravel and a custom CMS to sync some of the tags stored in the CMS to the database. This function works fine. However, I want to set the order for them numerically when they are inserted into the database, and if a new one syncs in the database, I want this to be added to the database as the last value, i.e. If there are 12 items, the new item will be added as the value 12 (since 0 would be the first value) in the order column. I want this to be accomplished without repeating existing order values.

This is what I have in my repository:

public function sync()
{

    $tags = $this->client->fetchTags('Status');
    $updated = $this->update_tasks($tags);        
    // remove the rest of the statuses
    \Status::whereNotIn('id',$updated->lists('id'))->delete();
}

/**
 * Save any tags to the local DB;
 * @param $tag
 */
public function update_tags($tags)
{


    $return = [];
    foreach ($tags as $tag) {
        $return[] = $tag->id;    
    }


    $local  = \Status::whereIn('cms_tag_id',$return)->get()->toIndexedArray('cms_tag_id');

    foreach ($tags as $tag) {

        if (isset($local[$tag->id]))
        {
            $local_doc = $local[$tag->id];
        } 
        else
        {
            $local_doc = new \Status;
        }
        $local_doc->updateFromCMS($tag);
        $uids[] = $local_doc->cms_tag_id;
    }
    return \Status::whereIn('cms_tag_id',array_unique($uids));
}

And then in my model I have this: Status.php

public function updateFromCMS($tag)
{
    $i = 0;
    $this->cms_tag_id = $tag->id;
    $this->cms_tag_name = $tag->name;
    $this->order = $i++;
    $this->save();
    return $this;
}

However, this adds all the values ​​in the order column as zero, which is undesirable, and any new found are also added with a value of 0. Any ideas on how I can get this order to be preserved, for example 0-100?

Update

Following instructions from @insanebits, I changed my model to the following:

public function updateFromCMS($tag)
{
    $i = Status::orderBy('order', 'DESC')->pluck('order');
    $this->cms_tag_id = $tag->id;
    $this->cms_tag_name = $tag->name;
    $this->order = $i+1;
    $this->save();
    return $this;
}

However, this now changes the order values ​​on all rows. that is, if the largest value is 5, then a value of 0 will be set to 6, and then the entire value will be incremented by 1. Therefore, the established order will be lost. Any ideas what I am doing wrong?

+3


source to share


2 answers


If you don't need to update the IDs and names of existing tags, you should only call updateFromCMS

for new tags:

foreach ($tags as $tag) {

    if (isset($local[$tag->id]))
    {
        $local_doc = $local[$tag->id];
    } 
    else
    {
        $local_doc = new \Status;
        $local_doc->updateFromCMS($tag); //only for new tags
    }

    $uids[] = $local_doc->cms_tag_id;
}

If you need to update the id and names of existing tags, add an argument to updateFromCMS

that tells it whether to change the order or not:



public function updateFromCMS($tag,$update_order)
{    
    $this->cms_tag_id = $tag->id;
    $this->cms_tag_name = $tag->name;

    if($update_order) {
        $i = Status::orderBy('order', 'DESC')->pluck('order');
        $this->order = $i+1;
    }

    $this->save();
    return $this;
}

And change your foreach

as follows

foreach ($tags as $tag) {

    if (isset($local[$tag->id]))
    {
        $local_doc = $local[$tag->id];
        $local_doc->updateFromCMS($tag,false); //don't update order
    } 
    else
    {
        $local_doc = new \Status;
        $local_doc->updateFromCMS($tag,true); //update order
    }

    $uids[] = $local_doc->cms_tag_id;
}
+2


source


What if you set a column order

in your database autoincrement

? for example your migration might look like:

class CreateStatusTable extends Migration {

    public function up() {
        Schema::create('status', function (Blueprint $table) {
            $table->increments('id');
            $table->increments('order');
            /* ... your other fields ... */
            $table->timestamps();
        }
    }
}

This way, you don't have to worry about setting a field explicitly order

in your model, and you know that every time you add a new one Status

to the DB, it will always be assigned the next highest integer value.

Alternatively, you can simply drop the column order

altogether and rely on a field id

that you know will be an integer and will incrementally grow as new rows are added Status

to the database.



The only potential drawback I see with this approach is that if you ever delete Status

, you end up with multiple rows that have non-sequential values ​​for id

or order

, that is, if you had 5 elements in the table and you deleted element with id = 3

, you would get 4 elements with id

values 0, 1, 2, 4

. However, the order will always be saved and the code will look like this:

$statuses = Status::orderBy('id', 'DESC');

always returns an array of objects Status

in the original order that you added to the database.

-1


source






More articles:


All Articles