How to select data from two tables and insert into two tables.

[iamsajidjaved]

Hi, I'm trying to register a user. I have 3 text input fields like name, email, password and one select field named as role. Options of the select tag should contain the entries of the roles table. Current I have superadmin, admin and editor entries in my role table.

What I will do?

1. Will name, email and password.
2. Select user role from select field which are retrieved from roles tables.
3. Hit Submit button.

What the Datatable Editor will does?

1. Insert the name, email and password into users table
2. Insert Id of the role and id of the user into role_user table. There is many to many relationship between users and roles table.

Laravel html blade file code

@extends('adminlte::page')

@section('title', 'Dashboard')

@section('content_header')
<h1>
    Users Table
</h1>
@stop
@section('content')
    <!-- Main content -->
      <div class="row">
        <div class="col-xs-12">
          <div class="box">
            <div class="box-header">
              <h3 class="box-title">Data Table With Full Features</h3>
            </div>
            <!-- /.box-header -->
            <div class="box-body">
                {{$dataTable->table(['id' => 'users',"class"=>'table table-responsive table-bordered table-striped table-hover'])}}
            </div>
            <!-- /.box-body -->
          </div>
          <!-- /.box -->
        </div>
        <!-- /.col -->
      </div>
      <!-- /.row -->
@stop

@section('css')
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.css" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.12/css/dataTables.bootstrap.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.1.0/css/responsive.bootstrap.min.css" type="text/css" />
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.5.0/css/buttons.bootstrap.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/select/1.2.4/css/select.bootstrap.min.css">
{{-- <link rel="stylesheet" href="{{asset('css/editor.dataTables.css')}}"> --}}
<link rel="stylesheet" href="{{asset('css/editor.bootstrap.css')}}">
@stop

@section('js')
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.5.0/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/select/1.2.4/js/dataTables.select.min.js"></script>
<script src="{{asset('js/dataTables.editor.js')}}"></script>
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.5.0/js/buttons.bootstrap.min.js"></script>
<script src="{{asset('js/editor.bootstrap.min.js')}}"></script>
<script>
  $(function() {
      $.ajaxSetup({
          headers: {
              'X-CSRF-TOKEN': '{{csrf_token()}}'
          }
      });

      var editor = new $.fn.dataTable.Editor({
          ajax: "users",
          table: "#users",
          display: "bootstrap",
          fields: [
              {label: "Name:", name: "name"},
              {label: "Email:", name: "email"},
              {label: "Password:", name: "password", type: "password"},
              {label: "Role:",
                name: "roles.name",
                type: "select",
                placeholder: "Select a Role"
              }
          ],
      });

      $('#users').on('click', 'tbody td:not(:first-child)', function (e) {
          editor.inline(this);
      });

      {{$dataTable->generateScripts()}}
  })
</script>
@stop

Datatable Editor Code

<?php

namespace App\DataTables;

use App\University;
use Illuminate\Http\Request;
use Illuminate\Support\Str;
use Spatie\Image\Image;
use Spatie\Image\Manipulations;
use Illuminate\Validation\Rule;
use Yajra\DataTables\DataTablesEditor;
use Illuminate\Database\Eloquent\Model;

class UniversityDataTablesEditor extends DataTablesEditor
{
    protected $actions = ['create', 'edit', 'remove', 'upload'];

    protected $uploadDir = '/storage/upload/';

    protected $model = University::class;

    /**
     * Get create action validation rules.
     *
     * @return array
     */
    public function createRules()
    {
        return [
            'name'  => 'required',
        ];
    }

    /**
     * Get edit action validation rules.
     *
     * @param Model $model
     * @return array
     */
    public function editRules(Model $model)
    {
        return [
            'name'  => 'required',
        ];
    }

    /**
     * Get remove action validation rules.
     *
     * @param Model $model
     * @return array
     */
    public function removeRules(Model $model)
    {
        return [];
    }

    protected $imageWidth = 500;

    public function upload(Request $request)
    {
        try {
            $request->validate($this->uploadRules());

            $type = $request->input('uploadField');
            $dir  = $this->uploadDir . $type;

            $uploadedFile = $request->file('upload');
            $filename     = time() . '.png';

            $uploadedFile->move(public_path($dir), $filename);

            $method = 'optimize' . Str::title(camel_case($type));
            if (method_exists($this, $method)) {
                $id = $this->{$method}($dir, $filename);
            } else {
                $id = $this->optimize($dir, $filename);
            }

            return response()->json([
                'data'   => [],
                'upload' => [
                    'id' => $id,
                ],
            ]);
        } catch (\Exception $exception) {
            return response()->json([
                'data'        => [],
                'fieldErrors' => [
                    [
                        'name'   => $request->get('uploadField'),
                        'status' => $exception->getMessage(),
                    ],
                ],
            ]);
        }
    }

    public function uploadRules()
    {
        return [
            'upload' => 'required|image',
        ];
    }

    protected function optimize($dir, $filename)
    {
        $path = public_path($dir . '/' . $filename);

        Image::load($path)
             ->width($this->imageWidth)
             ->format(Manipulations::FORMAT_PNG)
             ->optimize()
             ->save();

        return $dir . '/' . $filename;
    }
}

Datatable Code

<?php

namespace App\DataTables;

use App\User;
use Yajra\DataTables\Services\DataTable;

class UsersDataTable extends DataTable
{
    /**
     * Build DataTable class.
     *
     * @param mixed $query Results from query() method.
     * @return \Yajra\DataTables\DataTableAbstract
     */
    public function dataTable($query)
    {
        return datatables($query)->setRowId('id')->addColumn('password', '');
    }

    /**
     * Get query source of dataTable.
     *
     * @param \App\User $model
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function query(User $model)
    {
        return $model->newQuery()
            ->leftJoin( 'role_user',   'role_user.user_id',   '=', 'users.id' )
            ->leftJoin( 'roles',   'roles.id',   '=', 'role_user.role_id' )
            ->select('users.id', 'users.name', 'users.email','users.created_at','roles.name as role');
            
    }

    /**
     * Optional method if you want to use html builder.
     *
     * @return \Yajra\DataTables\Html\Builder
     */
    public function html()
    {
        return $this->builder()
                    ->columns($this->getColumns())
                    ->minifiedAjax()
                    ->parameters([
                        'dom' => 'Bfrtip',
                        'order' => [1, 'asc'],
                        'select' => [
                            'style' => 'os',
                            'selector' => 'td:first-child',
                        ],
                        'buttons' => [
                            ['extend' => 'create', 'editor' => 'editor'],
                            ['extend' => 'edit', 'editor' => 'editor'],
                            ['extend' => 'remove', 'editor' => 'editor'],
                        ]
                    ]);
    }

    /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
            [
                'data' => null,
                'defaultContent' => '',
                'className' => 'select-checkbox',
                'title' => '',
                'orderable' => false,
                'searchable' => false
            ],
            'id',
            'name',
            'email',
            'created_at',
            'role',
        ];
    }

    /**
     * Get filename for export.
     *
     * @return string
     */
    protected function filename()
    {
        return 'users_' . time();
    }
}

Controller Code

<?php

namespace App\Http\Controllers\Settings;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\User;
use Yajra\Datatables\Datatables;
use App\DataTables\UsersDataTable;
use App\DataTables\UsersDataTablesEditor;

class UserController extends Controller
{
    public function index(UsersDataTable $dataTable)
    {
        return $dataTable->render('settings.index');
    }

    public function store(UsersDataTablesEditor $editor)
    {
        return $editor->process(request());
    }
}

I'm using laravel 5.7 and latest version of yajra datatable editor.

Thanks. Help will be very appreciated.