Executing Raw Queries and Complex Unions in Laravel 5.1
You have a working SQL query involving UNION, LEFT JOIN, and aggregation, but translating it directly into Eloquent Builder syntax triggers an error regarding undefined bindings. The specific error Undefined property: Illuminate\Database\Eloquent\Builder::$bindings usually points to a fundamental mismatch between how the Eloquent Builder expects to chain queries versus how raw SQL fragments are constructed within that chain.
The primary issue in your attempt lies in the second part of the UNION. You are trying to append a query builder instance directly to another without explicitly defining it as a raw string or ensuring the grammar is compatible. Furthermore, mixing complex aggregations like sum() and max() inside an Eloquent chain that also relies on whereNotIn requires careful handling of the database engine's capabilities.
Using DB::statement for Raw Execution
If your goal is to execute a completely raw query without letting Eloquent interfere with the SQL structure, the most robust method in Laravel 5.1 is using the global DB facade. This approach bypasses the query builder's internal state management entirely.
You should wrap your entire SQL string in DB::statement or, more commonly for retrieving results, DB::select. Here is how you can adapt your specific logic:
use Illuminate\Support\Facades\DB;
$query = "
SELECT
cards.id_card, cards.hash_card, cards.table, users.name, 0 as total, cards.card_status, cards.created_at
FROM cards
LEFT JOIN users ON users.id_user = cards.id_user
WHERE hash_card NOT IN (SELECT orders.hash_card FROM orders)
UNION
SELECT
cards.id_card, orders.hash_card, cards.table, users.name,
SUM(orders.quantity * orders.product_price) as total,
cards.card_status, MAX(orders.created_at)
FROM menu.orders
LEFT JOIN cards ON cards.hash_card = orders.hash_card
LEFT JOIN users ON users.id_user = cards.id_user
GROUP BY hash_card
ORDER BY id_card ASC
";
$results = DB::select($query);
// Convert collection to array if needed
$cards = collect($results)->toArray();
This method guarantees that the SQL string matches your MySQL Workbench version exactly. It is particularly useful when dealing with complex UNION statements where the Eloquent builder struggles to maintain column type consistency across the union boundaries.
Correcting the Eloquent Approach
If you prefer to stay within the Eloquent ecosystem, you must construct the two halves of the UNION separately and then combine them using the union() method, ensuring both parts return compatible columns. The error you encountered likely occurred because the second part of the union was not properly isolated before being merged.
Here is a corrected pattern for splitting the logic:
$firstPart = Card::selectRaw('cards.id_card, cards.hash_card, cards.table, users.name, 0 as total, cards.card_status, cards.created_at')
->leftJoin('users', 'users.id_user', '=', 'cards.id_user')
// Note: whereNotIn with a subquery in Eloquent can be tricky.
// It is often safer to use raw SQL for the subquery part or use DB::table().
->whereRaw("hash_card NOT IN (SELECT hash_card FROM orders)");
$secondPart = Order::selectRaw('cards.id_card, orders.hash_card, cards.table, users.name, sum(orders.quantity*orders.product_price) as total, cards.card_status, max(orders.created_at)')
->leftJoin('cards', 'cards.hash_card', '=', 'orders.hash_card')
->leftJoin('users', 'users.id_user', '=', 'cards.id_user')
->groupBy('hash_card');
$unionQuery = $firstPart->union($secondPart)
->orderBy('id_card', 'asc')
->get();
However, you will notice that the whereNotIn clause using a subquery (Order::select(...)) inside the Eloquent builder often fails because the builder does not automatically resolve the nested query into raw SQL in the same way MySQL Workbench does. This is why whereRaw or the global DB facade becomes the preferred solution for this specific pattern.
Trade-offs and Pitfalls
The main trade-off here is between maintainability and flexibility. Using DB::select sacrifices some of Eloquent's model magic (like automatic mass assignment or relationship loading) but gives you precise control over the SQL generation. This is essential when dealing with UNION operations where column names and types must align perfectly across both select statements.
A common mistake developers make is attempting to chain complex subqueries directly into the where() clauses of an Eloquent builder without realizing that the builder generates different syntax than what MySQL expects in certain edge cases. By isolating the raw SQL logic or using DB::table, you ensure that the generated query string remains predictable and debuggable via Laravel's query log (DB::enableQueryLog();).
For complex reporting queries like this, where the logic involves aggregating data from multiple tables and filtering out specific records based on subqueries, sticking to raw SQL via the DB facade is often the most pragmatic path forward.