Sort by value of model method

On your Vote model add the following scope:

public function scopeVotesCount($query){
    return $query->join('votes','votes.post_id','=','posts.id')
        ->selectRaw('posts.*, count(votes.id) as count')->groupBy('votes.id');
}

In your Controller:

public function votes()
{
    $votes = Vote::with('posts')->votesCount()->orderBy('count', 'desc')->get();
}

I haven't tested the above code but it should get you started.

If you return a collection you can sort the collection

// field is here the name of the attribute you added to the post
$posts->sortBy('field');

// Or descending
$posts->sortByDesc('field');

@bobbybouwmann it's not a field that they wish sort by but the returned value of a method which is a perfect use case for a query scope.

@mstnorris I have two kinds of votes, denoted by the type column ("up" or "down"), so I'm not sure that code would be appropriate for my needs.

@freddinator FYI that kind of information would be great in your original post.

@freddinator Okay so you're trying to order by two types of votes? How do you imagine that? Ordering by both at the same time or order by vote difference? Are those votes on the same table or separate ones?

I can't stress enough, like @mstnorris said, that info should have been in your original post.

Solving a issue is one thing, guessing the setup is another. Posting Guidelines

There are two models: Post and Vote. A vote can be an upvote or downvote, this is denoted by the "type" column. There is one table for each. I have a method in the Post model which calculates the Post's score (number of upvotes - number of downvotes), which works. I want to be able to sort posts by their score, but this isn't a column in the database so I don't know how to do that. It will be paginated.

@freddinator why not just store an integer rather than a type 1 for up and -1 for down. Then you can get the total number of votes, and also calculate how many down votes there are just by knowing the number of up votes (1) and the total. No extra table involved, thus keeping the queries simpler.

@mstnorris I need to be able to prevent users from voting multiple times on a Post. Right now I have code in place to see if the user has already voted on that Post to prevent them from voting twice. If they vote differently, it modifies that vote, if they vote the same way, it displays an error message.

@freddinator Okay, let's push this towards a solution.

• Could we please see the Post and Vote models?
• Could we see the structures of the tables involved in all this?

Vote model:

<?php
namespace App;

use Illuminate\Database\Eloquent\Model;

class Vote extends Model
{
    protected $fillable = [
        'type',
        'post_id'
    ];

    public function post()
    {
        return $this->belongsTo('App\Post');
    }

}

Vote migration:

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateVotesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('votes', function (Blueprint $table) {
            $table->increments('id');

            $table->string('type');
            $table->integer('post_id')->unsigned();
            $table->string('ip');

            $table->timestamps();
        });

        Schema::table('votes', function(Blueprint $table) {
           $table->foreign('post_id')->references('id')->on('posts');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('votes');
    }
}

Post model:

<?php
namespace App;

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Model;

class Post extends Model  
{
    protected $fillable = [
        'url',
        'source'
    ];

    public function votes()
    {
        return $this->hasMany('App\Vote');
    }

    public function upVotes()
    {
        return $this->votes()->where('type', 'up')->count();
    }

    public function downVotes()
    {
        return $this->votes()->where('type', 'down')->count();
    }

    public function voteCount()
    {
        return $this->upVotes() - $this->downVotes();
    }

    public function recentUserVote()
    {
        $formatted_date = Carbon::now()->subDays(10)->toDateTimeString();
        return $this->votes()->where('ip', getUserIp())->where('created_at', '>=', $formatted_date)->first();
    }

}

Post migration

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreatePostsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');

            $table->string('url');
            $table->string('source');
            $table->string('ip');

            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('posts');
    }
}

getUserIp() is a function I wrote to get around Cloudflare's proxy, it passes the user's IP as a header, the function checks for the existence of that header, and returns the value if it exists, or if not, returns the user's IP in the normal fashion.

@freddinator All things being said, i have suggestion for you. On the Votes table, add another field for value- It's 1 for up and -1 for down.

Then in the Posts model you can do this:

public function scopeVotesCount($query)
    {
        return $query->join('votes', 'votes.post_id', '=', 'posts.id')
        ->selectRaw('posts.*, sum(votes.value) as total_vote');
    }

And to fetch all posts, based on the total of +1 and -1 you can do

return Post::with('votes')->votesCount()->groupBy('posts.id')->orderBy('total_vote', 'DESC')->get();

Just tried it out locally and works as expected

Thanks a bunch! That worked! Presumably to do the opposite order I just have to replace DESC with ASC?

@freddinator Yes indeed. No worries. Glad to help.