Jackksparrow
Schema::create('user_preferences', function (Blueprint $table) {
$table->id();
$table->integer('user_id')->references('id')->on('user')->onDelete('cascade');
$table->jsonb('positions_desired_to_work')->nullable();
$table->timestamps();
});
DB::table('user_preferences')->insert([
'user_id' => 1,
'positions_desired_to_work' => json_encode(["1","2","3"])
]);
$categoryId = 1;
$results = DB::table('users')
->whereExists(function ($query) use ($categoryId ) {
$query
->select('*')
->from('user_preferences')
->whereColumn('user_preferences.user_id', 'users.id')
->whereJsonContains('positions_desired_to_work' , "{$categoryId}");
})
->get();
şeklinde sonuç aldım. Üstteki sorgunun çıktısı aynen şu, sizinki ile aynı:
SELECT *
FROM `users`
WHERE EXISTS (SELECT *
FROM `user_preferences`
WHERE `user_preferences`.`user_id` = `users`.`id`
AND Json_contains(`positions_desired_to_work`, '"1"'))