Laravel 5.5 - 将MySQL查询传递给Javascript函数/ DataTables

Question

I'd like to pass a SQL query to a Javascript function, found in my view, which will populate a HTML table using the DataTables API. I'm having trouble converting the object array into plain JSON. How would I go about doing so?

[web.php]

Route::get('/admin', function () {
    $users = DB::connection('mysql')->select('SELECT * FROM users');
    return view('admin', compact('users'));
});

[admin.blade.php] (HTML)

<div class="row">
    <div class="col-lg-6">
        <div class="card mb-3">
            <div class="card-header"><i class="fa fa-user"></i> Users</div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-bordered table-striped table-compact" width="100%" id="userTable" cellspacing="0"></table>
                </div>
            </div>
        </div>
    </div>
</div>

[admin.blade.php] (Javascript)

<script type="text/javascript">
    $(document).ready(function() {
        $('#userTable').DataTable( {
            "scrollX": true,
            "scrollY": '55vh',
            "scrollCollapse": true,
            "paging": false,
            dom: 'ft',
            "aaData": "{{ $users }}",
            "aoColumns": [
                { "sTitle": "Name",   "mData": "name" },
                { "sTitle": "Email",  "mData": "email" },
            ]
        } );
    } );
</script>

[Example JSON object/array returned from database]

array:1 [
  0 => {#195
    +"id": 1
    +"name": "Josh_W"
    +"email": "[e-mail]"
    +"group": "Admin"
    +"level": 5
    +"password": "PASSWORD"
    +"remember_token": null
    +"created_at": "2017-10-12 14:42:38"
    +"updated_at": "2017-10-12 14:42:38"
  }
]

Answer 1

The select() method returns an array. You need to collect() the array and then call toJson():

<script type="text/javascript">
    $(document).ready(function() {
        $('#userTable').DataTable( {
            "scrollX": true,
            "scrollY": '55vh',
            "scrollCollapse": true,
            "paging": false,
            dom: 'ft',
            "aaData": {!! collect($users)->toJson() !!},
            "aoColumns": [
                { "sTitle": "Name",   "mData": "name" },
                { "sTitle": "Email",  "mData": "email" },
            ]
        } );
    } );
</script>

To avoid escaping your JSON use the {!! !!} syntax.

See the Laravel documentation on collections and displaying data on views for reference.