JOIN replaces the original id with the id of joined table in Laravel

using Eloquent gets around this problem of having colliding column names (it will happen for any column with the same name)

Using joins, you have to also consider using Selects and alias the columns with as just as you would with raw database queries.

https://laravel.com/docs/5.8/queries#selects

With using select I am getting this error.

"SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in field list is ambiguous (SQL: select `id` as `link_id`, `user_id` as `link_user_id`, `link` as `link_link`, `title` as `link_title`, `description` as `link_description`, `submitted_at`, `category_id` as `link_category_id`, `votes a link_votes` from `links` inner join `users` on `users`.`id` = `links`.`user_id` inner join `tags` on `tags`.`link_id` = `links`.`id` inner join `categories` on `categories`.`id` = `links`.`category_id` where `category_id` = 7)

this is the code

$linksbycategories = DB::table('links')
->select('id as link_id','user_id as link_user_id', 'link as link_link', 'title as link_title', 'description as link_description', 'submitted_at', 'category_id as link_category_id', 'votes a link_votes')
->where('category_id', '=',$catid)
->join('users', 'users.id', '=', 'links.user_id')
->join('tags', 'tags.link_id', '=', 'links.id')
->join('categories', 'categories.id', '=', 'links.category_id')->get();

You need to specify the table name, eg

->select('links.id as link_id','links.user_id as link_user_id',

otherwise the columns are ambiguous, ie you say alias the id column, but the database does not know which of the three you mean

With table names the query worked but now the JOIN data won;t show

->join('users', 'users.id', '=', 'links.user_id')
->join('tags', 'tags.link_id', '=', 'links.id')
->join('categories', 'categories.id', '=', 'links.category_id')

contrast with

$links = Links::whereHas('categories', $catid)
                 ->with(['categories','tags','users'])->get();

"Argument 2 passed to Illuminate\Database\Eloquent\Builder::whereHas() must be an instance of Closure or null, int given, called in D:\xampp\htdocs\bookmarking\vendor\laravel\framework\src\Illuminate\Support\Traits\ForwardsCalls.php on line 23

error on link

 public function whereHas($relation, Closure $callback = null, $operator = '>=', $count = 1)

sorry, whereHas needs a callback to evaluate the column of categories

$links = Links::whereHas('categories', function ($query) use ($catid) {
                 $query->where('id', '$catid');
                 })->with(['categories','tags','users'])->get();

Error Call to undefined method App\Links::categories()

})->with(['categories','tags','users'])->get();

These are my table names, do we need to use Model names or something else?

You need to define the categories relationship on your link Model.

function categorises(){ return $this->belongsTo(Category::class); }

I already have it in my Link Model

public function Category()
    {
        return $this->belongsTo('App\Category', 'category_id');
    }

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

Category Model

public function Link()
    {
        return $this->hasMany('App\Link', 'category_id');
    }

Tag Model

public function Link()
    {
        return $this->belongsTo('App\Link', 'id');
    }

User Model

public function user()
    {
        return $this->hasOne(Profile::class);
    }
    
    public function Link()
    {
        return $this->hasMany('App\Link','id');
    }

you need to use the relationship names.

Youwill find it less confusing in the long term if you stick with conventions.

Use capital letter for class names and camel case for method names

Thanks I'll remember it.

I got the code working with this

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

But now I am stuck with it's pagination.

{{$data->links()}}

won't work.

because you have to use paginate and not get

$linksbycategories = \App\Link::with('User','Category','Tags')
->whereHas('Category',function($q) use($slug){
$q->where('slug', '=', $slug);
})->paginate();