How to join two tables and get id from first table in Laravel

I'm trying to join posts and tags tables and get id of posts table, but this code giving me id (11) of tags table, but the post id is 15.

result

$posts = Post::leftJoin('tags', 'tags.post_id', '=', 'posts.id')
                ->where('tags.slug', $slug)->get();

 dd($posts);

Table tags

Schema::create('tags', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedBigInteger('post_id');
    $table->string('tag');
    $table->string('slug');
    $table->integer('views')->nullable();
    $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
});

Table posts

Schema::create('posts', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->unsignedBigInteger('category_id');
    $table->unsignedBigInteger('admin_id')->nullable();
    $table->string('title');
    $table->string('slug')->unique();
    $table->integer('views')->default(0);
    $table->longText('content');
    $table->text('meta_keywords')->nullable();
    $table->text('meta_description')->nullable();
    $table->enum('is_home', ['0', '1',])->default('1');
    $table->enum('is_featured', ['0', '1',])->default('0');
    $table->enum('is_slider', ['0', '1',])->default('0');
    $table->integer('slider_order')->default(0);
    $table->enum('type', ['Article', 'Video']);
    $table->enum('status', ['Visible', 'Invisible', 'Draft', 'Pending']);
    $table->foreign('category_id')->references('id')->on('categories');
    $table->foreign('admin_id')->references('id')->on('admins');
    $table->timestamps();
});

Model tag

namespace App;

use Illuminate\Database\Eloquent\Model;

class Tag extends Model
{
    protected $fillable = [
        'post_id', 'tag', 'slug',
    ];

    public $timestamps = false;
}

Model posts

namespace App;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    public function tags()
    {
        return $this->hasMany(Tag::class);
    }
}

1 answer

  • answered 2019-10-08 05:30 jithesh jose

    Try

     $posts = Post::leftJoin('tags', 'tags.post_id', '=', 'posts.id')
               ->select('tags.id as tag_id','posts.*')
               ->where('tags.slug','=', $slug)
               ->get();
    

    Now $posts->id will be id of posts table.$posts->tag_id will be the id of tags table.