srle90's avatar

Ordering Data by Clicking Column Titles

class PostController extends Controller { public function index() { $orderColumn = request('order_column', 'inspection_date'); if (!in_array($orderColumn, ['no', 'inspection_date'])) { $orderColumn = 'inspection_date'; } $orderDirection = request('order_direction', 'desc'); if (!in_array($orderDirection, ['asc', 'desc'])) { $orderDirection = 'desc'; }

    $posts = Post::with(['lastHistory'])
        ->when(request('category'), function(Builder $query) {
            $query->where('division', request('category'));
        })
        ->orderBy($orderColumn, $orderDirection)
        ->paginate(10);
    return PostResource::collection($posts);
}

}

"message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'inspection_date' in 'order clause' (Connection: mysql, SQL: select * from `posts` order by `inspection_date` desc limit 10 offset 0)",

How can I orderBy with inspection_date which came from

class Post extends Model { use HasFactory;

protected $fillable = ['no','division'];

public function lastHistory()
{
    return $this->hasOne(History::class, 'post_id')
        ->orderBy('post_id','asc')
        ->orderBy('inspection_date', 'desc');
}

}

db

public function up(): void
{
    Schema::create('posts', function (Blueprint $table) {
        $table->id();
        $table->integer('no');
        $table->string('division');
        $table->timestamps();
    });
}

/**
 * Reverse the migrations.
 */
public function down(): void
{
    Schema::dropIfExists('posts');
}

};

return new class extends Migration { /** * Run the migrations. */ public function up(): void { Schema::create('histories', function (Blueprint $table) { $table->id(); $table->foreignId('post_id')->constrained(); $table->longText('content'); $table->date('inspection_date'); }); }

/**
 * Reverse the migrations.
 */
public function down(): void
{
    Schema::dropIfExists('histories');
}

};

0 likes
5 replies
tangtang's avatar

@srle90

Unknown column inspection_date in order clause, is because the inspection_date column is defined in the histories table, not the posts table. You're trying to order the posts table by a column that it doesn't have, which is why you're getting this error.

To order the posts table by the inspection_date column from the related histories table, you should modify your query to join the histories table and then order by inspection_date.

this code as reference

class PostController extends Controller {
    public function index() {
        $orderColumn = request('order_column', 'inspection_date');
        if (!in_array($orderColumn, ['no', 'inspection_date'])) {
            $orderColumn = 'inspection_date';
        }
        $orderDirection = request('order_direction', 'desc');
        if (!in_array($orderDirection, ['asc', 'desc'])) {
            $orderDirection = 'desc';
        }

        $posts = Post::with(['lastHistory'])
            ->leftJoin('histories', 'posts.id', '=', 'histories.post_id')
            ->when(request('category'), function($query) {
                $query->where('division', request('category'));
            })
            ->orderBy('histories.'  .  $orderColumn, $orderDirection)
            ->select('posts.*')
            ->paginate(10);

        return PostResource::collection($posts);
    }
}
srle90's avatar

@tangtang

Ok I assume that I need to use some join but how to fetch only those lastHistory records public function lastHistory() { return $this->hasOne(History::class, 'post_id') ->orderBy('post_id','asc') ->orderBy('inspection_date', 'desc'); }

tangtang's avatar

@srle90

you can use a subquery in your lastHistory relationship. may you modify the lastHistory method in your Post model like this

public function lastHistory()
{
    return $this->hasOne(History::class, 'post_id')
        ->select('histories.*')
        ->orderBy('post_id', 'asc')
        ->orderBy('inspection_date', 'desc')
        ->latest()
        ->limit(1);
}
tangtang's avatar

@srle90

not really good way, but I suggest using if else condition

this code as reference

use Illuminate\Database\Eloquent\Builder;

class PostController extends Controller {
    public function index() {
        $fetchMethod = request('fetch_method', 'hasOne'); // Default to 'hasOne'
        $orderColumn = request('order_column', 'inspection_date');
        if (!in_array($orderColumn, ['no', 'inspection_date'])) {
            $orderColumn = 'inspection_date';
        }
        $orderDirection = request('order_direction', 'desc');
        if (!in_array($orderDirection, ['asc', 'desc'])) {
            $orderDirection = 'desc';
        }

        if ($fetchMethod === 'join') {
            // Use a join to fetch records
            $posts = Post::with(['lastHistory'])
                ->when(request('category'), function(Builder $query) {
                    $query->where('division', request('category'));
                })
                ->leftJoin('histories', 'posts.id', '=', 'histories.post_id')
                ->select('posts.*')
                ->orderBy("histories.$orderColumn", $orderDirection)
                ->paginate(10);
        } else {
            // Use hasOne to fetch records
            $posts = Post::with(['lastHistory'])
                ->when(request('category'), function(Builder $query) {
                    $query->where('division', request('category'));
                })
                ->orderBy('id', 'desc') // Order by post ID in descending order
                ->paginate(10);
        }

        return PostResource::collection($posts);
    }
}

with this code you need to pass the fetch_method to as paramete to controller

may the parameter like this

/posts?order_direction=desc&orderColumn=inspection_date&fetch_method=join

Please or to participate in this conversation.