CODEWITHSUNDEEP
phpformslaravel
Ajaxcbcb
Ajaxcbcb

Reputation: 167

Laravel 5 Join table query with where clause

Trying to achieve with Laravel 5.2's eloquent model

I have 2 tables:

1) projects (id, projectName, users_id)

2) todo (id, todoName, ProjectID[id], users_id)

i want to join these table and foreach the table content with

->where('users_id','=',Auth()->User()->id)

Part of the code

$todos = DB::table('to_dos')->join('projects','to_dos.projectID','=','projects.projectName')
                                                            ->where('users_id','=',Auth()->User()->id)
                                                            ->get();

the objective is to print toDoName and projectName

@foreach($todos as $todo)
    {{$todo -> ToDoName}}
    {{$todo -> projectName}}
    <br>
@endforeach

Upvotes: 0

Views: 6001

Answers (2)

Jobin
Jobin

Reputation: 8282

Try this,

$todos = DB::table('to_dos as T')
->leftjoin('projects as P','T.projectID','=','P.projectName')
->Where('T.users_id',Auth()->User()->id)
->get();

Upvotes: 0

Jilson Thomas
Jilson Thomas

Reputation: 7303

The best way is to create a relation between the tables and eager load the relation.

In your Todo model, add the following method: 

public function project()
{
  return $this->belongsTo('App\Project');
}

Now, from your controller, you can just call:

$todos = Todo::with('project')->where('user_id', Auth::id());

This would give you the Todos for the currently logged in user with the corresponding projects.

Now in your view, you can use: 

@foreach($todos as $todo)
   {{ $todo->name }}
   {{ $todo->project->name }}
@endforeach

Note: It will be good if you follow some conventions. The table columns shall be as follows:

users table -> Model: User.php
columns: id, name....

projects table -> Model: Project.php
columns: id, name, user_id

todos table-> Model: Todo.php
columns: id, name, project_id, user_id.

Remember, when you follow the convention over configurations, things are much easier.

Upvotes: 3

Related Questions