CODEWITHSUNDEEP
sqllaraveleloquent
merdan
merdan

Reputation: 1259

Laravel Convert Mysql query to Eloquent

I'm new to Laravel and I can write simple eloquent queries but have no idea how to convert this query to eloquent. Can anyone give any idea, is it possible to convert this to eloquent or I have to write raw query?

"Select categories.id, categories.name, Sum(likes.liked) as liked 
              FROM categories, likes 
              WHERE likes.material_id IN (SELECT category_material.material_id 
                                          FROM category_material 
                                          WHERE category_material.category_id = categories.id) 
              GROUP BY categories.id";

Here my Models

class Material extends Model
{
    public function categories(){
        return $this->belongsToMany(Category::class,'category_material');
    }
    public function likes(){
        return $this->hasMany(Like::class);
    }

////////////////////////////////////////////////////////////
class Like extends Model
{
    protected $table = 'likes';
    public function user(){
        return $this->belongsTo(User::class);
    }

    public function material(){
        return $this->belongsTo(Material::class);
    }


//////////////////////////////////////////////////////
    class Category extends Model
    {
        public function materials(){
            return $this->belongsToMany(Material::class,'category_material');
        }

Upvotes: 0

Views: 512

Answers (1)

Rwd
Rwd

Reputation: 35170

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.

Upvotes: 2

Related Questions