How to use with on the return of a DB::Select - Laravel

Question

To get the information, is being using a DB::RAW thus:

That’s in the Repository. Already in Controller, I would like to use the with to bring the relationships made in the Model:

But the DB::select returns by default a array, with the consultation made.

This can be used at the same time?

Answer 1

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

To solve this, the Laravel has a method called Hydrate, that converts a Query Builder for an object Eloquent.

If, after the process, you still need to do some relationship inside the Model, you will need to use the ->load()instead of the ordinary ->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 the sequence to simplify further relationship:

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

Consider that there are no thoughts on best practices in this solution, nor even performance analysis.

In the end I resolved otherwise, however, I believe that the theme can help someone in some way, because the conversion of Query Builder to the Eloquent is something useful to me.

Answer 2

I think you could test something like this

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

It might work, anything take a look at this link: https://stackoverflow.com/questions/43757062/laravel-raw-query-and-including-model-relations