How to convert mysql query to laravel query builder

Question

Is there any way to convert following query to laravel query builder?

select `employee_id`
from `otc_employee_qualifications` 
where `emp_qualifctn_type` IN ('29','27') 
group by `employee_id`
having count(Distinct `emp_qualifctn_type`) = 2

do u need to know how to write query for laravel or you want to write code to automatically create query for laravel from given query?? — PRANAV
– PRANAV, Commented Nov 20, 2015 at 9:10
i want to just convert that query to laravel query builder. thats all. — Jishad P
– Jishad P, Commented Nov 20, 2015 at 9:12
What you have tried so far? Post your attempts. If you simply tried to search Laravel Query Builder then you simply get the docs too — Narendrasingh Sisodia
– Narendrasingh Sisodia, Commented Nov 20, 2015 at 9:14

AnkiiG · Accepted Answer · 2015-11-20 09:56:10Z

3

Try as below :

$users = DB::table('otc_employee_qualifications')
         ->select('employee_id')
         ->whereIn('emp_qualifctn_type', [27,29])
         ->groupBy('employee_id')
         ->having(DB::raw("count(Distinct emp_qualifctn_type)"), '=', 2)
         ->get();

edited Nov 20, 2015 at 9:56

answered Nov 20, 2015 at 9:14

AnkiiG

3,4861 gold badge19 silver badges28 bronze badges

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

11 Comments

Jishad P Over a year ago

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'employee_id,DB::raw("distinct' in 'field list' (SQL: select employee_id,DB::raw("distinct as as from otc_employee_qualifications where emp_qualifctn_type in (27, 29) group by employee_id having counter = 2)

Narendrasingh Sisodia Over a year ago

Convert ->select('employee_id,DB::raw("distinct emp_qualifctn_type as counter')) into ->select('employee_id',DB::raw("distinct emp_qualifctn_type as counter"))

Narendrasingh Sisodia Over a year ago

You're Welcome. @AG21

Jishad P Over a year ago

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct emp_qualifctn_type as counter from otc_employee_qualifications where ' at line 1 (SQL: select employee_id, distinct emp_qualifctn_type as counter from otc_employee_qualifications where emp_qualifctn_type in (27, 29) group by employee_id having emp_qualifctn_type = 2)

AnkiiG Over a year ago

@JishadP please check your having clause

|

rdanusha · Accepted Answer · 2017-10-20 11:43:31Z

-2

Answer:

  DB::select('employee_id')
        ->from('otc_employee_qualifications')
        ->whereIn('emp_qualifctn_type', ('29', '27'))
        ->groupBy('employee_id')
        ->having(DB::raw('count(Distinct emp_qualifctn_type)'), '=', 2)
        ->get();

You can convert SQL query into laravel eloquent query by using bellow website.

This will convert SQL query to laravel eloquent base query

Convert SQL query to Eloquent base query

edited Oct 20, 2017 at 11:43

answered Oct 20, 2017 at 8:23

rdanusha

9333 gold badges16 silver badges25 bronze badges

1 Comment

YOG_PHP Over a year ago

how to convert, this query to laravel "select * from officer_availabilities where officer_id = 52 and 1 BETWEEN start_day AND end_day"

Collectives™ on Stack Overflow

How to convert mysql query to laravel query builder

2 Answers 2

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related