#StackBounty: #php #mysql #laravel #eloquent #laravel-6 Laravel relationship conflicts in union

Bounty: 100

I have following model:
1- User model

 /**
 * Define user and functional area relationship
 */
public function functionalAreas()
{
    return $this->belongsToMany('AppFunctionalArea', '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('AppFunctionalArea', '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 AppUser model in result, its like businesses are also as user object.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.