Using Laravel Collection methods(first, paginate etc) on a fluent query

Question

I'm currently using Laravel's fluent queries to grab data from an MSSQL DB. In order to keep using Eloquent's methods (list, sort, filter etc), I'm wrapping the fluent queries in a new collection:

return new Collection( DB::table('tblCustomerUsers')
            ->select('ContractID','ContractNum','ContractName')         
            ->where('CustomerUserID',Auth::User()->CustomerUserID)
            ->where('Restricted','0')

            ->orderBy('ContractTypeID','ASC')

            ->get()
        );  

With various wheres and joins as necessary. This all works splendidly, but as soon as I use an Eloquent method other than get, I get the following error:

Argument 1 passed to Illuminate\\Support\\Collection::__construct() must be of the type         array, object given

It seems to be something to do with the actual structure of what's being returned to the new Collection - get() or take() return an array of objects, which is fine, whereas it seems first(), for example, just returns a single object, hence the error from the Collection. Paginate() obviously returns a Paginator object, which the new Collection also has a tantrum about.

I don't want to have to remove the Collection wrapper, as I then lose the ability to use other Collection methods on the result (I'd rather not use methods like lists() in the query itself as I'm using the same set query result in different ways across the view, so I'm trying to keep it flexible, and want to keep number of queries low)

Perhaps I'm trying to have my cake and eat here, but if not, any help is appreciated!

Cheers!

Answer 1

You can actually use Eloquent itself:

$users = DB::table('tblCustomerUsers')
            ->select('ContractID', 'ContractNum', 'ContractName')         
            ->where('CustomerUserID', Auth::User()->CustomerUserID)
            ->where('Restricted', '0')
            ->orderBy('ContractTypeID', 'ASC')
            ->get();

$users = User::hydrate($users);

$users will now be an Eloquent Collection of Eloquent User models.

---

If you're only getting a single item, use this:

$user = DB::table('tblCustomerUsers')
            ->select('ContractID', 'ContractNum', 'ContractName')         
            ->where('CustomerUserID', Auth::User()->CustomerUserID)
            ->where('Restricted', '0')
            ->orderBy('ContractTypeID', 'ASC')
            ->first();

$user = (new User)->newFromBuilder($user);

$user is now a regular Eloquent User model.