How to use with no return from a DB :: Select - Laravel

The Jilcimar answer solves for cases where you can query from the Model itself, in my case I wanted to make the combination of Joins first.

To solve this, Laravel has a method called Hydrate , which converts a Query Builder to a Eloquent object.

If, after the process, you still need to do some relationship within the Model, you will need to use ->load() instead of the common ->with() .

In my case, the solution generated this method:

public function pending_orders()
{
    $results = DB::select("

        SELECT orders.*,

        orders.id as orders_id,
        invoices.id as invoice_id,
        invoices.state as invoice_status,

        GROUP_CONCAT(DISTINCT nfes.id  SEPARATOR ',') As nfe_id,
        GROUP_CONCAT(DISTINCT nfes.status  SEPARATOR ',') As nfe_status,

        GROUP_CONCAT(DISTINCT packages.id  SEPARATOR ',') As package_id,
        GROUP_CONCAT(DISTINCT shipments.id  SEPARATOR ',') As shipment_id

        FROM orders

        LEFT JOIN invoices ON orders.id = invoices.order_id
        LEFT JOIN nfes ON invoices.id = nfes.invoice_id
        LEFT JOIN packages ON orders.id = packages.order_id
        LEFT JOIN shipments ON packages.id = shipments.package_id

        WHERE

        orders.deleted_at is NULL AND
        orders.state != 'in_checkout'
        AND (invoices.state != 'paid' OR invoices.state is NULL)
        AND  nfes.invoice_id NOT IN (SELECT invoice_id FROM nfes WHERE status = 'aprovado')

        GROUP BY orders_id, invoice_id, invoice_status
        ORDER BY nfes.id DESC

        ");
    return Order::hydrate($results);
}

And in sequence to simplify additional relationships:

$orders = $this->order->pending_orders()->load('items')->load('lineItems')->load('user')->load('packages')->load('invoice');

Consider that there are no thoughts about good practices in this solution, not even performance analysis.

At the end of the day I decided otherwise, but I believe that the theme can help somebody in some way, since the conversion from Query Builder to Eloquent is something useful to me.

I think you could test something like this

Order::select('seu select aqui')->with('sua relação do model Order')->get();

If it works, take a look at this link: link