Support the ongoing development of Laravel.io →
posted 8 years ago
Eloquent
Last updated 5 months ago.
0

Bumping this, there were 6 people looking to do this on the old thread so now that makes 8 clueless people.

In my case, I'd like to find relating products, by finding products in the same category. A product can have many categories.

Ideally it'd be something like:

return $this->belongsToManyThrough('Category', 'Product');

For now, what I'm doing is looping through the categories, and then get each category's products and add the product to a master array, then convert it all to a collection. Like so:

<?php

public function relatedProducts()
{
	$products = array();

	foreach ($this->categories as $category)
	{
		foreach ($category->products as $product)
		{
			if ($product->id !== $this->id && !isset($products[$product->id]))
			{
				$products[$product->id] = $product;
			}
		}
	}

	return \Illuminate\Database\Eloquent\Collection::make($products);
}

(this is a function on the product model)

Please, someone tell if there's a better way.

Last updated 5 months ago.
0

Make that 9 people... I'm looking to make this work in one of my current projects if it possible!?

Last updated 5 months ago.
0

Make it 10.

Last updated 5 months ago.
0

Hi all.

I spent an hour looking into this. There is a bug in the join query creation.

Ex. class Organization extends Eloquent { public function users() { return $this->hasManyThrough('User', 'Link', 'organization_id', 'user_id'); } } ... $posts = $organization->users()->get(); ...

Currently translates into: select users.*, links.organization_id from users inner join links on links.id = users.user_id where users.deleted_at is null and links.organization_id = 1

Should be: select users.*, links.organization_id from users inner join links on links.user_id = users.id where users.deleted_at is null and links.organization_id = 1

Suggestion: For now, use the regular join query methods to mimic above query. Example: class Organization extends Eloquent { public function users() { return DB::table('users') ->join('links', 'users.id', '=', 'links.user_id') ->select('users.*', 'links.organization_id') ->where('links.organization_id', '=', $this->id); } }

Happy coding!

Last updated 5 months ago.
0

I use this approach (placed in base model):

    public function manyThroughMany($related, $through, $firstKey, $secondKey, $pivotKey)
    {
        $model = new $related;
        $table = $model->getTable();
        $throughModel = new $through;
        $pivot = $throughModel->getTable();

        return $model
            ->join($pivot, $pivot . '.' . $pivotKey, '=', $table . '.' . $secondKey)
            ->select($table . '.*')
            ->where($pivot . '.' . $firstKey, '=', $this->id);
    }

Usage is the same as with hasManyThrough, but with all parameters required and additional pivotKey param:

public function topics()
    {
        return $this->manyThroughMany('Academy\Models\Topic', 'Academy\Models\PeriodTopic', 'period_id', 'id', 'topic_id');
}

In my case I connect three models via pivot and this pivot has it's own model. For "classic" manyToMany (without pivot model) this code may by modified slightly but you've got the idea

Last updated 5 months ago.
0

(bump) Make it 11...

Last updated 5 months ago.
0

I attach a join after the eloquent syntax to attach data for a secondarily related model. If you only want data from that model (table), and not the intermediary model (table), specify with the select syntax.

This function grabs the user through "Sales_orders".


    public function user()
    {
     
        return $this->hasOne('Sales_orders','id','order_id')

            ->join('users', 'sales_orders.user_id', '=', 'users.id')

            ->select('users.*');
         
    }

The select statement tells it to include all fields from the users table, none from the sales_orders table.

This function grabs product information for each item that is attached to an order. Many-to-Many-Through-Many.


 public function products()
    {
     
        return $this->hasMany('Sales_order_items', 'sales_order_id')

         ->leftJoin('catalog_products', 'sales_order_items.catalog_product_id', '=', 'catalog_products.id')
        
         ->select('catalog_products.*');
                
    }

Last updated 5 months ago.
0

@terion-name

In my case I connect three models via pivot and this pivot has it's own model. For "classic" manyToMany (without pivot model) this code may by modified slightly but you've got the idea

How would you modify this to use a "classic" manyToMany (without pivot model)?

