Query Laravel Select Where Array

Question

I have a users table. Inside there is a column called city_id. Inside the city_id column data are stored like this :for ex:["12,54,87,18"] Now I want to select user/users whose city_id exists or is equal to the $postcity variable. for ex:

$postcity = 54;

my code for select:

$usermatchings = DB::table('users')
                                    
                ->Where('city_id', $postcity )
                                
                ->get();
                foreach ($usermatchings as $usermatching) {
                $recipients = $usermatching->phone; 
                }
                dd($recipients);

but not working... please guide me.thank you

You should consider reorganizing structure by making cities table and associating manyToMany relations (along with city_user table) where you would store current relations. It would speed up things a lot. — Tpojka
– Tpojka, Commented Dec 25, 2020 at 22:12

Donkarnash · Accepted Answer · 2020-12-25 15:03:28Z

1

Assuming:

datatype or the city_id column is json
cast is defined on the model
not using sqlite as database


class User extends Model
{
    protected $casts = ['city_id' => 'array'];
}

You can use whereJsonContains

$usermatchings = DB::table('users')
    ->whereJsonContains('city_id', $postcity)
    ->get();

//To get an array of all phone (numbers)
$recepients = $usermatchings->pluck('phone')->all();

Laravel docs: https://laravel.com/docs/8.x/queries#json-where-clauses

edited Dec 25, 2020 at 15:03

answered Dec 25, 2020 at 14:57

Donkarnash

12.9k5 gold badges30 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Amin Ebrahimzadeh Over a year ago

Yep! great, thank you. but Dump just one user`s phone (while I have many users equal this conditionals)

Donkarnash Over a year ago

pluck() method is used on collection returned by the get() on query. So it will pluck the values for phone from all elements of collection and return a collection of just the values of phone. laravel.com/docs/8.x/collections#method-pluck

Amin Ebrahimzadeh Over a year ago

I send $recipients to Twilio function for send sms; return ($this->sendMessageAmin($body, $recipients)); but get error:

Argument 1 passed to Twilio\Rest\Api\V2010\Account\MessageList::create() must be of the type string, array given, called in

Amin Ebrahimzadeh Over a year ago

this my function ` private function sendMessageAmin($message, $recipients) { $account_sid = '****'; $auth_token = '******'; $twilio_number = '*******'; $client = new Client($account_sid, $auth_token); $client->messages->create($recipients, ['from' => $twilio_number, 'body' => $message]); }`

Donkarnash Over a year ago

Am not aware of the Twilio api, but if you need to send the recepients as string of comma separated values, you can use join() on collection instead of using all() to convert to array like $recepients = $usermatchings->pluck('phone')->join(',');

|

BABAK ASHRAFI · Accepted Answer · 2020-12-25 18:04:40Z

1

You can use like as stated in laravel query builder doc. Below code may help you:

$usermatchings = DB::table('users')                      
    ->whereRaw('FIND_IN_SET(?,city_id)', [$postcity])
    ->get();

Notice: If you wanna save the result in an array, you should aggregate it, not override it.

foreach ($usermatchings as $usermatching) {
    $recipients[] = $usermatching->phone; 
}

edited Dec 25, 2020 at 18:04

answered Dec 25, 2020 at 14:56

BABAK ASHRAFI

4,3114 gold badges26 silver badges35 bronze badges

1 Comment

Tpojka Over a year ago

This is not well solution and won't be reliable since it will return rows that contain ["540", "541", "542", "1054"] etc.

Collectives™ on Stack Overflow

Query Laravel Select Where Array

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related