PHPackages                             humweb/laravel-eloquent-join - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. humweb/laravel-eloquent-join

ActiveLibrary[Database & ORM](/categories/database)

humweb/laravel-eloquent-join
============================

This package introduces the join magic for eloquent models and relations.

4.1.3(5y ago)04.5k↓50%1MITPHP

Since Dec 17Pushed 9mo agoCompare

[ Source](https://github.com/humweb/laravel-eloquent-join)[ Packagist](https://packagist.org/packages/humweb/laravel-eloquent-join)[ Docs](https://github.com/fico7489/laravel-eloquent-join)[ RSS](/packages/humweb-laravel-eloquent-join/feed)WikiDiscussions master Synced 1mo ago

READMEChangelogDependencies (4)Versions (44)Used By (0)

[![Tests status](https://github.com/fico7489/laravel-eloquent-join/workflows/Test/badge.svg)](https://github.com/fico7489/laravel-eloquent-join/workflows/Test/badge.svg)

Laravel Eloquent Join
=====================

[](#laravel-eloquent-join)

This package introduces the join magic for eloquent models and relations.

Introduction
------------

[](#introduction)

Eloquent is a powerful ORM but its join capabilities are very poor.

#### First Eloquent Problem (sorting)

[](#first-eloquent-problem-sorting)

With laravel you can't perform sorting of the relationship fields without manually joining related table which is very awkward. Let me give you a few reasons why. If you have a table with **posts** and related **categories** your code might look like this:

```
$posts = Post::select('posts.*')
    ->join('categories', 'categories.id', '=', 'posts.category_id')
    ->groupBy('posts.id')
    ->where('categories.deleted_at', '=', null)
    ->orderBy('categories.name');

if(request()->get('date')){
    $posts->where('posts.date', $date)
}

$posts = $posts->get();

```

1.The first problem is that you need to worry about select.

```
    ->select('posts.*')

```

Reason : without **select** id from the category can be selected and hydrated into the Post model.

2.The second problem is that you need to worry about **groupBy**.

```
->groupBy('posts.id');

```

Reason : if the relation is HasOne and there are more than one categories for the post, the query will return more rows for categories.

3.The third problem is that you need to change all other where clauses from :

```
    ->where('date', $date)

```

to

```
    ->where('posts.date', $date)

```

Reason : a **post** and **category** can have "date" attribute and in that case without selecting an attribute with table "ambiguous column" error will be thrown.

4.The fourth problem is that you are using table names(not models) and this is also bad and awkward.

```
    ->where('posts.date', $date)

```

5.The fifth problem is that you need to worry about soft deletes for joined tables. If the **category** is using SoftDeletes trait you must add :

```
    ->where('categories.deleted_at', '=', null)

```

This package will take care of all above problems for you. Unlike **sorting**, you can perform **filtering** on the relationship fields without joining related tables, but this package will give you the ability to do this easier.

#### Second Eloquent Problem (subqueries)

[](#second-eloquent-problem-subqueries)

With laravel you can perform where on the relationship attribute but laravel will generate subqueries which are more slower than joins. With this package you will be available to perform where on the relationship with joins in an elegant way.

Requirements
------------

[](#requirements)

Laravel VersionPackage TagSupportedDevelopment Branch>= 5.5.04.\*yesmaster< 5.5.0-no-Package is also tested for SQLite, MySql and PostgreSql

Installation & setup
------------------------

[](#installation--setup)

1.Install package with composer

```
composer require fico7489/laravel-eloquent-join

```

With this statement, a composer will install highest available package version for your current laravel version.

2.Use Fico7489\\Laravel\\EloquentJoin\\Traits\\EloquentJoinTrait trait in your base model or only in particular models.

```
...
use Fico7489\Laravel\EloquentJoin\Traits\EloquentJoin;
use Illuminate\Database\Eloquent\Model;

abstract class BaseModel extends Model
{
    use EloquentJoin;
...

```

3.IMPORTANT

For **MySql** make sure that **strict** configuration is set to **false**

config/database.php

```
        'mysql' => [
			...
            'strict'    => false,
			...

```

and that's it, you are ready to go.

Options
-------

[](#options)

Options can be set in the model :

```
class Seller extends BaseModel
{
    protected $useTableAlias = false;
    protected $appendRelationsCount = false;
    protected $leftJoin = false;
    protected $aggregateMethod = 'MAX';

```

or on query :

```
    Order::setUseTableAlias(true)->get();
    Order::setAppendRelationsCount(true)->get();
    Order::setLeftJoin(true)->get();
    Order::setAggregateMethod(true)->get();

```

#### **useTableAlias**

[](#usetablealias)

Should we use an alias for joined tables (default = false)

With **true** query will look like this :

```
select "sellers".* from "sellers"
    left join "locations" as "5b5c093d2e00f"
	...

```

With **false** query will look like this :

```
select "sellers".*
	from "sellers"
	left join "locations"
	...

```

Alias is a randomly generated string.

#### **appendRelationsCount**

[](#appendrelationscount)

Should we automatically append relation count field to results (default = false)

With **true** query will look like this :

```
select "sellers".*, count(locations.id) AS locations_count
	from "sellers"
	left join "locations" as "5b5c093d2e00f"
	...

```

Each **relation** is glued with an underscore and at the end **\_count** prefix is added. For example for

```
->joinRelations('seller.locations')

```

field would be **seller\_locations\_count**

#### **leftJoin**

[](#leftjoin)

Should we use **inner join** or **left join** (default = true)

```
select "sellers".*
	from "sellers"
	inner join "locations"
	...

```

vs

```
select "sellers".*
	from "sellers"
	left join "locations"
	...

```

#### **aggregateMethod**

[](#aggregatemethod)

Which aggregate method to use for ordering (default = 'MAX').

When join is performed on the joined table we must apply aggregate functions on the sorted field so we could perform group by clause and prevent duplication of results.

```
select "sellers".*, MAX("locations" ."number") AS sort
	from "sellers"
	left join "locations"
	group by "locations" ."id"
	order by sort
	...

```

Options are : **SUM**, **AVG**, **MAX**, **MIN**, **COUNT**

Usage
-----

[](#usage)

### Currently available relations for join queries

[](#currently-available-relations-for-join-queries)

- **BelongsTo**
- **HasOne**
- **HasMany**

### New clauses for eloquent builder on BelongsTo and HasOne relations :

[](#new-clauses-for-eloquent-builder-on-belongsto-and-hasone-relations-)

**joinRelations($relations, $leftJoin = null)**

- ***$relations*** which relations to join
- ***$leftJoin*** use **left join** or **inner join**, default **left join**

**orderByJoin($column, $direction = 'asc', $aggregateMethod = null)**

- ***$column*** and ***$direction*** arguments are the same as in default eloquent **orderBy()**
- ***$aggregateMethod*** argument defines which aggregate method to use ( **SUM**, **AVG**, **MAX**, **MIN**, **COUNT**), default **MAX**

**whereJoin($column, $operator, $value, $boolean = 'and')**

- arguments are the same as in default eloquent **where()**

**orWhereJoin($column, $operator, $value)**

- arguments are the same as in default eloquent **orWhere()**

**whereInJoin($column, $values, $boolean = 'and', $not = false)**

- arguments are the same as in default eloquent **whereIn()**

**whereNotInJoin($column, $values, $boolean = 'and')**

- arguments are the same as in default eloquent **whereNotIn()**

**orWhereInJoin($column, $values)**

- arguments are the same as in default eloquent **orWhereIn()**

**orWhereNotInJoin($column, $values)**

- arguments are the same as in default eloquent **orWhereNotIn()**

### Allowed clauses on BelongsTo, HasOne and HasMany relations on which you can use join clauses on the query

[](#allowed-clauses-on-belongsto-hasone-and-hasmany-relations-on-which-you-can-use-join-clauses-on-the-query)

- Relations that you want to use for join queries can only have these clauses : **where**, **orWhere**, **withTrashed**, **onlyTrashed**, **withoutTrashed**.
- Clauses **where** and **orWhere** can only have these variations \*\* **->where($column, $operator, $value)**\*\* **->where(\[$column => $value\])**
- Closures are not allowed.
- Other clauses like **whereHas**, **orderBy** etc. are not allowed.
- You can add not allowed clauses on relations and use them in the normal eloquent way, but in these cases, you can't use those relations for join queries.

Allowed relation:

```
public function locationPrimary()
{
    return $this->hasOne(Location::class)
        ->where('is_primary', '=', 1)
        ->orWhere('is_primary', '=', 1)
        ->withTrashed();
}

```

Not allowed relation:

```
public function locationPrimary()
{
    return $this->hasOne(Location::class)
        ->where('is_primary', '=', 1)
        ->orWhere('is_primary', '=', 1)
        ->withTrashed()
        ->whereHas('state', function($query){return $query;}
        ->orderBy('name')
        ->where(function($query){
            return $query->where('is_primary', '=', 1);
        });
}

```

The reason why the second relation is not allowed is that this package should apply all those clauses on the join clause, eloquent use all those clauses isolated with subqueries NOT on join clause and that is more simpler to do.

You might get a picture that there are too many rules and restriction, but it is really not like that. Don't worry, if you do create the query that is not allowed appropriate exception will be thrown and you will know what happened.

### Other

[](#other)

- If the model uses the SoftDelete trait, where deleted\_at != null will be automatically applied
- You can combine new clauses unlimited times
- If you combine clauses more times on same relation package will join related table only once

```
Seller::whereJoin('city.title', '=', 'test')
    ->orWhereJoin('city.title', '=', 'test2');

```

- You can call new clauses inside closures

```
Seller::where(function ($query) {
    $query
        ->whereJoin('city.title', '=', 'test')
        ->orWhereJoin('city.title', '=', 'test2');
});

```

- You can combine join clauses e.g. whereJoin() with eloquent clauses e.g. orderBy()

```
Seller::whereJoin('title', '=', 'test')
    ->whereJoin('city.title', '=', 'test')
    ->orderByJoin('city.title')
    ->get();

```

See action on real example
--------------------------

[](#see-action-on-real-example)

Database schema :

[![Database schema](https://raw.githubusercontent.com/fico7489/laravel-eloquent-join/master/readme/era.png)](https://raw.githubusercontent.com/fico7489/laravel-eloquent-join/master/readme/era.png)

Models :

```
class Seller extends BaseModel
{
    public function locations()
    {
        return $this->hasMany(Location::class);
    }

    public function locationPrimary()
    {
        return $this->hasOne(Location::class)
            ->where('is_primary', '=', 1);
    }

    public function city()
    {
        return $this->belongsTo(City::class);
    }

```

```
class Location extends BaseModel
{
    public function locationAddressPrimary()
    {
        return $this->hasOne(LocationAddress::class)
            ->where('is_primary', '=', 1);
    }

```

```
class City extends BaseModel
{
    public function state()
    {
        return $this->belongsTo(State::class);
    }
}

```

### Join

[](#join)

##### Join BelongsTo

[](#join-belongsto)

`Seller::joinRelations('city')`

##### Join HasOne

[](#join-hasone)

`Seller::joinRelations('locationPrimary')`

##### Join HasMany

[](#join-hasmany)

`Seller::joinRelations('locations')`

##### Join Mixed

[](#join-mixed)

`Seller::joinRelations('city.state')`

### Join (mix left join)

[](#join-mix-left-join)

`Seller::joinRelations('city', true)->joinRelations('city.state', false)`

### Join (multiple relationships)

[](#join-multiple-relationships)

`Seller::join(['city.state', 'locations'])`

### Ordering

[](#ordering)

##### Order BelongsTo

[](#order-belongsto)

`Seller::orderByJoin('city.title')`

##### Order HasOne

[](#order-hasone)

`Seller::orderByJoin('locationPrimary.address')`

##### Order HasMany

[](#order-hasmany)

`Seller::orderByJoin('locations.title')`

##### Order Mixed

[](#order-mixed)

`Seller::orderByJoin('city.state.title')`

### Ordering (special cases with aggregate functions)

[](#ordering-special-cases-with-aggregate-functions)

##### Order by relation count

[](#order-by-relation-count)

`Seller::orderByJoin('locations.id', 'asc', 'COUNT')`

##### Order by relation field SUM

[](#order-by-relation-field-sum)

`Seller::orderByJoin('locations.is_primary', 'asc', 'SUM')`

##### Order by relation field AVG

[](#order-by-relation-field-avg)

`Seller::orderByJoin('locations.is_primary', 'asc', 'AVG')`

##### Order by relation field MAX

[](#order-by-relation-field-max)

`Seller::orderByJoin('locations.is_primary', 'asc', 'MAX')`

##### Order by relation field MIN

[](#order-by-relation-field-min)

`Seller::orderByJoin('locations.is_primary', 'asc', 'MIN')`

### Filtering (where or orWhere)

[](#filtering-where-or-orwhere)

##### Filter BelongsTo

[](#filter-belongsto)

`Seller::whereJoin('city.title', '=', 'test')`

##### Filter HasOne

[](#filter-hasone)

`Seller::whereJoin('locationPrimary.address', '=', 'test')`

##### Filter HasMany

[](#filter-hasmany)

`Seller::whereJoin('locations.title', '=', 'test')`

##### Filter Mixed

[](#filter-mixed)

`Seller::whereJoin('city.state.title', '=', 'test')`

### Relation count

[](#relation-count)

```
$sellers = Seller::setAppendRelationsCount(true)->join('locations', '=', 'test')
    ->get();

foreach ($sellers as $seller){
    echo 'Number of location = ' . $seller->locations_count;
}

```

### Filter (mix left join)

[](#filter-mix-left-join)

```
Seller::joinRelations('city', true)
    ->joinRelations('city.state', false)
    ->whereJoin('city.id', '=', 1)
    ->orWhereJoin('city.state.id', '=', 1)

```

Generated queries
-----------------

[](#generated-queries)

Query :

```
Order::whereJoin('seller.id', '=', 1)->get();

```

Sql :

```
select "orders".*
    from "orders"
    left join "sellers" on "sellers"."id" = "orders"."seller_id"
    where "sellers"."id" = ?
    and "orders"."deleted_at" is null
    group by "orders"."id"

```

Query :

```
Order::orderByJoin('seller.id', '=', 1)->get();

```

Sql :

```
select "orders".*, MAX(sellers.id) as sort
    from "orders"
    left join "sellers" on "sellers"."id" = "orders"."seller_id"
    where "orders"."deleted_at" is null
    group by "orders"."id"
    order by sort asc

```

Elegance of package
-------------------

[](#elegance-of-package)

Lets look how first example from documentation now looks like. This code :

```
$posts = Post::select('posts.*')
    ->join('categories', 'categories.id', '=', 'posts.category_id')
    ->groupBy('posts.id')
    ->where('categories.deleted_at', '=', null)
    ->orderBy('categories.name');

if(request()->get('date')){
    $posts->where('date', $date)
}

$posts = $posts->get();

```

is now :

```
$posts = Post::orderByJoin('category.name');

if(request()->get('date')){
    $posts->where('posts.date', $date)
}

$posts = $posts->get();

```

Both snippets do the same thing.

Tests
-----

[](#tests)

This package is well covered with tests. If you want run tests just run **composer update** and then run tests with **"vendor/bin/phpunit"**

Contribution
------------

[](#contribution)

Feel free to create new issue for :

- bug
- notice
- request new feature
- question
- clarification
- etc...

License
-------

[](#license)

MIT

**Free Software, Hell Yeah!**

###  Health Score

42

—

FairBetter than 90% of packages

Maintenance41

Moderate activity, may be stable

Popularity23

Limited adoption so far

Community16

Small or concentrated contributor base

Maturity76

Established project with proven stability

 Bus Factor1

Top contributor holds 87.4% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~26 days

Recently: every ~87 days

Total

39

Last Release

2071d ago

Major Versions

1.2.2 → 2.1.32018-02-23

1.2.3 → 2.1.42018-02-23

1.2.x-dev → 2.2.42018-02-23

2.2.4 → 3.0.02018-08-26

3.0.2 → 4.0.02018-10-31

### Community

Maintainers

![](https://www.gravatar.com/avatar/8b536bae26e43306745d195a34b8dff510bc05419cdc3503f96c5cb3fd4e7239?d=identicon)[ryun](/maintainers/ryun)

---

Top Contributors

[![fico7489](https://avatars.githubusercontent.com/u/4559663?v=4)](https://github.com/fico7489 "fico7489 (187 commits)")[![deanmalan](https://avatars.githubusercontent.com/u/4767109?v=4)](https://github.com/deanmalan "deanmalan (6 commits)")[![ryun](https://avatars.githubusercontent.com/u/227672?v=4)](https://github.com/ryun "ryun (5 commits)")[![fjrid](https://avatars.githubusercontent.com/u/26612244?v=4)](https://github.com/fjrid "fjrid (4 commits)")[![xavadu](https://avatars.githubusercontent.com/u/1382820?v=4)](https://github.com/xavadu "xavadu (3 commits)")[![kennyturner](https://avatars.githubusercontent.com/u/59434835?v=4)](https://github.com/kennyturner "kennyturner (2 commits)")[![davidjosephhayes](https://avatars.githubusercontent.com/u/9373380?v=4)](https://github.com/davidjosephhayes "davidjosephhayes (2 commits)")[![lloy0076](https://avatars.githubusercontent.com/u/1174532?v=4)](https://github.com/lloy0076 "lloy0076 (1 commits)")[![bryce13950](https://avatars.githubusercontent.com/u/6487928?v=4)](https://github.com/bryce13950 "bryce13950 (1 commits)")[![longlevan9x](https://avatars.githubusercontent.com/u/25762133?v=4)](https://github.com/longlevan9x "longlevan9x (1 commits)")[![mikebronner](https://avatars.githubusercontent.com/u/1791050?v=4)](https://github.com/mikebronner "mikebronner (1 commits)")[![Sharptsa](https://avatars.githubusercontent.com/u/4447875?v=4)](https://github.com/Sharptsa "Sharptsa (1 commits)")

---

Tags

laravel eloquent joinlaravel sort joinlaravel where joinlaravel join relationlaravel join

###  Code Quality

TestsPHPUnit

Code StylePHP CS Fixer

### Embed Badge

![Health badge](/badges/humweb-laravel-eloquent-join/health.svg)

```
[![Health](https://phpackages.com/badges/humweb-laravel-eloquent-join/health.svg)](https://phpackages.com/packages/humweb-laravel-eloquent-join)
```

###  Alternatives

[fico7489/laravel-eloquent-join

This package introduces the join magic for eloquent models and relations.

338475.6k6](/packages/fico7489-laravel-eloquent-join)[owen-it/laravel-auditing

Audit changes of your Eloquent models in Laravel

3.4k33.0M95](/packages/owen-it-laravel-auditing)[staudenmeir/eloquent-json-relations

Laravel Eloquent relationships with JSON keys

1.1k5.8M24](/packages/staudenmeir-eloquent-json-relations)[gearbox-solutions/eloquent-filemaker

A package for getting FileMaker records as Eloquent models in Laravel

6454.8k2](/packages/gearbox-solutions-eloquent-filemaker)[cybercog/laravel-ownership

Laravel Ownership simplify management of Eloquent model's owner.

9126.6k3](/packages/cybercog-laravel-ownership)[io238/laravel-iso-countries

Ready-to-use Laravel models and relations for country (ISO 3166), language (ISO 639-1), and currency (ISO 4217) information with multi-language support.

5462.3k](/packages/io238-laravel-iso-countries)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)