Laravel Join Tables

Laravel join tables

To achieve this, you have Relationship in Eloquent ORM. The official website states :

Of course, your database tables are probably related to one another. For example, a blog post may have many comments, or an order could be related to the user who placed it. Eloquent makes managing and working with these relationships easy. Laravel supports many types of relationships:

You can read all about eloquent relationship over here

If you do not want to use relationship, following is an example on how to join two tables :

   DB::table('users')
->select('users.id','users.name','profiles.photo')
->join('profiles','profiles.id','=','users.id')
->where(['something' => 'something', 'otherThing' => 'otherThing'])
->get();

The above query will join two tables namely users and profiles on the basis of user.id = profile.id with two different conditions, you may or may not use where clause, that totally depends on what you are trying to achieve.

Laravel, query on joined table after join

Simply alter the relationship with a callback:

return AssignmentResponse::where('assignment_id', $request->assignment)
    ->with('likes')
    ->when(
        $request->filled("queryString"),
        function ($q) use ($request) {
            $q->whereHas("user", fn ($q) => $q->where("name", "like", "%$request->queryString%"))
                ->with(["user" => fn ($q) => $q->where("name", "like", "%$request->queryString%")]);
        }
    )
    ->get()

Note both with() and whereHas() are used to ensure only matching values are returned. A conditional clause is used in place of your if statement.

Laravel join three tables with relationships

As you have not defined relationships, we have to go with Query builder, Update your joins as below

 $payments = DB::table('sales_payments')
    ->join('sales_invoices', 'sales_invoices.invoice_id', '=', 'sales_payments.invoice_id')
    ->join('customers', 'sales_invoices.customer_id', '=', 'customers.customer_id')
    ->join('payment_options', 'payment_options.paymentoption_id', '=', 'sales_payments.paymentoption_id')
    ->select('sales_payments.*', 'sales_invoices.*', 'customers.*', 'payment_options.*')
    ->get();

Then you can add where clause.

How to join tables in Laravel?

To get user created challenges, I believe you are already doing it correctly.

And to get user joined challenges. You almost get it right with your pivot table. You can have look on many-to-many relationship in laravel documentation.

These are the sample code to retrieve challenges created by a user and challenges joined by a user. These code should reside in your User Model, you should keep the model.

public function created_challenges()
{
    return $this->hasMany(Challenge::class,'user_id','id');
}

public function joined_challenges()
{
     return $this->belongsToMany(Challenges::class, 'user_challenges', 'user_id', 'challenges_id');
}

Once you get the relationship working right, you can simply use the collection returned from the relationship. Once you pass the $user->joined_challenges to your view, you can do as below

@foreach ($joined_challenges as $challenge)
    <p>{{$challenge->hashtag}}</p>   
    <p>{{$challenge->title}}</p>   
@endforeach

Let me know it this works, cheers! ;)

Laravel Join Tables with intermediate relations

$contents = RecentView::where('recent_views.user_id', $loggedUser)
->leftJoin('feed_posts','recent_views.post_id','=','feed_posts.id')
->leftJoin('users','feed_posts.user_id','=','users.id')
->paginate(12)->toArray();

Just check the table name and the rest

---

Related Topics