Laravel join query using Laravel eloquent

0
votes

i have two tables 'users' and 'projects'

Table users

-----------------------------------
user_id | name | email | password |
-----------------------------------
   1    |name1 |email1 | password1|
-----------------------------------
   2    |name2 |email2 | password2|

Table projects

 project_id  |user_id | name          | 
 --------------------------------------
    1        |   1    | project_name1 |
 -----------------------------------
    2        |   1    | project_name2 |

There is no relation between these two tables but user_id in projects are the id from user table.

Normal Query

SELECT a.user_id,a.name AS username,b.name AS project_name FROM users a LEFT JOIN projects b ON a.user_id=b.user_id;

I want the same query execution in laravel eloquent.

Have searched in google got some idea about "with" function but not able to get the output.

Thanks.

4
phpmysqllaravel-5
it is not clear what your question is. please state what result you expect. – 1010
Are you using DB::raw ? – Sulthan Allaudeen
@1010 i need to join two tables the normal query i have mentioned. The same output i have to accomplish in laravel eloquent. for eg : using User::all() like statements. – PRASANTH
if you want it with eloquent you should have primary and foreign keys defined in the tables in question. – Khan Shahrukh

4 Answers

2
votes

You can simply do as following,

DB::table('users')
        ->join('projects =', 'users.id', '=', 'posts.user_id')
        ->select('*')
        ->get();

For more details you can refer laravel Docs

2
votes

This is the way you should use Eloquent: create User.php

<?php

class User extends Eloquent
{
    public function projects()
    {
        return $this->hasMany('Project');
    }

}

create Project.php

<?php

class Project extends Eloquent
{
}

On controller, write this to get all users, with related projects inserted inside each users:

$users = User::with('projects')->all();
foreach($users as $user)
{
    $project_of_this_user = $user->projects;
}
1
votes

This should be what you need:

DB::table('users')
  ->join('projects', 'projects.user_id', '=', 'users.user_id')
  ->select('users.user_id', 'users.name as username', 'projects.name as project_name')
  ->get();
1
votes

I think, this one helps you out 

    DB::table('users')
            ->leftJoin('projects', 'users.user_id', '=', 'projects.user_id')
            ->select('users.user_id', 'users.name as username', 'projects.name as project_name')
            ->get();