0

Another :/

0

Make It++

0

One thing === geez

0

andrewsuzuki said:

Bumping this, there were 6 people looking to do this on the old thread so now that makes 8 clueless people.

In my case, I'd like to find relating products, by finding products in the same category. A product can have many categories.

Ideally it'd be something like:

return $this->belongsToManyThrough('Category', 'Product');

For now, what I'm doing is looping through the categories, and then get each category's products and add the product to a master array, then convert it all to a collection. Like so:

<?php

public function relatedProducts()
{
  $products = array();

  foreach ($this->categories as $category)
  {
  	foreach ($category->products as $product)
  	{
  		if ($product->id !== $this->id && !isset($products[$product->id]))
  		{
  			$products[$product->id] = $product;
  		}
  	}
  }

  return \Illuminate\Database\Eloquent\Collection::make($products);
}

(this is a function on the product model)

Please, someone tell if there's a better way.

Could clean it up a little with

function relatedProducts()
	{
		return static::where('id', '<>', $this->id)
			->whereHas('categories', function($q){
				$q->whereIn('id',$this->categories()->lists('id'));
			})->get();
	}
0

(bump) +1, That makes 16 if I'm right xD

0

Hi Guys !

I've just read yours posts and bumps and thought this solution can help you. I'm using Laravel 5 and just defined a new Relation Type called HasManyThroughBelongsTo as following :


<?php namespace App\Relations;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\Relation;
use Illuminate\Database\Query\Expression;

class HasManyThroughBelongsTo extends Relation
{

    /**
     * The distance parent model instance.
     *
     * @var \Illuminate\Database\Eloquent\Model
     */
    protected $farParent;

    /**
     * The near key on the relationship.
     *
     * @var string
     */
    protected $firstKey;

    /**
     * The far key on the relationship.
     *
     * @var string
     */
    protected $secondKey;

    /**
     * @param Builder $query
     * @param Model   $farParent
     * @param Model   $pivotModel
     * @param         $firstKey
     * @param         $secondKey
     */
    public function __construct( Builder $query, Model $farParent, Model $pivotModel, $firstKey, $secondKey )
    {
        $this->firstKey  = $firstKey;
        $this->secondKey = $secondKey;
        $this->farParent = $farParent;
        parent::__construct( $query, $pivotModel );
    }

    /**
     * Set the base constraints on the relation query.
     *
     * @return void
     */
    public function addConstraints()
    {
        $parentTable = $this->parent->getTable();

        $this->setJoin();

        if ( static::$constraints )
        {
            $this->query->where( $parentTable . '.' . $this->firstKey, '=', $this->farParent->getKey() );
        }
    }

    /**
     * Add the constraints for a relationship count query.
     *
     * @param  \Illuminate\Database\Eloquent\Builder $query
     * @param  \Illuminate\Database\Eloquent\Builder $parent
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function getRelationCountQuery( Builder $query, Builder $parent )
    {
        $parentTable = $this->parent->getTable();

        $this->setJoin( $query );

        $query->select( new Expression( 'count(*)' ) );

        $key = $this->wrap( $parentTable . '.' . $this->firstKey );

        return $query->where( $this->getHasCompareKey(), '=', new Expression( $key ) );
    }

    /**
     * Set the join clause on the query.
     *
     * @param  \Illuminate\Database\Eloquent\Builder|null $query
     * @return void
     */
    protected function setJoin( Builder $query = null )
    {
        $query = $query ?: $this->query;
        $left  = $this->parent->getTable() . '.' . $this->secondKey;
        $right = $this->related->getQualifiedKeyName();
        $query->join( $this->parent->getTable(), $left, '=', $right );
    }

    /**
     * Set the constraints for an eager load of the relation.
     *
     * @param  array $models
     * @return void
     */
    public function addEagerConstraints( array $models )
    {
        $table = $this->parent->getTable();
        $this->query->whereIn( $table . '.' . $this->firstKey, $this->getKeys( $models ) );
    }

