CODEWITHSUNDEEP
phpmysqlsqllaraveleloquent
fourzero
fourzero

Reputation: 61

Comparing multiple database row values with Laravel Eloquent / MySQL

I'm using Laravel for my project and have a MySQL table as follows:

| User_id | technology | years_experience |
| 1       | PHP        |  4               |
| 2       | AngularJS  |  2               |
| 1       | HTML       |  2               |

At the moment I'm using a standard where query which only supports one criteria

Table::where([
['technology', '=', 'PHP'],
['years_experience', '>', 2]
])->get()->pluck('user_id');

How can I filter and group results by User_id if I wanted to retrieve all User_ids with PHP > 2 AND HTML > 1?

Upvotes: 2

Views: 1501

Answers (3)

fourzero
fourzero

Reputation: 61

The issue with using WHERE / AND is that it does not compare values on a row level but searches for matching values across columns

The "hack" is to use HAVING(). Here's the working solution:

Table::groupBy('user_id')
->havingRaw("sum(tech='PHP' AND experience>=2) > 0")
->havingRaw("sum(tech='HTML' AND experience>=1) > 0")
->get()
->pluck('user_id')

Upvotes: 1

Rutvij Kothari
Rutvij Kothari

Reputation: 1285

Question is (condition1 AND condition2) AND (condition3 AND condition4)

This is how you can do it:

Table::where(function($query){
    $query->where('technology', '=', 'PHP')->where('years_experience', '>', 2);
})->where(function($query) {
    $query->where('technology', '=', 'HTML')->where('years_experience', '=', 1);
})->get()->pluck('user_id');

Upvotes: 0

Aron Quiray
Aron Quiray

Reputation: 26

haven't tested this, but ideally i guess this is what you need

Table::where('technology', '=', 'PHP')->andWhere('years_experience', '>', 2)->get()->pluck('user_id')->andWhere('user_id','=',ANYUSERID)->groupBy('user_id')->get(['user_id','technology','years_experience']);

Upvotes: 0

Related Questions