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.

Uncaused

you have typo in :private function users) should be users() – Scoundrel

Could you output the result of the final query with ->toSql( ) and share ? – Blond

I suspect there is a conflict in DB::raw("CONCAT(first_name, ' ', last_name) AS name"), and DB::raw("functional_areas.name_en AS name"),. Use different name alias for both – Blond

@Blond Updated my question. – Uncaused

I don't think you can mix UNION and eager loading (with) in laravel. After performing the UNION query laravel doesn't know from which table the ids come from. I' afraid you'll have to implement your own eager loading logic or use lazy loading (which would't be the end of the world with 10 records). – Courtenay

@jones, it would be great if you can share the db schema with us. waiting for your reply. You can create a fiddle for the same with some data for quick understanding. – Archdeacon

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.

Neustria

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);
}

Archdeacon

@jones, can you try that. Is it working for you? Pagination also not considered here. – Archdeacon

Your solution seems to work, but how about pagination issue, I want to take 10 records of mixed organizations, and users. – Uncaused

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;

Dandridge