Eloquent vs query builder, why?

@Boubou Hey

Well, I find myself using both on the same project. I roll with Eloquent by default since it makes the code much more expressive than using the query builder. However, there are many cases where I find myself needing to write some custom query to achieve something (for example, using the on duplicate update feature of MySQL) that would result in many more queries if I stuck to Eloquent.

I see, thank you for the answer, this will help greatly.

Just a quick question please, when you build your queries in Laravel, do you perform them in the controllers or model as it can be done in both.

Thanks,

@Boubou well, it's gonna be a bit vague, but, it depends.

I try to keep everything super simple. If it's some simple query, I will definitely do that in the controller itself. If I need to some a bit more complex query I might offload that to a method on the model itself and if it is a complex query, I might extract a query object to do that.

For example, I have built some kind of an online shopping site for a customer, and for that very specific project, the user's cart had to be saved between sessions and never be deleted unless the user specifically emptied the cart or made an order. In order to achieve that, the cart is stored in the database for the user. Because the cart is stored in the database, its structure is very similar to what an order may look like. To me, it did not make sense to run a query for each cart item and "move" it to the orders table which is the easiest solution if you were using Eloquent to do that. Instead, I've written the following query:

DB::insert(
            "insert into order_product(order_id, product_id, quantity, price, created_at, updated_at)
            select '{$order->id}' as order_id, c.product_id, c.quantity, p.price, '{$order->created_at}' as created_at, '{$order->updated_at}' as updated_at
            from cart as c
            join price_list_product as p on p.product_id = c.product_id and p.price_list_id = '{$user->price_list_id}'
            where c.user_id={$user->id}"
        );

The query is not overly complicated but it felt a little bit disgusting keeping that query the controller so I simply extracted that query to a method on the model.

Eloquent is active record, which is a shortcut language, in the background it's converted to normal sql / pdo. In fact the following is what it boils down to:

    public function run($sql, $args = NULL)
    {
        if (!$args)
        {
             return $this->query($sql);
        }
        $stmt = $this->prepare($sql);
        $stmt->execute($args);
        return $stmt;
    }

Where $args is the bindings array.

Taken from another framework that uses just straight pdo, but in the background eloquent is also doing just that.

Oh some queries can not be done in active record.

@kfirba thank you for the reply. There is something I find strange in your query, I am used to mysqli prepared statements, perhaps I am wrong..., in

 insert into order_product(order_id, product_id, quantity, price, created_at, updated_at)

Is there not a missing

VALUES (1, 2, 3, 4, 5, 6)

Then from "insert" the query goes to "from", this should be select no?

@jlrdw thanks for the reply. I understand what you mean.

@Boubou I'm not doing a regular insert, I'm doing an insert select query. Basically, it will take a table structure and insert it. You can read more about it here: https://dev.mysql.com/doc/refman/5.7/en/insert-select.html

@kfirba Thanks! I did not know about those!

Someone posted a performance comparison of Eloquent vs query builder. Query builder is much faster.

https://www.youtube.com/watch?v=3TJfR1Ta4GU