Convert SQL query to Eloquent Laravel

Question

I have a sql query:

select `id` from `users` 
where (
        select count(*) 
        from `user_event` as `uev` 
        where `uev`.`leader_id` = `users`.`id`
      ) > 1

How can I convert it to Eloquent Laravel?

lukasgeiter · Accepted Answer · 2015-01-20 15:56:38Z

3

Assuming you have set up the relationship you can use the has() method for that:

$users = User::select('id')->has('events', '>', 1)->get();

If you want an array of users ids (since you're only selecting the id) you can also use lists():

$ids = User::has('events', '>', 1)->lists('id');

Since you asked, this would be an alternative method (not tested though)

User::where(DB::raw('1'), '<', function($q){
    $q->from('user_event')
      ->where('user_event.leader_id', 'users.id');
})->get();

edited Jan 20, 2015 at 15:56

answered Jan 20, 2015 at 15:34

lukasgeiter

154k29 gold badges349 silver badges280 bronze badges

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

5 Comments

Joshua Hansen Over a year ago

Awesome. Thank you very much !!!. Assuming I don't use has. Do you have any other ways?

lukasgeiter Over a year ago

You're welcome. Maybe with some raw queries, but that's not really Eloquent anymore ;) I see no reason why not to use has if you're using Eloquent anyways..

Joshua Hansen Over a year ago

I am studying Query Builder and Eloquent so can you give me the raw query to do it. Thanks again for your help.

Joshua Hansen Over a year ago

I got a error: QLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

lukasgeiter Over a year ago

Hmm. Okay, then it probably doesn't work that way... If you figure something out feel free to suggest an edit

Collectives™ on Stack Overflow

Convert SQL query to Eloquent Laravel

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related