Ad

JOIN Replaces The Original Id With The Id Of Joined Table In Laravel

I got four tables

  1. Links (id, user_id, link_data, category_id, submit_date)
  2. Users (id, username, password, status)
  3. Categories (id, name, slug)
  4. Tags (id, link_id, tags)

I want to show all link details with username, Category name & slug and tags but with join it replaces the links.id with the category.id.

$catid = Category::where('slug', '=', $slug)->firstOrFail()->id;

$linksbycategories = DB::table('links')
->where('category_id', '=',$catid)
->join('users', 'users.id', '=', 'links.user_id')
->join('categories', 'categories.id', '=', 'links.category_id')
->join('tags', 'tags.link_id', '=', 'links.id')->get();
return $linksbycategories;
//return view('category', ['linksbycategories' => $linksbycategories]);

I have 3 tags of link id 1 and the result I get is 3 times data of first link information with link id as 7 which is the category id of that link. How can I solve this.

Ad

Answer

please add below code into link model:

public function User()
{
    return $this->hasOne('App\User','id','user_id');
}
public function Category()
{
    return $this->hasOne('App\Category','id','category_id');
}
public function Tags()
{
    return $this->hasMany('App\Tags ','link_id','id');
}

and also make changes into query as below:

$linksbycategories = App\Link::with('User','Category','Tags')
->whereHas('Category',function($q) use($slug){
$q->where('slug', '=', $slug);
})->get();
Ad
source: stackoverflow.com
Ad