How to Execute Raw Queries with Laravel 5.1

How to execute raw queries with Laravel 5.1?

I found the solution in this topic and I code this:

$cards = DB::select("SELECT
        cards.id_card,
        cards.hash_card,
        cards.`table`,
        users.name,
        0 as total,
        cards.card_status,
        cards.created_at as last_update
    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) last_update 
    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");

How to run raw SQL query without any expected result in Laravel 6?

did you try DB::statement() ?

$query = 'INSERT INTO product_copys (SELECT '. implode(',', $myColumArray) .' FROM products)';

DB::statement($query);

Building a raw query into the Laravel Query Builder

Your SQL query is very customized and can not be written by eloquent and query builder so you can run it as a raw SQL like this:

$results = DB::select("... your customized SQL query ...");

then you can pass the variable $results to your blade:

return view('reports.secretuserlist',compact('results'));

in the blade, you can access the data via the $results variable

How to write raw query in Laravel

You can get records by using raw query in laravel like:

$sql = 'SELECT table_name "Name_of_the_table", table_rows "Rows Count", round(((data_length + index_length)/1024/1024),2)
"Table Size (MB)" FROM information_schema.TABLES WHERE table_schema = "Name_of_the_Database" AND table_name ="Name_of_the_table"';

$results = DB::select($sql);

How to run normal query in Laravel 7, not using Eloquent

May be you're finding for this solution.
you can using it for resolve your problem.

$yourComplexQuery = "
    Insert Into ..............
    Select..............
    Join....
    Join....
    Where...............
   ...............................................................
";
DB::select($yourComplexQuery);

raw query join left with select to laravel 8 query

there some sql chaining issue. @Ramiz Kongulov forget to split group by clause try this.

$buku = \DB::table('buku')
    ->select(['buku.*','kategory.*', 'tag.*'])
    ->leftJoin('kategori_buku', 'kategori_buku.id', '=', 'buku.id_kategori')
    ->leftJoin('detail_buku_tag', 'detail_buku_tag.id_buku', '=', 'buku.id')
    ->leftJoin(\DB::raw('(SELECT tag_buku.id, GROUP_CONCAT(tag) AS tag FROM tag_buku) AS tag_buku'),
        'tag_buku.id', '=', 'detail_buku_tag.id_tag')
    ->groupBy('buku.id')
    ->get();

How to convert raw subquery into laravel query with query builder?

It took me a while to understand what's going on.

As I understood from your raw query, this is called a nested query.

You can use DB facade to make this query like this:

  
  DB::select('inventory.EmployeeID, inventory.created_date AS OrderDate, SUM(inventory.calculation) AS TotalPrice')
    ->fromSub(function ($nestedQuery) use ($stores) {
      $nesterQuery->select('i.id ...')->from('stationary_orders as o')
        ->leftJoin('stationary_items', 'o.Stationary_ID', '=', 'stationary_items.id')
        ->whereIn('o.Store', $stores)
        ...
    }, 'inventory')
    ->groupBy('inventory.EmployeeID')->get()

---

Related Topics