Laravel join query using Laravel eloquent

Question

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.

it is not clear what your question is. please state what result you expect. — 1010
– 1010, Commented Jun 5, 2015 at 4:24
@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
– PRASANTH, Commented Jun 5, 2015 at 4:39
if you want it with eloquent you should have primary and foreign keys defined in the tables in question. — Khan Shahrukh
– Khan Shahrukh, Commented Jun 5, 2015 at 5:27

Bishal Paudel · Accepted Answer · 2015-06-05 05:12:15Z

2

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

edited Jun 5, 2015 at 5:12

answered Jun 5, 2015 at 5:06

Bishal Paudel

1,9662 gold badges21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PRASANTH Over a year ago

Thanks this will help for relations but if i have no relation between users and projects how can i put where condition.

Bishal Paudel Over a year ago

$users = User::with('projects')->where('email', '=', '[email protected]')->get(); OR $users = User::where('email', '=', '[email protected]')->get();

Damith · Accepted Answer · 2015-06-05 04:50:24Z

2

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

answered Jun 5, 2015 at 4:50

Damith

1142 bronze badges

Comments

BudwiseЯ · Accepted Answer · 2015-06-05 04:46:19Z

1

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

answered Jun 5, 2015 at 4:46

BudwiseЯ

1,8262 gold badges16 silver badges28 bronze badges

1 Comment

PRASANTH Over a year ago

I have tried these and are working. I need to do it using laravel model. I have created User model class for 'users' and Project class for 'projects'

Md Eftakhairul Islam · Accepted Answer · 2015-06-05 05:26:55Z

1

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

edited Jun 5, 2015 at 5:26

answered Jun 5, 2015 at 5:06

Md Eftakhairul Islam

1985 silver badges10 bronze badges

Collectives™ on Stack Overflow

Laravel join query using Laravel eloquent

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related