Order based on similarities

// Post m:n Category

$post = Post::find(1);

Post::whereHas('categories', function($q) {
	$q->whereIn('categories.id', $post->categories->pluck('id')->toArray())
		->where('posts.id', '<>', $post->id);
})->get();

Currently it's ordered by id.

For example

The categories of Post#1 are id [2,3] and the second query gives

[
	// Post id: 2 that has category id:
	[1,2,3],

	// Post id: 4 that has category id:
	[2,5,4],

	// Post id: 8 that has category id:
	[3,2,6]
]

But I want to order them by how many categories it matched

So it should look like this

[
	// Post id: 2 that has category id:
	[1,2,3],

	// Post id: 8 that has category id:
	[3,2,6]

	// Post id: 4 that has category id:
	[2,5,4],
]

Shivamyadav

4 months ago

Level 20

I believe you need to use the group by categories.

$post = Post::with('categories')->findOrFail(1);

$categoryIds = $post->categories->pluck('id');

$relatedPosts = Post::query()
    ->where('posts.id', '<>', $post->id)
    ->whereHas('categories', function ($q) use ($categoryIds) {
        $q->whereIn('categories.id', $categoryIds);
    })
    ->withCount([
        'categories as matched_categories_count' => function ($q) use ($categoryIds) {
            $q->whereIn('categories.id', $categoryIds);
        }
    ])
    ->orderByDesc('matched_categories_count')
    ->get();

1 like

Please or to participate in this conversation.