0

I want to add DB::raw() with specific where clause to existing query.

I have this Query:

    protected static $type = 
    [
        'daily' => 1,
        'weekly' => 2,
        'montly' => 3,
    ];

    $revenuedetails = DB::table("users")
        ->select("users.username", "users.email",DB::raw("DATE(users.created_at) as subscription_date"))
        ->where('users.plan', self::$type[$plan] ?? null);

But I want to add this:

DB::raw("DATE(users.created_at) as expired_date"))
where('users.plan_status', 0)

It whould be immediately beside

DB::raw("DATE(users.created_at) as subscription_date"))

Hence the the structure will now be

username | email | subscription_date | expired_date

I want to add

DB::raw("DATE(users.created_at) as expired_date"))

with its own specific where clause

where('users.plan_status', 0)

to the existing query.

Please note that

where('users.plan', self::$type[$plan] ?? null)

is the overall where clause.

Then end result should be

username | email | subscription_date | expired_date

user11352561
  • 2,277
  • 12
  • 51
  • 102

2 Answers2

1

If I'm understanding you correctly, if the plan status = 0 then you want to use the created_at date as the expiration date? If not, then expired_date should be null?

If so, you can accomplish this with a case statement, like this ...

DB::raw(" CASE WHEN users.plan_status = 0 THEN DATE(users.created_at) ELSE null END AS expired_date ")
AndyChern
  • 336
  • 1
  • 5
0

Hi If I understood good you want to use a where on your alias created so you can use "having()" instead of where as follows:

$revenuedetails = DB::table("users")
    ->select("users.username", "users.email",DB::raw("DATE(users.created_at) as subscription_date"), DB::raw("DATE(users.created_at) as expired_date"))
    ->having("subscription_date", "operador", "Value_condition")
    ->having("expired_date", "operador", "Value_condition")
    ->where('users.plan', self::$type[$plan] ?? null);

Also you can check the below reference:

Can you use an alias in the WHERE clause in mysql?

I hope I've helped you

Regards

Daniel Luna
  • 341
  • 2
  • 9
  • I said DB::raw("DATE(users.created_at) as expired_date")) will only be valid where where('users.plan_status', 0).It is not for the general query. Hence when plan_status is 0 then it has expired. So it does not affect other fields. – user11352561 Jun 06 '19 at 17:02
  • Ok I understood, the solution provided by AndyChern will works for you – Daniel Luna Jun 07 '19 at 13:58