Ad

Laravel: How To Order Results Of Related Models?

I have came up with the following query builder and I am getting exact results unless I need to sort the results on the basis of total number of likes and threads to a forum and paginate according.

Below is the query builder without the sortby for popular:

$limit = isset($_GET['limit']) && !empty($_GET['limit']) && is_numeric($_GET['limit']) ? (int) $_GET['limit'] : 10;

$page = isset($_GET['page']) && !empty($_GET['page']) && is_numeric($_GET['page']) ? (int) $_GET['page'] : 1;

$offset = ($page - 1) * $limit;

$forums = Forum::where('status', 1);

if(isset($_GET['search']) && !empty($_GET['search'])) {

    $search = trim($_GET['search']);

    $forums = $forums->where(function($query) use($search) {

        $query->where('title', 'LIKE', '%' . $search . '%')->orWhere('content', 'LIKE', '%' . $search . '%');

    });

}

if(isset($_GET['category']) && !empty($_GET['category'])) {

    $forums = $forums->whereIn('category_id', explode(',', trim($_GET['category'])));

}

if(isset($_GET['sortby']) && !empty($_GET['sortby'])) {

    $sortby = trim($_GET['sortby']);

    if($sortby == 'newest') {

        $forums = $forums->latest();

    } elseif($sortby == 'oldest') {

        $forums = $forums->oldest();

    } elseif($sortby == 'popular') {

        // Sort By: Popular | Logic: Total no of likes and threads to a forum

    } else {

        $forums = $forums->latest();

    }

} else {

    $forums = $forums->latest();

}

$forums = $forums->offset($offset)->limit($limit)->get();

I have defined the eloquent relationship in the Forum model as below:

public function threads()
{

    return $this->hasMany('App\ForumThread', 'forum_id', 'id');

}

public function likes()
{

    return $this->hasMany('App\ForumLike', 'forum_id', 'id');

}

Forum table schema:

CREATE TABLE `forums` (
    `id` bigint(20) UNSIGNED NOT NULL,
    `category_id` int(10) UNSIGNED NOT NULL,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `title` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
    `content` longtext COLLATE utf8mb4_unicode_ci,
    `image` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `tags` text COLLATE utf8mb4_unicode_ci,
    `status` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Forum likes table schema:

CREATE TABLE `forum_likes` (
    `id` bigint(20) UNSIGNED NOT NULL,
    `forum_id` bigint(20) UNSIGNED NOT NULL,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Forum threads table schema is like below:

CREATE TABLE `forum_threads` (
    `id` bigint(20) UNSIGNED NOT NULL,
    `forum_id` bigint(20) UNSIGNED NOT NULL,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Thanks!

Ad

Answer

Counting Related Models
If you want to count the number of results from a relationship without actually loading them you may use the withCount method, which will place a {relation}_count column on your resulting models.

So, you can use withCount() and then order the results by the placed column. For example for 'likes' relation:

} elseif ($sortby == 'popular') {
    $forums = $forums->withCount('likes')->orderBy('likes_count', 'desc');
}
Ad
source: stackoverflow.com
Ad