Maneesh Rao
Maneesh Rao

Reputation: 184

Covert MySql query to Laravel

Can you please help me out to write the Laravel Raw Query for below Mysql query.

SELECT 
    SUM(IF(d.business_email_template_10_open = "Yes", 1,0)) AS `business_email_template_10_open`,
    SUM(IF(d.business_email_template_11_open = "Yes", 1,0)) AS `business_email_template_11_open`
   FROM dummy_email_track d 
       join recommend_email_send_by_cron r on d.user_id = r.user_id
       join user_form_submission u on r.user_id = u.id'
  where d.business_id = $businessId

Upvotes: 0

Views: 30

Answers (3)

vikalp
vikalp

Reputation: 330

Please try this :

$data = DB::table('dummy_email_track as d')
        ->select('d.*','r.*','u.*')
        ->selectRaw('SUM(IF(d.business_email_template_10_open = "Yes", 1,0)) AS business_email_template_10_open')
        ->selectRaw('SUM(IF(d.business_email_template_11_open = "Yes", 1,0)) AS `business_email_template_11_open`')
        ->leftJoin('recommend_email_send_by_cron as r','d.user_id', '=', 'r.user_id')
        ->leftJoin('user_form_submission as u', 'r.user_id', '=', 'u.id')
        ->where('d.business_id','=', $businessId)
        ->get();

Upvotes: 0

Koesnoom
Koesnoom

Reputation: 30

If you can't use stored procedure, ignore my answer.

I think you can't use Laravel QueryBuilder cause something reason(I have a similar situation). How about using SP(stored procedure)?

Mysql SP

CREATE DEFINER=`your_mysql_id`@`%` PROCEDURE `get_usage_business_email_template`()
__SP:BEGIN
    BEGIN
        SELECT
            SUM(IF(d.business_email_template_10_open = "Yes", 1,0)) AS `business_email_template_10_open`,
            SUM(IF(d.business_email_template_11_open = "Yes", 1,0)) AS `business_email_template_11_open`
        FROM dummy_email_track d 
            join recommend_email_send_by_cron r on d.user_id = r.user_id
            join user_form_submission u on r.user_id = u.id'
        WHERE d.business_id = $businessId
    END;
END

And, Laravel call this SP like below

$result = DB::select('CALL get_usage_business_email_template()');

I hope you will save your time becasue my answer...

Upvotes: 1

Jasper Helmich
Jasper Helmich

Reputation: 751

This should give you a headstart!

https://laravel.com/docs/5.6/database#running-queries

$results = DB::select('select * from users where id = :id', ['id' => 1]);

Make sure you give right id to your where clause. So in you case it would something like this:

(where d.business_id = :businessId, ['businessId' => 1])

Goodluck!

Upvotes: 0

Related Questions