Because of the way eager loading works, there isn't anything you can really do to the SQL being run to get done what you're looking for.
When you do Product::with('parent')->get(), it runs two queries.
First, it runs the query to get all the products:
select * from `products`
Next, it runs a query to get the eager loaded parents:
select * from `products` where `products`.`id` in (?, ?, ?)
The number of parameters (?) corresponds to the number of results from the first query. Once the second set of models has been retrieved, the match() function is used to relate the objects to each other.
In order to do what you want, you're going to have to create a new relationship and override the match() method. This will handle the eager loading aspect. Additionally, you'll need to override the addConstraints method to handle the lazy loading aspect.
First, create a custom relationship class:
class CustomBelongsTo extends BelongsTo
{
// Override the addConstraints method for the lazy loaded relationship.
// If the foreign key of the model is 0, change the foreign key to the
// model's own key, so it will load itself as the related model.
/**
* Set the base constraints on the relation query.
*
* @return void
*/
public function addConstraints()
{
if (static::$constraints) {
// For belongs to relationships, which are essentially the inverse of has one
// or has many relationships, we need to actually query on the primary key
// of the related models matching on the foreign key that's on a parent.
$table = $this->related->getTable();
$key = $this->parent->{$this->foreignKey} == 0 ? $this->otherKey : $this->foreignKey;
$this->query->where($table.'.'.$this->otherKey, '=', $this->parent->{$key});
}
}
// Override the match method for the eager loaded relationship.
// Most of this is copied from the original method. The custom
// logic is in the elseif.
/**
* 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)
{
$foreign = $this->foreignKey;
$other = $this->otherKey;
// First we will get to build a dictionary of the child models by their primary
// key of the relationship, then we can easily match the children back onto
// the parents using that dictionary and the primary key of the children.
$dictionary = [];
foreach ($results as $result) {
$dictionary[$result->getAttribute($other)] = $result;
}
// Once we have the dictionary constructed, we can loop through all the parents
// and match back onto their children using these keys of the dictionary and
// the primary key of the children to map them onto the correct instances.
foreach ($models as $model) {
if (isset($dictionary[$model->$foreign])) {
$model->setRelation($relation, $dictionary[$model->$foreign]);
}
// If the foreign key is 0, set the relation to a copy of the model
elseif($model->$foreign == 0) {
// Make a copy of the model.
// You don't want recursion in your relationships.
$copy = clone $model;
// Empty out any existing relationships on the copy to avoid
// any accidental recursion there.
$copy->setRelations([]);
// Set the relation on the model to the copy of itself.
$model->setRelation($relation, $copy);
}
}
return $models;
}
}
Once you've created your custom relationship class, you need to update your model to use this custom relationship. Create a new method on your model that will use your new CustomBelongsTo relationship, and update your parent() relationship method to use this new method, instead of the base belongsTo() method.
class Product extends Model
{
// Update the parent() relationship to use the custom belongsto relationship
public function parent()
{
return $this->customBelongsTo('App\Product', 'parent_id', 'id');
}
// Add the method to create the CustomBelongsTo relationship. This is
// basically a copy of the base belongsTo method, but it returns
// a new CustomBelongsTo relationship instead of the original BelongsTo relationship
public function customBelongsTo($related, $foreignKey = null, $otherKey = null, $relation = null)
{
// If no relation name was given, we will use this debug backtrace to extract
// the calling method's name and use that as the relationship name as most
// of the time this will be what we desire to use for the relationships.
if (is_null($relation)) {
list($current, $caller) = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 2);
$relation = $caller['function'];
}
// If no foreign key was supplied, we can use a backtrace to guess the proper
// foreign key name by using the name of the relationship function, which
// when combined with an "_id" should conventionally match the columns.
if (is_null($foreignKey)) {
$foreignKey = Str::snake($relation).'_id';
}
$instance = new $related;
// Once we have the foreign key names, we'll just create a new Eloquent query
// for the related models and returns the relationship instance which will
// actually be responsible for retrieving and hydrating every relations.
$query = $instance->newQuery();
$otherKey = $otherKey ?: $instance->getKeyName();
return new CustomBelongsTo($query, $this, $foreignKey, $otherKey, $relation);
}
}
Fair warning, none of this has been tested.
