Laravel Convert Query MySQL to Model Query

Laravel Convert Mysql query to Eloquent

You can define a likes relationship in your Category model like so:

public function likes()
{
    return $this->belongsToMany(Like::class, 'category_material', 'category_id', 'material_id', 'id', 'material_id');
}

Then to achieve what you're after with Eloquent you can use a mixture of has() and withCount, however, we're going to modify the withCount call to return a sum() instead:

$catrgories =  Category::has('likes')->withCount([
    'likes as liked' => function ($query) {
        $query->select(DB::raw('SUM(likes.liked)'));
    },
])->get();

---

If you're wanting to return categories that don't have any likes you can remove the has() method, and introduce the COALESCE() function to your raw query:

$catrgories =  Category::withCount([
    'likes as liked' => function ($query) {
        $query->select(DB::raw('COALESCE(SUM(likes.liked), 0)'));
    },
])->get();

---

Alternatively, you could simply load the necessary relationships and then use that fact that Eloquent returns collection to get the value after you've retrieved the results from the database:

$categories = Category::with('materials.likes')->get()->map(function ($item) {

    $item->setAttribute('liked', $item->materials->map(function ($item) {
        return $item->likes->map->sum('liked')->sum();
    })->first());

    $item->unsetRelation('materials');

    return $item;
});

This would mean that you don't have to add the custom relationship.

Laravel convert query mysql to model query

Uptime::whereOnline(0)
    ->groupBy('server_id')
    ->select('server_id', DB::raw('count(*) as status'))
    ->get();

whereOnline is shortcut from where('online', 0)

How to convert this raw sql query to laravel eloquent or query builder?

Try in this way.
For more details Check: https://laravel.com/docs/8.x/queries

$query  = DB::table('employees as emp')
    ->leftjoin('attendance_log as al','al.emp_id','emp.device_emp_id')
    ->join('departments as dep','dep.id','emp.department_id')
    ->select('emp.*', 'dep.department_name', 'al.auth_date' , DB::raw("MIN(al.auth_time) AS check_in, MAX(al.auth_time) AS check_out"))
    ->whereIn('al.emp_id', [1,2,3]) // use your array
    ->groupBy('emp.device_emp_id', DB::raw('DATE(al.auth_date_time)'));

    if($start_date){
        $query  = $query->where('al.auth_date' , '>=' , $start_date);
    }
    if($end_date){
        $query  = $query->where('al.auth_date' , '<=' , $end_date);
    }
  $data = $query->get();

Convert MySQL Query Into Laravel Eloquent

You can't access the propertys because ->get() returns a collection of objects. For this to work you only need one object of your Eloquent Model.

If you only ever need one result you can replace ->get() with ->first() to directly receive the model object.

How to convert query to Laravel Eloquent ORM

I would use the following raw MySQL query, which does not require any subquery:

SELECT product_id, COUNT(*) AS matches 
FROM product
WHERE filter_id IN (2, 4)
GROUP BY product_id
HAVING matches = 2;

Your updated Laravel/Eloquent code:

OcProductFilter::with('product')
    ->select('product_id', DB::raw('COUNT(*) matches'))
    ->groupBy('product_id')
    ->whereIn('filter_id', [2, 4])
    ->havingRaw('matches = ?', [2])
    ->get();

How to write a where not in query in laravel?

If you want to use Eloquent, it'll be like below

Eloquent model

<?php

namespace App\Modules\Vehicle\Models;

use Illuminate\Database\Eloquent\Model;

class ApiAccessAuth extends Model
{
    protected $table = 'apiaccessauth';
}

Your query

ApiAccessAuth::whereNotIn('site_name', function ($query) {
    $query->from('API_site_access_log')
        ->select('site_name')
        ->whereDate('created_at', '2021-10-15');
})->get();

If you want to use query builder, it'll be like below

use Illuminate\Support\Facades\DB;

DB::table('apiaccessauth')
    ->whereNotIn('site_name', function ($query) {
        $query->from('API_site_access_log')
            ->select('site_name')
            ->whereDate('created_at', '2021-10-15');
    })->get();

How to convert this sql query into laravel query builder?

I had come out with the Laravel SQL to get the duration sum group by each itemregistrationID:

   if(request('tempoh_negeri')) {
            $query->select(DB::raw('m.itemRegistrationID, m.NegeriID, sum(distinct m.duration)'))
                  ->from(DB::raw('(SELECT itemRegistrationID, NegeriID, yeartamatkhidmat - yearmulakhidmat as duration FROM itemregistrationpangkat) AS m 
                        RIGHT JOIN itemregistrations ON itemregistrations.itemRegistrationID=m.itemRegistrationID'))
                  ->groupBy('m.itemRegistrationID') 
                  ->havingRaw('sum(m.duration) >= ?', [request('tempoh_negeri')]);
  }

---

Related Topics