Roni
3 years ago

Non Standard Relationship Issue, is it a bug or is it me?

Posted 3 years ago by Roni

I've been trying to leverage eloquent more, as my models are becoming bloated with functionality that seems to "already" be handled by eloquent, but I can't get some relationship features to work. This issue centers around adding and updating items in a very simple pivot. I have several of these pivots around a series of objects that can all be interconnected. Servers can be under a router, users on a server, users on a router, security layers etc...

It's not that big a system and the logic is simple enough, but the many to many interconnects are killing me.

Laravel 5.1.33

Here is some working code

self::synchronizeUsers($servicePlanId,$user_id);
// This should be done via relationship, but instead I'm making M:N models
// here is the function being run.
...
    public static function synchronizeUsers($servicePlanId,$userId) {

        $servicePlansGroups = ServicePlan::groupsIDsArray($servicePlanId);
        foreach ($servicePlansGroups as $servicePlansGroup){
            SyncUserGroup::addOrUpdate($servicePlansGroup,$userId);
        }
    }

in my other class, here is the addOrUpdate function. I just made a model that fits over the existing table that I made for the M:N relationship. That was a quick easy fix, but now I'm duplicating eloquent expected behavior, and I'll have to do it everywhere I have M:N's, which seems like a poor solution.

Here is the class SyncUserGroup, that is doing the job of the relationship:

    public static function addOrUpdate($group_id,$user_id) {

        $model = SyncUserGroup::where('group_id',$group_id)->where('user_id',$user_id)->first();
        if(is_null($model)){
            SyncUserGroup::create(['group_id'=>$group_id,
                                   'user_id'=>$user_id]);

        }else{
            SyncUserGroup::where('group_id',$group_id)->where('user_id',$user_id)
                ->update(array('last_verified_on' => date('Y-m-d H:i:s')));
        }
    }

Now, even though this all works fine, It just bothers me that I can't get this relationship to work, so here's my second attempt. With all the relationships: So I'm I'm getting a userobject, relating a service plan to the user, and then calling all the groups (apps, which are hosted on servers and adding the user to the group.

    foreach ($this->plan->groups as $group)
            {
                // I used attach detach because I couldn't find a good test to
                // run against searching for the key,I found one in the next
                // iteration, but it exposed another weird issue.

                $group->users()->detach($user_id);
                $group->users()->attach($user_id);

            }

// here are the associated relationships on the vpnuser object
    public function groups() {
        return $this->belongsToMany('\App\Group', 'sync_user_groups','user_id','group_id');
    }

    public function plan() {
        return $this->belongsTo('\App\ServicePlan', 'service_plan_id');
    }

// Here is one other side of the plan relation, the service plan to group

    public function groups() {
        return $this->hasMany('\App\Group','service_plan_id','id');
    }

    public function vpnuser() {
        return $this->hasMany('App\VpnUser','service_plan_id','id');
    }

// And finally the group to vpnuser

    public function servicePlan() {
        return $this->belongsTo('\App\ServicePlan','service_plan_id');
    }

    public function users() {
        return $this->belongsToMany('\App\VpnUser','sync_user_groups','group_id','user_id')
            ->withPivot('last_verified_on')
        ;

I haven't calculated the server cost, but I thought an update might be simpler then detach and attach , however the contains() method is a super expensive server method downloading the entire pivot and using a collection to filter it, so, I thought I'd write a "hasPivot" function and add it to the mix. I found this on stack exchange, but the error that it's throwing shows a really weird SQL error that in my mind is showing me something else is happening or I'm getting a bit lost.

So I've changed detach(), attach() to the follwing

        foreach ($this->plan->groups as $group)
            {
                if($group->hasPivot('users','user_id',$user_id))
                    $group->users()->updateExistingPivot(array('last_verified_on'=>time()));
                else
                    $group->users()->attach($user_id);
            }

The hasPivot code function found in the controller looks like this:

function hasPivot($relation_name, $searchKey,$keyValue) {
    return (bool) $this->$relation_name()->where($searchKey,'=',$keyValue)->count();
}

This blows up to an error that has me writing this post I could probably figure out how to add the table name to the search key, so I'm not worried about the ambiguous call. The big issue is on the join

QueryException in Connection.php line 655:
SQLSTATE[23000]: Integrity constraint violation: 
1052 Column 'user_id' in where clause is ambiguous 
(SQL: select count(*) as aggregate from `vpnusers` 
inner join `sync_user_groups` on `vpnusers`.`id` = `sync_user_groups`.`user_id` 
where `sync_user_groups`.`group_id` = 15 and `user_id` = 21)

vpnusers.id = sync_user_groups.user_id where Specifically sync_user_groups.id???? Where is that comming from? The relationships all state sync_user_groups.user_id specifically.

If you are running into something similar, Read this thread to the end!!! Thanks.

Please sign in or create an account to participate in this conversation.