How can I find duplicate phones from json column with whereIn in Laravel. Below is my code?
<code> // Orders table migration
$table->json("recipient")->nullable();
// Data castings in Order Model
protected $casts = [
"recipient" => "array"
];
// Order created
$order = Order::create([
"recipient" => [
"phone" => "1234424532",
"email" => "[email protected]"
],
"status" => "completed"
]);
// Try to retrieve the recipients with duplicate phone numbers
$query = Order::query();
return $query->select('id', 'recipient')
->whereNotNull('recipient')
->whereIn('recipient->phone', function ($subQuery) {
$subQuery->select('recipient->phone AS phone')
->from('orders')
->groupBy('phone')
->havingRaw('COUNT(*) > 1');
})->paginate();
</code>
<code> // Orders table migration
$table->json("recipient")->nullable();
// Data castings in Order Model
protected $casts = [
"recipient" => "array"
];
// Order created
$order = Order::create([
"recipient" => [
"phone" => "1234424532",
"email" => "[email protected]"
],
"status" => "completed"
]);
// Try to retrieve the recipients with duplicate phone numbers
$query = Order::query();
return $query->select('id', 'recipient')
->whereNotNull('recipient')
->whereIn('recipient->phone', function ($subQuery) {
$subQuery->select('recipient->phone AS phone')
->from('orders')
->groupBy('phone')
->havingRaw('COUNT(*) > 1');
})->paginate();
</code>
// Orders table migration
$table->json("recipient")->nullable();
// Data castings in Order Model
protected $casts = [
"recipient" => "array"
];
// Order created
$order = Order::create([
"recipient" => [
"phone" => "1234424532",
"email" => "[email protected]"
],
"status" => "completed"
]);
// Try to retrieve the recipients with duplicate phone numbers
$query = Order::query();
return $query->select('id', 'recipient')
->whereNotNull('recipient')
->whereIn('recipient->phone', function ($subQuery) {
$subQuery->select('recipient->phone AS phone')
->from('orders')
->groupBy('phone')
->havingRaw('COUNT(*) > 1');
})->paginate();
It did not work. Any helps will be appreciated. Thanks in advance