Laravel relationship conflicts in union

Question

I have following model: 1- User model

 /**
 * Define user and functional area relationship
 */
public function functionalAreas()
{
    return $this->belongsToMany('App\FunctionalArea', 'user_functional_areas', 'user_id', 'functional_area_id')->withPivot('id', 'is_primary')->withTimestamps();
}

and Business model:

 /**
 * Define business and user functional area relationship
 */
public function functionalAreas()
{
    return $this->belongsToMany('App\FunctionalArea', 'business_functional_areas', 'business_id', 'functional_area_id')->withTimestamps();
}

Now I should take all businesses and users and show them in a single list, for this I'm using from union, following is my query:

public function usersAndOrganizations()
{
    $users = $this->users();

    $organizations = $this->organizations();

    $invitees = $users->union($organizations)->paginate(10);
    
    return response()->json($invitees);
}

private function users()
{
    $users = User::byState($approved = true, 'is_approved')
        ->search()->select([
            'id',
            DB::raw("CONCAT(first_name, ' ', last_name) AS name"),
            'about',
            'address',
            'slug',
            'average_reviews',
            DB::raw("'freelancer' AS type")
        ]);

  $users = $users->with([
        "functionalAreas" => function ($q) {
            $q->select([
                'functional_areas.id',
                DB::raw("functional_areas.name_en AS name"),
            ]);
        }
    ]);
    return $users;
}
 

private function organizations()
{
    $businesses = Business::where('owner_id', '!=', auth()->user()->id)->verified()
        ->active()->search()
        ->select([
            'id',
            'name',
            'about',
            'address',
            'slug',
            'average_reviews',
            DB::raw("'business' AS type")
        ]); 
        $businesses = $businesses
            ->with([
            "functionalAreas" => function ($q) {
                $q->select([
                    'functional_areas.id',
                    DB::raw("functional_areas.name_en AS name"),
                ]);
            }
        ]);
        return $businesses;
} 

But above query not return the business functional area, its output query use from user relationship instead of business, that with section generate twice the following query:

select
  `functional_areas`.`id`,
  functional_areas.name_en AS name,
  `user_functional_areas`.`user_id` as `pivot_user_id`,
  `user_functional_areas`.`functional_area_id` as `pivot_functional_area_id`,
  `user_functional_areas`.`id` as `pivot_id`,
  `user_functional_areas`.`is_primary` as `pivot_is_primary`,
  `user_functional_areas`.`created_at` as `pivot_created_at`,
  `user_functional_areas`.`updated_at` as `pivot_updated_at`
from `functional_areas`
inner join `user_functional_areas`
  on `functional_areas`.`id` = `user_functional_areas`.`functional_area_id`
where `user_functional_areas`.`user_id` in (2, 6, 7)

But in fact 6, and 7 is business id not user only 2 is user id, one of this queries should use business_functional_areas instead of user_functional_areas. One more thing found is, all items are inside App\User model in result, its like businesses are also as user object.

Answer 1

The only way for now is to use from map.

public function usersAndOrganizations()
{
    $users = $this->users();

    $organizations = $this->organizations();

    $invitees = $users->union($organizations)->paginate(10);
  
    $invitees = $this->getRelatedData($invitees);

    return response()->json($invitees);
}


private function getRelatedData($invitees)
{
    $invitees->map(function($object) use($functionalAreaName) {
        if($object->type == 'business') {
            $relationName = 'businesses';
            $relationKey = 'business_id';
            $attachableType = Business::MORPHABLE_TYPE;
        }
        if($object->type == 'freelancer') {
            $relationName = 'users';
            $relationKey = 'user_id';
            $attachableType = User::MORPHABLE_TYPE;
        }
        $functionalAreas = FunctionalArea::whereHas($relationName, function($q) use ($object, $relationKey){
            $q->where($relationKey, $object->id);
        })->get([$functionalAreaName.' As name', 'id']);

        $object->functional_areas =  $functionalAreas->toArray();

    });

    return $invitees;
}

And remove with from your functions, and call this after you get the paginated result.

Answer 2

In simple words, for now you would not be able to achieve it using Eloquent Eager Loading with Unions. This is not supported yet in Laravel. One of such scenario for which they closed as a Non-Fix issue is Union with Eloquent fail....

Reason: While calling UNION function only the first model(user) is considered main model and model type of result set of other model(Business) passed as argument will be converted to the main one(USER) only and the main model relationship is called on all records(not the desired one).

Due to the above issue only relationship of user model is called on each record of result set. So even for business_id = 1, functional_area of user_id =1 are being fetched.

You can debug more about it from below file & function.

File: 
<your_laravel_project>\vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php
Function: get

Alternate Solution You can fetch the both result set as it is and then merge them after data fetch using php.

public function usersAndOrganizations()
{
    $users = $this->users()->get();
    $organizations = $this->organizations()->get();
    $invitees =  $users->toBase()->merge($organizations->toBase())->toArray();
    dd($invitees);
}

Answer 3

You can not concat incompatible queries with union. See Unions.
Your users() method return eloquent builder for User entity. And organizations() return builder for Business entity.
Thus, it is incorrect to select users and organizations in one query.
The correct query is like that:

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;