ThinkingMan
1 month ago
374
14
Eloquent

Fetch data from remote database and add/update to local database

Posted 1 month ago by ThinkingMan

Background:

My app has a customers table that I want to "sync" with customer records from a remote database. The remote database contains customer records with core information like account number, name, address, etc... I want to add or update those records in my app so I can extend the functionality for things like call tasks and marketing or other CRM related functions.

I have to do this because the customer uses an on-premise solution which doesn't have API's. But I do have access to the MySQL data so I can read the records. I'll do an initial load then look for deltas based on the "last updated" field on the remote data.

Code:

Remote Customer Table Model

class RCustomers extends Model
{
    //
    use RemoteConTrait;

    protected $primaryKey = 'cr_customer';

    protected $table= "CR";

    protected $connection = 'remote';

    protected static function boot()
    {
        static::addGlobalScope('cr_customer', function (Builder $builder) {
            $builder->where('cr_customer', '<>' , 0);
        });

        static::addGlobalScope('select_fields', function (Builder $builder) {
            $builder->select(
                [
                    'cr_customer as cr_customer',
                    'cr_job_number as cr_job_number',
                    'cr_sort_name_type as cr_sort_type',
...

Local Customer Table Model

class Customers extends Model
{
    use Notifiable;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    //
    protected $fillable = [
        'cr_customer',
        'cr_job_number',
	...

I was going to have a job that ran daily to do this. This is where I keep getting hung up. I have tried a few approaches and I know I am wrong but this is where I am. What's the best approach?

 DB::connection('remote')->table('CR')->orderBy('cr_customer')->chunk(10, function ($remoteCRs) {
                $remoteCRs->map( function ($data){

                    return $data;
                });
                foreach ($remoteCRs as $remoteCR) {

                    //customer record
                    Customers::all()

                        ->updateOrCreate([
                            'cr_customer' => $remoteCR->cr_customer
                        ],
                            $remoteCR->getAttributes());

                    echo $remoteCR->order_number."\n";



                }
            });

Please sign in or create an account to participate in this conversation.