    /**
     * Initialize the relation on a set of models.
     *
     * @param  array  $models
     * @param  string $relation
     * @return array
     */
    public function initRelation( array $models, $relation )
    {
        foreach ( $models as $model )
        {
            $model->setRelation( $relation, $this->related->newCollection() );
        }

        return $models;
    }

    /**
     * Match the eagerly loaded results to their parents.
     *
     * @param  array                                    $models
     * @param  \Illuminate\Database\Eloquent\Collection $results
     * @param  string                                   $relation
     * @return array
     */
    public function match( array $models, Collection $results, $relation )
    {
        $dictionary = $this->buildDictionary( $results );

        // Once we have the dictionary we can simply spin through the parent models to
        // link them up with their children using the keyed dictionary to make the
        // matching very convenient and easy work. Then we'll just return them.
        foreach ( $models as $model )
        {
            $key = $model->getKey();

            if ( isset( $dictionary[ $key ] ) )
            {
                $value = $this->related->newCollection( $dictionary[ $key ] );

                $model->setRelation( $relation, $value );
            }
        }

        return $models;
    }

    /**
     * Build model dictionary keyed by the relation's foreign key.
     *
     * @param  \Illuminate\Database\Eloquent\Collection $results
     * @return array
     */
    protected function buildDictionary( Collection $results )
    {
        $dictionary = [ ];

        $foreign = $this->firstKey;

        // First we will create a dictionary of models keyed by the foreign key of the
        // relationship as this will allow us to quickly access all of the related
        // models without having to do nested looping which will be quite slow.
        foreach ( $results as $result )
        {
            $dictionary[ $result->{$foreign} ][ ] = $result;
        }

        return $dictionary;
    }

    /**
     * Get the results of the relationship.
     *
     * @return mixed
     */
    public function getResults()
    {
        return $this->get();
    }

    /**
     * Execute the query as a "select" statement.
     *
     * @param  array $columns
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public function get( $columns = [ '*' ] )
    {
        // First we'll add the proper select columns onto the query so it is run with
        // the proper columns. Then, we will get the results and hydrate out pivot
        // models with the result of those columns as a separate model relation.
        $select = $this->getSelectColumns( $columns );

        $models = $this->query->addSelect( $select )
                              ->getModels();

        // If we actually found models we will also eager load any relationships that
        // have been specified as needing to be eager loaded. This will solve the
        // n + 1 query problem for the developer and also increase performance.
        if ( count( $models ) > 0 )
        {
            $models = $this->query->eagerLoadRelations( $models );
        }

        return $this->related->newCollection( $models );
    }

    /**
     * Set the select clause for the relation query.
     *
     * @param  array $columns
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    protected function getSelectColumns( array $columns = [ '*' ] )
    {
        if ( $columns == [ '*' ] )
        {
            $columns = [ $this->related->getTable() . '.*' ];
        }

        return array_merge( $columns, [ $this->parent->getTable() . '.' . $this->firstKey ] );
    }

    /*
     * Get a paginator for the "select" statement.
     *
     * @param  int    $perPage
     * @param  array  $columns
     * @return \Illuminate\Pagination\Paginator
     */
    public function paginate( $perPage = null, $columns = [ '*' ] )
    {
        $this->query->addSelect( $this->getSelectColumns( $columns ) );

        return $this->query->paginate( $perPage, $columns );
    }

    /**
     * Get the key for comparing against the parent key in "has" query.
     *
     * @return string
     */
    public function getHasCompareKey()
    {
        return $this->farParent->getQualifiedKeyName();
    }

}

And extended Eloquent Model with


public function hasManyThroughBelongTo( $related, $through, $firstKey = null, $secondKey = null )
    {
        $through = new $through;
        $related = new $related;

        $firstKey  = $firstKey ?: $this->getForeignKey();
        $secondKey = $secondKey ?: $related->getForeignKey();

        return new HasManyThroughBelongsTo( $related->newQuery(), $this, $through, $firstKey, $secondKey );
    }

And then, the model relation looks like :


public function relatedModels()
    {
        return $this->hasManyThroughBelongTo( 'RelatedModel', 'PivotModel' );
    }

