How to get array with rows related to another table for Bllim Datatable - Laravel

Question

I use Bllim/Datatables package for the datatables on my web application; but I can't retrieve all the related rows.

I use the code below:

$books= $client->books()->where('books.type', 0);

And I send it to Datatables::of method as follow:

return Datatables::of($books)
    ->edit_column('type', '{{$type}}')
    ->edit_column('created_at', function($obj) {
    return $obj->created_at->format('d/m/Y (h:i)');
})
->set_index_column('id')
->make();

But all this return an Internal Server Error (500) with the follow message:

{"error":
   {
   "type":"ErrorException",
   "message":"Undefined property: Illuminate\\Database\\Eloquent\\Builder::$columns",
   "file":"\/var\/www\/clients\/myapp\/vendor\/bllim\/datatables\/src\/Bllim\/Datatables\/Datatables.php",
   "line":256
   }
}

MY DATABASE STRUCTURE:

create table clients(
    id int not null auto_increment,
    name varchar(10) not null,
    password varchar(60) not null,
    unique(name),
    primary key(id),
    created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    updated_at timestamp NOT NULL DEFAULT now() ON UPDATE now()
)CHARSET utf8 COLLATE utf8_spanish_ci;

/* PIVOT TABLE */
create table book_client(
    id int not null auto_increment,
    book_id int,
    client_id int, 
    primary key(id),
    FOREIGN KEY (book_id ) REFERENCES books(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id ) REFERENCES clients(id) ON DELETE CASCADE
)CHARSET utf8 COLLATE utf8_spanish_ci;

create table books(
    id int not null auto_increment,
    name varchar(50) not null,
    description varchar(500),
    type varchar(10) not null,
    primary key(id),
    created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    updated_at timestamp NOT NULL DEFAULT now() ON UPDATE now()
)CHARSET utf8 COLLATE utf8_spanish_ci;

In the views I have the next:

/*In the view of clients*/
public function books(){
    return $this->belongsToMany("Book");
}
/*In the view of books: (yes, in my case, a book could belong to multiple clients*/
public function clients(){
    return $this->belongsToMany("Client");
}

Anybody Know the method for make I need?

Answer 1

You need to use the next sentence:

/* The client whose ID is 1*/
$client = Client::find(1);

$client->books()
->getQuery()
->getQuery()
->select(array('id', 'ColumnA', 'ColumnB'));

You can use the where clausule also:

$client->books()
->getQuery()
->getQuery()
->select(array('id', 'ColumnA', 'ColumnB'))
->where('id','=',1);

Note which I used getQuery() twice, this is because Bllim/Datatables need an Query/Builder object.