CODEWITHSUNDEEP
phpsqllaravellaravel-5.4
Patrick Obafemi
Patrick Obafemi

Reputation: 1066

Laravel Query builder how to check if an item appears twice

Okay I want to use Laravel Query builder to check if a particular user's name appears twice in a column. For example in my program when a new user registers he is randomly assigned to a user as a child of that user. So the new user will have a field like parent_id . A user can not have more than two children so I want to query my database using the query builder to get any random user who's ID does not appear twice in the parent_id column

Upvotes: 1

Views: 1979

Answers (3)

Meera Tank
Meera Tank

Reputation: 719

Try these 

User::select('id', DB::raw('COUNT(*) as count'))
    ->groupBy('parent_id')
    ->having('count', '<' , 2)
    ->inRandomOrder()->first();

or Shorter way

User::select('id')->groupBy('parent_id')->havingRaw('COUNT(*) < 2')->inRandomOrder()->first();

Here you will get parent_id in random manner whose appearance in DB as parent_id is less than 2 Times

Hope this will help you.

Upvotes: 2

Ragas
Ragas

Reputation: 3205

User::where('parent_id', $someId)->count();

I think this is the query you are looking for. It returns the number of times an id is used as parent_id. Then you can use if statement to check if it is less than two or not.

You can use loop and random number generator to get the random $someId.

Upvotes: 1

Suniti Yadav
Suniti Yadav

Reputation: 403

This is my suggestion , instead of finding out through query builder its better to make 'parent_id' column as unique as well as you can do validation in function as - 

'parent_id' => 'required|unique:users'

Hope this will help you.

Upvotes: 0

Related Questions