Laravel eager loading get by count of relationship entries/values with FIND_IN_SET function

Hello,

I tried to get counts with eager loading records with the FIND_IN_SET function.

here is an example using tables:

-- quizzes: id, name, category_ids, created_at, updated_at

-- categories: id, name, created_at, updated_at

quizzes table has the field category_ids as comma-separated like 1,2,3,4. and the categories table has 4 records.

and I want to get a list of categories in that I want to get quizzes count of each category with eager-loading.

So, Is there a more straightforward way to implement this? Thank you a lot!

hupp

3 years ago

Level 11

@lonad79190 Here you can try this :

class Post extends Model
{
    public function tags()
    {
        return $this->belongsToMany(Tag::class);
    }

    public function scopeWithCountAndFindInSet($query, $tags)
    {
        $subQuery = $this->tags()
            ->select(DB::raw('COUNT(*)'))
            ->whereRaw('FIND_IN_SET(tags.id, ?)', [$tags])
            ->getQuery();

        return $query->withCount(['tags' => $subQuery]);
    }
}

you can call above method with calling bellow lines :

$tags = '1,2,3';
$posts = Post::withCountAndFindInSet($tags)->get();

I have used these tables in my case you can replace your tables with quizzes with post & categories with tags

lonad79190

3 years ago

Level 1

@hupp Thanks for the reply,

But, I want to get all categories that have quizzes. no arguments passing.

in which I want is like this, Category::withCount('quizzes'); with the FIND_IN_SET function.

lonad79190

3 years ago

Level 1

Can anyone solve this question?

Please or to participate in this conversation.