CODEWITHSUNDEEP
phpmysqllaravellaravel-5eloquent
Snedden27
Snedden27

Reputation: 1930

A little complex query using eloquent

I am trying to learn laravel and currently using eloquent to interact with the database. I am stuck on how I could use eloquent to get a kind of a join in eloquent.

I have a many to many relation between two tables :users and projects , I use sharedProject table to be the intermediate table .

The tables are as such

Users:

| iduser | name       | password       |
----------------------------------------
|  1     | somename   | hashPassword   |
|  2     | somename2  | hashPassword   |
|  3     | somename3  | hashPassword   |
|  4     | somename4  | hashPassword   |
----------------------------------------

Projects:

| pid | projectname
-------------------
|  1  | somename
|  2  | somename

SharedProjects:

| pid | iduser | sharedProjectid | 
----------------------------------
|  1  |  1     |       1         |
|  1  |  2     |       2         |

Now I want to get all the users who are not sharing a given project, for example in the above case for project with id 1 , I should get user 3 and user 4.

Here are my relationships in User model

  /**
     * User can have many projects
     *
     * @var array
     */
    public function projects(){
        return $this->hasMany('App\Project','pid','iduser'); // hasmany(model,foreignkey,localkey)
    }


    /**
     * The user can have many shared projects
     */
    public function sharedProjects()
    {

        return $this->belongsToMany('App\SharedProject', 'sharedProjects', 'iduser', 'pid');//  belongsToMany('intermediate tablename','id1','id2')
    }

and in the Project model:

     /**
         * The project can be shared by many users
         */
        public function sharedProjects()
        {

            return $this->belongsToMany('App\SharedProject', 'sharedProjects', 'pid', 'iduser');//  belongsToMany('intermediate tablename','id1','id2')
        }
 /**
     * a project belongs to a single user
     *
     * @var array
     */
     public function user()
    {
        return $this->belongsTo('App\User');
    }

I would prefer a eloquent way to do this , but would also except it, if can't be done in eloquent and I have to see a alternate approach I would appreciate a plain mysql query as well.

Thanks

Upvotes: 0

Views: 152

Answers (2)

Snedden27
Snedden27

Reputation: 1930

I resort to use plain mysql queries, this seems to work for me:

 $nonSharedUsers = DB::select( DB::raw("SELECT iduser FROM users WHERE  NOT EXISTS (SELECT * FROM sharedProjects WHERE sharedProjects.iduser= users.iduser and sharedProjects.pid=:projectId)"), array(
                     'projectId' => $pid,
                    ));

Upvotes: 0

patricus
patricus

Reputation: 62248

Once you define your Eloquent models and your many-to-many relationships, you can use them to get the data you're looking for.

Assuming a User model that has a projects relationship defined, you can use the whereDoesntHave() method to query for a list of users that are not related to a specific project.

$projectId = 1;
$users = User::whereDoesntHave('projects', function ($q) use ($projectId) {
        return $q->where('projects.id', $id);
    })->get();

You can read about defining many-to-many relationships in Eloquent here.

You can read about querying relationship existence here.

As you may notice, not all methods are documented (like whereDoesntHave()), so you may have to go source code diving. You can dig into the Eloquent codebase here.

Upvotes: 3

Related Questions