SQLSTATE[42S22]: Column not found: 1054 Unknown column 'p.id' in 'field list'

Question

I am trying to execute this query in laravel and it give me the

error:SQLSTATE[42S22]: Column not found: 1054 Unknown column 'p.id' in 'field list' (SQL: select `p`.`id` as `post_id`, `title`, `post_content`, `image_dir`, `video_dir`, `p`.`created_at` as `post_date`, `first_name`, `last_name`, `profile_img` from `users` as `u,posts` where `title` like %mo% or `post_content` like %mo% order by `p`.`created_at` desc)

$posts= DB::table('users AS u,posts AS p')
    ->select('p.id as post_id','title','post_content','image_dir','video_dir','p.created_at as post_date','first_name','last_name','profile_img')
    ->where('title', 'like', "%{$key}%")
    ->orWhere('post_content', 'like', "%{$key}%")
    ->orderBy('p.created_at', 'desc')
    ->get();

my posts table

Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->unsignedBigInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users');
            $table->text('post_content')->nullable();
            $table->double('likes')->default(0);
            $table->double('dislikes')->default(0);
            $table->integer('size')->nullable();
            $table->string('image_dir')->nullable();
            $table->string('video_dir')->nullable();
            $table->timestamps();
        });

my user table

Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('first_name');
            $table->string('last_name');
            $table->string('gender');
            $table->string('phone_number')->nullable();
            $table->string('location')->nullable();
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->string('profile_img')->nullable();
            $table->string('cover_img')->nullable();
            $table->rememberToken();
            $table->timestamps();

You have to select one table. Tell me what are you going to do and I can help you. — Nikita
As you can see the fragment AS p in DB::table('users AS u,posts AS p') was ignored. Maybe simply do not use an alias for posts? or try to use CROSS JOIN instead of comma. — Akina
I want to retrieve the post date from the post table and use the foreign key user_id to retrieve the user name and profile image of the user who posted the post from users table and send it with the rest of post data as one record. — mohammad abd elhady

steven7mwesigwa steven7mwesigwa · Accepted Answer · 2021-05-13T20:37:11

It appears like you need a join.

Inner Join clause

$posts= DB::table('users AS u')
    ->join('posts As p', 'u.id', '=', 'p.user_id' )
    ->select('p.id as post_id','title','post_content','image_dir','video_dir','p.created_at as post_date','first_name','last_name','profile_img')
    ->where('title', 'like', "%{$key}%")
    ->orWhere('post_content', 'like', "%{$key}%")
    ->orderBy('p.created_at', 'desc')
    ->get();

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'p.id' in 'field list'

1 Answers