Illuminate has no grammar for PostgreSQL DISTINCT ON syntax

[jaw-sh]

In my attached code snippet, I am trying to select distinct images (identified by a file_id) and associated information (FK'd on other columns).

group_by doesn't work in MySQL as it does in PostgreSQL because MySQL does not follow standards. Please see this StackOverflow question for more information.

->distinct() doesn't work because if I select [file_id, post_id] so that I can build my relationships, then the distinction implies I only want distinct file and post combinations.

The only way around this, that I can find, is to use DB::raw to custom build my select statement (done in my snippet).

Eloquent should support a ->distinctOn() that can accomplish what this does more easily.

    /**
     * Returns a few posts for the front page.
     *
     * @param  int  $number  How many to pull.
     * @param  boolean $sfwOnly  If we only want SFW boards.
     * @return Collection  of static
     */
    public static function getRecentImages($number = 16, $sfwOnly = true)
    {
        $query = static::where('is_spoiler', false)
            ->whereHas('storage', function($query) {
                $query->where('has_thumbnail', true);
            })
            ->whereHas('post.board', function($query) use ($sfwOnly) {
                $query->where('is_indexed', true);
                $query->where('is_overboard', true);

                if ($sfwOnly)
                {
                    $query->where('is_worksafe', '=', true);
                }
            })
            ->with('storage')
            ->with('post.board')
            ->limit(20);

        if ($query->getQuery()->getConnection() instanceof \Illuminate\Database\PostgresConnection)
        {
            // PostgreSQL does not support the MySQL standards non-compliant group_by syntax.
            // DISTINCT itself selects distinct combinations [attachment_id,file_idd, not just file_id.
            // We have to use raw SQL to accomplish this.
            $query->select(
                \DB::raw("DISTINCT ON (file_id) *")
            );
        }
        else
        {
            $query->orderBy('attachment_id', 'desc');
            $query->groupBy('file_id');
        }

        return $query->get();
    }

[GrahamCampbell]

Thanks for the report, but we don't process feature requests. We are, however, open to PRs.