Laravel Query Builder error with joins and parameter grouping

Question

Have a set of queries I would like to convert from raw to query builder but having issues when using parameter grouping:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.trail_ends_at' in 'where clause' (SQL: select 'campaigns'.* from 'users' inner join 'campaigns` on 'users'.'id' = 'campaigns'.'user_id' inner join 'subscriptions' on 'users'.'id' = 'subscriptions'.'user_id' where ('users'.'trial_ends_at >= 2018-06-15 00:00:00 or 'users'.'trail_ends_at' is null) and 'campaign'.'active' = 1 and 'campaign'.'style' = monthly and ('subscriptions'.'trial_ends_at' >= 2018-06-15 00:00:00 or 'subscriptions'.'trail_ends_at' is null) and ('subscriptions'.'ends_at' >= 2018-06-15 00:00:00 or 'subscriptions'.'ends_at' is null))

SQL query that works:

DB::select('select c.* 
FROM  users u, subscriptions s, campaigns c
WHERE   c.user_id = u.id
AND s.user_id = u.id
AND (u.trial_ends_at IS NULL OR u.trial_ends_at >= CURDATE() + :leadDays1)
AND c.active > 0
AND c.style = \'monthly\'
AND (s.ends_at IS NULL OR s.ends_at >= CURDATE() + :leadDays2)
AND (s.trial_ends_at IS NULL OR s.trial_ends_at >= CURDATE() + :leadDays3)
AND c.send_on <= CURDATE() + :leadDays4
AND MONTH(c.send_on) = MONTH(CURDATE() + :leadDays5),
AND DAYOFMONTH(c.send_on) = DAYOFMONTH(CURDATE() + :leadDays6)',
     [
       'leadDays1' => $leadDays,
       'leadDays2' => $leadDays,
       'leadDays3' => $leadDays,
       'leadDays4' => $leadDays,
       'leadDays5' => $leadDays,
       'leadDays6' => $leadDays
      ]
 );

Builder query that errors:

$checkDate = Carbon::today()->addDays($leadDays);

    return $campaigns = DB::table('users')
    ->join('campaigns', 'users.id', '=', 'campaigns.user_id')
    ->join('subscriptions', 'users.id', '=', 'subscriptions.user_id')
    ->select('campaigns.*')
    ->where(function ($query) use ($checkDate) {
        return $query->where('users.trial_ends_at', '>=', $checkDate)
        ->orWhereNull('users.trail_ends_at');
      })
    ->where('campaign.active', '=', 1)
    ->where( 'campaign.style', '=', $style)
    ->where(function ($query) use ($checkDate) 
        {
        return $query->where('subscriptions.trial_ends_at', '>=', $checkDate)
    ->orWhereNull('subscriptions.trail_ends_at');
       })
    ->where(function ($query) use ($checkDate) {
        return $query->where('subscriptions.ends_at', '>=', $checkDate)
    ->orWhereNull('subscriptions.ends_at');
       });

I guess in inner where calls your don't need an array of arguments just use as $query->where('subscriptions.trial_ends_at', '>=', $checkDate) — M Khalid Junaid

Gvidon Shmavonyan Gvidon Shmavonyan · Accepted Answer · 2018-06-11T16:26:22

Check your column names. I think trail_ends_at should be trial_ends_at.

Laravel Query Builder error with joins and parameter grouping

1 Answers