What do you think about this solution ?

0

terion-name said:

I use this approach (placed in base model):

   public function manyThroughMany($related, $through, $firstKey, $secondKey, $pivotKey)
   {
       $model = new $related;
       $table = $model->getTable();
       $throughModel = new $through;
       $pivot = $throughModel->getTable();

       return $model
           ->join($pivot, $pivot . '.' . $pivotKey, '=', $table . '.' . $secondKey)
           ->select($table . '.*')
           ->where($pivot . '.' . $firstKey, '=', $this->id);
   }

Cheers terion-name, I just used your method for a setup like:

  • Users table with the usual id field.
  • User Followers table for users that follow other users, with two IDs: id_follower and id_followed (both with foreign keys to the users.id column).

In the User model, to get a user's followers, a simple:

$this->hasManyThrough('App\Models\User', 'App\Models\UserFollower', 'id_followed', 'id');

did the trick, but to get the users a certain user is following it got trickier and I couldn't get it done with hasManyThrough(). The aforementioned manyThroughMany did the trick fine:

$this->manyThroughMany('App\Models\User', 'App\Models\UserFollower', 'id_follower', 'id', 'id_followed');
1

I had the same question today and I found a different solution which does not require an additional class and still works with the "BelongsToMany"-relation (so you can do eager loading):

(0) In the following example I'd like to get the products of a category's sub-categories, so

  • A category has a column parent_category_id, that might be null
  • A product can have many categories, so there is a table "category_product"

(1) Create a view as shortcut

DB::statement('create view parent_category_product_view as select distinct product_id,parent_category_id from category_product left join categories on (category_id = categories.id)');

(2) Add the relationship

$this->belongsToMany(Product::class, 'parent_category_product_view', 'parent_category_id');

That's it. Another (untested) example is the mentioned related product:

The view:

DB::statement('create view related_product_view as select distinct a.product_id as product_id,b.product_id as related_product_id from category_product as a join category_product as b on (a.category_id = b.category_id and a.product_id <> b.product_id)');

The relationship:

$this->belongsToMany(Product::class, 'related_product_view', 'related_product_id');
Last updated 7 years ago.
1

For people struggling with it, the approach I took was simply to create an intermediary model and access it that way.

Instead of thinking of it like user, user_group, and group, I thought of it like user, membership, group.

//user.php

public function memberships(){
 return $this->hasMany('Membership');
}

if you feel safe enough you can do the belongs to many with group directly as well for easy access but it's a bit dangerous when things start to get complex

//membership.php

public function user(){
 return $this->belongsTo('User');
}

public function group(){
return $this->belongsTo('Group');
}
//group.php

public function memberships(){
 return $this->hasMany('Membership');
}

Instead of $user->groups in your view do:

@foreach ($user->memberships as $membership)
{{{$membership->group->name}}}
@endforeach

I feel like we're told we're supposed to use pivots/many to many relationships but there's really almost no advantage to them (unless I'm missing something?). My rule is that if the pivot table has ANY attributes other then the ids I'll probably just use this method...or if I need events for them (since pivots don't have events either).

Last updated 7 years ago.
0

@Maxeee09 have you made your solution into a gist/plugin? I think that would be helpful. I'd like to see something like this get into the core framework.

Last updated 6 years ago.
0

@Maxeee09 Awesome code! I've just it as works smoothly. I'm with @whoacowboy. I think this relationship should be on the core.

0

In Laravel you can Constrain Eager loads with code like the following

$users = App\User::with(['posts' => function ($query) {
    $query->where('title', 'like', '%first%');
}])->get()

So you can include your many to many relationship in the second query. I had a situation where I had a table with "offers". Each offer was linked to a "business" in the business table. There was also a "users" table, and a many-to-many relationship existed between the users and the businesses as users could subscribe to one or more businesses to follow them for offers. So, to get a list of offers available for a user from all the businesses they follow can be found using the following:

