How to select some field and order by on Querying Relationship Existence? Laravel 5.3

Question

My code is like this :

public function getFavoriteStore($param)
{
    $num = 20;
    $q = $param['q'];
    $location = $param['location'];

    $result = $this->store_repository->whereHas('favorites', function ($query) use($q, $location) {
        $query->select('stores.id', 'stores.name', 'stores.photo','stores.address')
              ->where('stores.status', '=', 1)
              ->where('favorites.favoritable_type', 'like', 'App\\\Models\\\Store');
        if($location)
           $query->where('stores.address', 'like', "%$location%");

        if($q) {
            $query->where('stores.name', 'like', "%$q%")
                  ->where('stores.address', 'like', "%$q%", 'or');
        }
        $query->orderBy('favorites.updated_at', 'desc');
    })->paginate($num);

    return $result;
}

It works

But, order by does not work

Besides, the above query, I only select some field. But when I debug the query, the result display all field

It seems there are still wrong

Is there anyone who can help me?

I follow this tutorial : https://laravel.com/docs/5.3/eloquent-relationships#querying-relationship-existence

UPDATE

My favorite model is like this :

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Favorite extends Model
{
    protected $fillable = ['user_id', 'favoritable_id', 'favoritable_type'];
    public function favoritable()
    {
        return $this->morphTo();
    }
}

My store model is like this :

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Store extends Model
{   
    protected $fillable = ['name', 'address', 'phones', 'address', 'email'];
    public function favorites()
    {
        return $this->morphMany(Favorite::class, 'favoritable');
    }
}

Favorites table has field id, user_id, favoritable_id, favoritable_type

Stores table has field id, name, address, phones, addres, email, photo

The relation between store and favorite are id (stores table) and favoritable_id (favorites table)

I would use Laravel debug bar to look at the actual SQL that's generated and go from there. — JC Lee

Amit Gupta Amit Gupta · Accepted Answer · 2017-02-25T07:33:20

whereHas() is only used to check the existence of a relation so, you have to use join() to order the results with related table

$query = $this->store_repository
    ->join('favorites', function ($join) {
        $join->on('stores.id', '=', 'favorites.favoritable_id')
            ->where('favorites.favoritable_type', 'like', 'App\\\Models\\\Store');
    })
    ->where('stores.status', '=', 1)
    ->select('stores.id', 'stores.name', 'stores.photo','stores.address');

if($location)
    $query->where('stores.address', 'like', "%$location%");

if($q) {
    $query->where('stores.name', 'like', "%$q%")
        ->where('stores.address', 'like', "%$q%", 'or');
}

$result = $query->orderBy('favorites.updated_at', 'desc')->paginate($num);

I have not tested it but I am pretty sure it will work.

How to select some field and order by on Querying Relationship Existence? Laravel 5.3

1 Answers