close
IgorArnaut's avatar

Order by and filter by related table attribute with Eloquent

Listing is related to Apartment and Apartment is related to Building.

class Listing extends Model
{
	// ... fillable, casts

	public function apartment(): BelongsTo
    {
        return $this->belongsTo(Apartment::class);
    }
	
	// ... other relations and functions
}
class Building extends Model
{
	protected $fillable = [
        "city",
        "street",
        "floor_count",
        "elevator",
        "parking",
        "garage",
        "cctv",
        "intercom"
    ];
    public $timestamps = false;

	// ... casts

	public function apartments(): HasMany
    {
        return $this->hasMany(Apartment::class);
    }

	// ... other functions
}

I access tables apartments and buildings using with method.

class ListingService
{
    use ListingOrderBy, ListingWhere;

    private const PAGE_SIZE = 2;

    function findAll(string $order)
    {
        $listings = Listing::query()->with(["apartments", "buildings", "users"]);
        return $listings->paginate(self::PAGE_SIZE, pageName: "strana");
    }

	// ...
}

How do I order by and filter listings by its related table columns? For example, I want to filter listings by city or order listings by area.

trait ListingOrderBy
{
    public function orderBy(Builder $listings, string $order)
    {
        return match ($order) {
            "najpopularniji" => $listings->withCount("bookmarks")->orderByDesc("bookmarks_count"),
            "veći" => $listings->orderByDesc("bookmarks_count"), // order by $listing->apartment->area
            "manji" => $listings->orderByDesc("bookmarks_count"), // order by $listing->apartment->area
            "skuplji" => $listings->orderByDesc("price"),
            "jeftiniji" => $listings->orderBy("price"),
            default => $listings->latest()
        };
    }
}
trait ListingWhere
{
    public function whereCityLike(Builder $listings, array $data)
    {
        return $listings->when(isset($data["grad"]), function () use ($listings, $data) {
            return $listings->whereLike([]); // filter by city from $listing->apartment->building
        });
    }
0 likes
5 replies
krisi_gjika's avatar

to order/filter by column of another table you need to make that column available on your query.

Listing::query()
  ->leftJoin('apartments as ap', 'ap.id', 'listings.apartment_id')
  ->leftJoin('buildings as bu', 'bu.id', 'ap.building_id')
  ->where('bu.city', $filters['city'])
  ->orderBy('ap.area');
1 like
ghabriel25's avatar

@igorarnaut You can use something like this

Listing::query()
    ->with(['apartments', 'buildings', 'users'])
    ->whereHas('apartment.building', function (Builder $query) use ($city) {
        $query->whereLike('city', "%{$city}%");
    })
    ->orderBy(
        Apartment::select('area')
            ->whereColumn('apartments.id', 'listings.apartment_id')
    )
    ->get();
Tray2's avatar

Not really, eloquent is just sql in the end, and to order or filter by another table, you need to do a join. Writing joins in eloquent/query builder isn't always pretty, at least not if there are multiple tables involved. One option I like to use is to push the query down into the database, and use a database view, and the use eloquent on that view just as I would a single table. I wrote a blog post on that a few years back, and you can find it here.

https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application

Please or to participate in this conversation.