An eloquent way to get related *and* unrelated models

[kohenkatz]

I have models Organization and Feature, with a belongsToMany relationship between them (i.e. an organization has multiple features, and a feature can be used by multiple organizations).

I am being asked for a simple "list of all features, indicating which of those features are enabled for the given organization."

One of my coworkers used the following query to do it:

SELECT
    f.id,
    f.friendly_name,
    of.id IS NOT NULL AS enabled
FROM features f
LEFT OUTER JOIN organization_features of
    ON f.id = of.feature_id AND of.organization_id=?
ORDER BY 1;

I'm trying to figure out if there is a more "Laravel-style" way to write this.

Obviously I can just translate this query to use the query builder, but is there a better way?

[macropay-solutions]

List of all features does not depend on the organisations so why the need to link it? You can use the append.

in your model

/**
 * called via $this->append(['allFeatures'])
 */
public function getAllFeaturesAttribute(): array
{
    return Feature::query()->all(); // which you can extract into a function that is caching them .
}

https://laravel.com/docs/10.x/eloquent-collections#method-append

[shaedrich]

Having a model attribute that queries a collection, feels wrong

[kohenkatz]

Thanks, but that doesn't really answer the question - it's not any different that doing separate queries for the features table and the features_organizations table and then putting tother the result in the view. It's still two separate queries.

The point of the question is about if there's a nicer way to do a single query like the SQL query that is shown.

[macropay-solutions]

Then you can create a new BelongsToMany class that extends Illuminate\Database\Eloquent\Relations\BelongsToMany and overwrite the performJoin method

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

    // We need to join to the intermediate table on the related model's primary
    // key column with the intermediate table's foreign key for the related
    // model instance. Then we can set the "where" for the parent models.
    $query->join(
        $this->table,
        $this->getQualifiedRelatedKeyName(),
        '=',
        $this->getQualifiedRelatedPivotKeyName(),
        'outer' // see here
    );

    return $this;
}

You can add select and order in that relation.(try without them first)

[macropay-solutions]

@shaedrich any relation is a query in model when eager loading anyway.

[shaedrich]

@macropay-solutions Yeah, but not an attribute. I suppose, you're mixing concepts here.

[daronspence]

@kohenkatz sorry for the years late reply, but did you ever figure out a solution to this? I have a parent with children that are sometimes related through a pivot table, but I would like to load everything as if they are always related. Seems like a similar type of problem.

Event

• Team
  • Volunteer

Events have volunteers through teams, but they also have all teams, but sometimes the teams will be empty from event to event. Teams and Volunteers are a fixed pool that largely stays static between Events, but every event has different volunteers on each team based on a pivot table scheduled_volunteers = (event_id, team_id, volunteer_id)

I would love to be able to define all teams as belonging to an Event, if if there is nobody currently scheduled.

[macropay-solutions]

@kohenkatz

Questions:

if if there is nobody currently scheduled.

How do you know that there is no one being scheduled?
What is the resource that you want to list from these and with which relations?
What is the structure of all these tables?

[daronspence]

How do you know that there is no one being scheduled?

The scheduled_volunteers pivot table is empty for that event/team combo.

What is the resource that you want to list from these and with which relations?

I would ideally like to load Events with all Teams (even empty ones) and have Volunteers be a loaded relationship on Teams.

So $event->load('teams.volunteers');

However, it would also still be handy to be able to load/query Volunteers from the Event with $event->volunteers

What is the structure of all these tables?

Table structure is exactly what you imagine. The only thing that ties these three models together for the sake of this discussion is the scheduled_volunteers pivot table.

scheduled_volunteers
- id
- event_id
- team_id
- volunteer_id
- timestamps

[macropay-solutions]

@daronspence so this combination can be

• event_id // not nullable
• team_id // nullable
• volunteer_id // nullable

team_id and volunteer_id can't be both null at the same time

Or maybe only volunteer_id can be nullable. That fits better with your description.

[macropay-solutions]

If the Events are not tied with teams via pivot, that can't be defined as a relation.

class Event extends BaseModel
 {
    protected $fillable = [];

    public function volunteers(): BelongsToMany
    {
        return $this->belongsToMany(
            Volunteer::class, // 4
            ScheduledVolunteerPivot::class, // 2 & 3
            'event_id', // 2
            'volunteer_id', // 3
            'id', // 1 $this
            'id', // 4
            __FUNCTION__
        )->wherePivotNotNull('volunteer_id')->withPivot((new ScheduledVolunteerPivot())->getColumns());
    }

    public function teams(): BelongsToMany
    {
        return $this->belongsToMany(
            Team::class, // 4
            ScheduledVolunteerPivot::class, // 2 & 3
            'event_id', // 2
            'team_id', // 3
            'id', // 1 $this
            'id', // 4
            __FUNCTION__
        )->wherePivotNotNull('team_id'); // if team_id is not nullable this is not needed
    }

    public function emptyTeams(): BelongsToMany
    {
            return $this->teams()->wherePivotNull('volunteer_id');
    }

    public function teamsWithEventVolunteers(): BelongsToMany
    {
            if ($this->exists) {
                return $this->teams()->with('volunteers', function (Relation $builder): void {
                     $builder->wherePivot('event_id', '=', $this->id);
                });
            }

           return $this->teams();
    }
}