$userOffers = Offer::has('business.subscribers')->with(['business' => function ($query) use ($userId) {
        $query->with(['subscribers' => function ($query) use ($userId) {
            $query->where('user_id', $userId);
        }]);
    }])->get();

I'm passing in the $userId variable in case you wonder what that is for. The 'subscribers' is the many to many relationship set up in the business model. This works without having to add anything extra to the model.

The has('business.subscribers') filters the list of offers where the business has at least one subscriber, which we are obviously setting.

Last updated 6 years ago.
0

This is awesome, only small issue. It doesn't compatible with softDeletes. So I added these lines:

	if (method_exists($this->parent, 'getDeletedAtColumn')) {
		$this->query->whereNull($this->parent->getTable().'.'.$this->parent->getDeletedAtColumn());
	}

	if (method_exists($this->related, 'getDeletedAtColumn')) {
		$this->query->whereNull($this->related->getTable().'.'.$this->related->getDeletedAtColumn());
	}

Basically extends the where condition with the "normal" IS NULL condition for softDeletes tables.

0

I fixed my issue by using the whereIn on the query builder

$user = Auth::user();
		$products=Product::where('deleted_at', NULL);
		if(!$user->hasRole("admin")) {
			$brands=[];
			foreach($user->brand as $brand){
				$brands[$brand->id] = $brand->id;
			}
			$products = $products->whereIn("brand_id",$brands);
		}

Last updated 6 years ago.
0

I think I've figured out how to fix this bug. vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/HasManyThrough.php::setJoin(). I've commented out the erroneous lines and placed their replacements directly beneath them:

/**
 * Set the join clause on the query.
 *
 * @param  \Illuminate\Database\Eloquent\Builder|null  $query
 * @return void
 */
protected function setJoin(Builder $query = null)
{
    $query = $query ?: $this->query;

    //$foreignKey = $this->related->getTable().'.'.$this->secondKey;
    $localKey = $this->parent->getTable().'.'.$this->secondKey;
    //$query->join($this->parent->getTable(), $this->getQualifiedParentKeyName(), '=', $foreignKey);
    $query->join($this->parent->getTable(), $localKey, '=', $this->related->getQualifiedKeyName());

    if ($this->parentSoftDeletes()) {
        $query->whereNull($this->parent->getQualifiedDeletedAtColumn());
    }
}

Until a fix is actually put in place, one option may be to have your model extend a BaseModel class that extends Model, that contains it's own incarnation of a hasManyThrough() method. Within that method, allow it to instantiate the Eloquent HasManyThrough class by calling on the parent incarnation of the regular hasManyThrough() method. Then assign an anonymous function to the setJoin method of that new object, overwriting it. This new function should mirror the original one, except with the addition of the fix described above.

Edit: Turns out php doesn't natively support 'monkey patching' in this way. runkit_method_redefine() may be the solution to this, but since it's something that needs to be installed separately, it may not be ideal.

Last updated 6 years ago.
0

I created a HasManyThrough relationship with support for BelongsToMany: Repository on GitHub

After the installation, you can use it like this:

User → belongs to many → Role → has many → Permission

class User extends Model {
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function permissions() {
        return $this->hasManyDeep(Permission::class, ['role_user', Role::class]);
    }
}
0

@staudenmeir

Thanks a lot!

It works!!!!!

It is a Laravel 5.5 composer package that can perform multi-level relationships (deep)

Package: https://github.com/staudenmeir/eloquent-has-many-deep

Example:

User → belongs to many → Role → belongs to many → Permission

class User extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function permissions()
    {
        return $this->hasManyDeep(
            'App\Permission',
            ['role_user', 'App\Role', 'permission_role'], // Pivot tables/models starting from the Parent, which is the User
        );
    }
}

Example if foreign keys need to be defined:

https://github.com/staudenmeir/eloquent-has-many-deep/issues/7#issuecomment-431477943

Last updated 3 years ago.
1

Sign in to participate in this thread!

LoadForge

Your banner here too?

futureFry futurefry Joined 4 Mar 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

The Laravel portal for problem solving, knowledge sharing and community building.

© 2022 Laravel.io - All rights reserved.