Laravel Convert Mysql query to Eloquent

Question

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');
        }

What have you tried so far? Was there something specific that you had trouble with when trying to convert it yourself? — Rwd
– Rwd, Commented Jul 22, 2019 at 7:12
I think you need to try it yourself first then if you have a error or stuck on something then you can ask it here — Jovs
– Jovs, Commented Jul 22, 2019 at 7:15
Do you have models set up for these tables and do you have relationships set up between them? — Rwd
– Rwd, Commented Jul 22, 2019 at 7:18

Rwd · Accepted Answer · 2019-07-22 11:22:38Z

2

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.

edited Jul 22, 2019 at 11:22

answered Jul 22, 2019 at 10:13

Rwd

35.3k7 gold badges71 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

merdan Over a year ago

But i dont have Category and Like relation

merdan Over a year ago

this query is giving BadMethodCallException "Call to undefined method App\Models\Category::likes()"

Rwd Over a year ago

@merdan Sorry about that, I'd thought I'd seen that relationship. One sec, there are a couple of options that I just want to test.

Rwd Over a year ago

@merdan I have added an example likes() relationship to the top of my answer.

Rwd Over a year ago

@merdan I have also added an alternative to the bottom of my answer as well.

Collectives™ on Stack Overflow

Laravel Convert Mysql query to Eloquent

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related