class Team extends BaseModel
{
     protected $fillable = [];

    public function volunteers(): BelongsToMany
    {
        return $this->belongsToMany(
            Volunteer::class, // 4
            ScheduledVolunteerPivot::class, // 2 & 3
            'team_id', // 2
            'volunteer_id', // 3
            'id', // 1 $this
            'id', // 4
            __FUNCTION__
        )->wherePivotNotNull('volunteer_id');
    }
}

class Volunteer extends BaseModel
{
     protected $fillable = [];
}

class ScheduledVolunteerPivot extends BaseModel
{
     protected $fillable = ['event_id', 'team_id', 'volunteer_id'];
}

class EventsService extends BaseResourceService
{
    /**
     * @inheritDoc
     */
    protected function setBaseModel(): void
    {
        $this->model = new Event();
    }

    /**
     * @inheritDoc
     */
    public function list(array $request): Builder
    {      
       $builder = $this->model::query();

       $builder->with(['teams', 'volunteers']); // on list teamsWithEventVolunteers will not return volunteers from that event. See get

       if (isset($request['volunteerName']) {
            $builder->whereRelation(
               'volunteers',
               function (Builder $builder) use ($request): void {
                    $builder->where('name', $request['volunteerName']);
               }
           );
       }
        
        return $builder;
    }

    /**
     * @inheritDoc
     */
    public function get(string $identifier, array $withRelations = [], bool $appendIndex = true): BaseModel
    {
        $model = $this->model::query()->with($withRelations)->where(
            $this->extractIdentifierConditions($identifier)
        )->firstOrFail()->appendIndexRequiredOnFilteringAttribute($appendIndex);
        $model->teamsWithEventVolunteers;

        return $model;
    }
}

@daronspence The above is the final version that we could think of.

[macropay-solutions]

@daronspence if teams are not related to Events via pivot (volunteer_id = null case), you could fetch all teams once and then in php custom "eager load" them into Events as empty teams if they are not present.

Also to achieve Event-Team-Volunteer architecture on LIST (on get you have it), you could eager load into each event-team the volunteers bases on volunteers relation and on the pivot that comes with each volunteer.

[macropay-solutions]

@daronspence
Reading this again after so long gives us another idea.
From the experience with our cruFd lib suite, this is how we would do it:

Request: list of all features, indicating which of those features are enabled for the given organization

SELECT
    f.id,
    f.friendly_name,
    of.id IS NOT NULL AS enabled
FROM features f
LEFT OUTER JOIN organization_features of
    ON f.id = of.feature_id AND of.organization_id=?
ORDER BY 1;

This implies enabled if the pivot exists for it.

class Feature extends BaseModel
 {
    protected $fillable = ['friendly_name'];

    public function organizationFeaturePivots(): HasMany
    {
        return $this->hasMany(OrganizationFeaturePivot::class, 'feature_id', 'id')
    }
}

class OrganizationFeaturePivot extends BaseModel
{
     protected $fillable = ['organization_id', 'feature_id'];
}

class Organization extends BaseModel
{

}

class FeaturesService extends BaseResourceService
{
    /**
     * @inheritDoc
     */
    protected function setBaseModel(): void
    {
        $this->model = new Feature();
    }

    /**
     * @inheritDoc
     */
    public function list(array $request): Builder
    {
        // Request: list of all features, indicating which of those features are enabled for the given organization
        
       $builder = $this->model::query();

       $builder->withExists(['organizationFeaturePivots as enabled' => function (Builder $builder) use ($request): void {
            $builder->where('organization_id', $request['organization_id'] ?? null);
       }])
        
        return $builder;
    }
}

[macropay-solutions]

@kohenkatz
Demo
https://maravelith.laravel-crud-wizard.com/maravelith-10/laravel-lumen-crud-wizard#operations

use this query:
aggregates[existRelations][0]=operationProductPivots&aggregates[existRelationsFilters][operationProductPivots][product_id][in][0]=45482&limit=10&page=1&withRelations[0]=products&id[from]=3748913

SQL:

SELECT 
    `operations`.*,
    EXISTS( SELECT 
            *
        FROM
            `operations_products_pivot`
        WHERE
            `operations`.`id` = `operations_products_pivot`.`operation_id`
                AND `operations_products_pivot`.`product_id` = '45482') AS `operationProductPivots_exist`
FROM
    `operations`
WHERE
    `operations`.`id` >= '3748913'
ORDER BY `created_at` DESC
LIMIT 10 OFFSET 0