Level 2
is it "trial" or "trail" on your users column??.
if its correct, can we see your migration or database schema ??
1 like
Jun 11, 2018
4
Level 11
Laravel Query Builder error column not found with joins and parameter grouping
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))
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
]
);
Query builder I get the error with:
$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');
});
Thank you in advance!
Level 67
Why do you think there is a benefit to converting all of that working sql to "eloquent"? Just use it as is. It's much more efficient than going through eloquent, which then has to translate it all back to raw sql to execute, which is what you already have in the first place. You're already using query binding, so it's safe, too. Look at how much time gets wasted trying to get it to work. You're really not gaining anything here, but are definitely losing some things.
1 like
Level 11
@Cronix thank you, I actually agree but not really my call in the project and beside I was trying to learn something. Sorry if that is noise.
Level 16
I'm having a similar issue now and actually had the whole thing in SQL but I found what we lost was the global scopes and the ability to easily page. So I converted it back to eloquent but now getting this Column not found when trying to call having on a column that was definitely selected. Here's the section from my filter method.
if (request()->rentBalance && !empty(request()->rentBalance)) {
$rentBalanceOperator = request()->rentBalanceOperator ? request()->rentBalanceOperator : '>';
$query->havingRaw("tenant_rent_balance {$rentBalanceOperator} ?", [request()->rentBalance]);
}
and here's the column being selected in the query section
Lease::select([
...,
...,
...,
DB::raw(
"(
SELECT SUM(cat.balance) FROM categories cat WHERE cat.account_id = tenant_account.id AND cat.type = 'rent') AS tenant_rent_balance"
),
])
Keep getting: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tenant_rent_balance' in 'having clause'
When I do the having on the raw SQL in workbench it works just fine.
Please or to participate in this conversation.