Column not found even tho the column is returned.

[WildEgo]

Hey guys, I'm pretty new to using this package and I got a question, it might problem with my code but I'm getting a Column not found even tho the column was provided through a join.

Here's my code currently

$profiles = QueryBuilder::for(Profile::from('profiles as profiles')->select([
	'profiles.user_id',
	'profiles.slug',
	'profiles.avatar',
	'profiles.languages',
	'users.name',
	'users.servers',
	'users.state as rank',
	'winrate AS winrate',
	'order_count as orders',
])->whereNotNull('profiles.approved'))
	->join('users', 'profiles.user_id', 'users.id')
	->join(DB::raw('
		(select `booster`, ifnull(count(*), 0) as order_count
		from `orders`
		where `status` = 4 and `orders`.`deleted_at` is null
		group by `booster`) orders_count
	'), function ($join) {
		$join->on('profiles.user_id', '=', 'orders_count.booster');
	})
	->join(DB::raw('
		(select `booster`,
			cast(
			ifnull((
				count(
					case
					when `status` = "Win" then 1
					else null
					end
				) / (
					count(
					case
						when `status` = "Win" then 1
						else null
					end
					) + count(
					case
						when `status` = "Loss" then 1
						else null
					end
					)) * 100)
			, 0)
			as decimal(18, 2)) as winrate
		from `games`
		where `games`.`status` != "Remake" and `games`.`deleted_at` is null
		group by `booster`) booster_winrate
	'), function ($join) {
		$join->on('profiles.user_id', '=', 'booster_winrate.booster');
	})
	->allowedFilters([
		'name',
		AllowedFilter::callback('orders', function (Builder $query, $value) {
			$query->where('orders', '>=', $value);
		}),
		AllowedFilter::callback('winrate', function (Builder $query, $value) {
			$query->where('winrate', '>=', $value);
		}),
	])
	->allowedSorts('orders', 'winrate', 'rank')
	->paginate(9)
	->appends(request()->query());

It runs and retrieves everything perfectly when I request /boosters?filter[winrate]=80&sort=-orders, but when I call /boosters?filter[orders]=10&filter[winrate]=80&sort=-orders, it throws an exception saying the column wasn't found, orders follows the same logic as winrate so I don't really understand the reasoning for the exception to be thrown.

Again sorry if it's not the correct to place to post this.

Kind regards,
Luis Bizarro

[dominikb]

Can you post the stack trace and the generated SQL query? (Call ->toSql() on the QueryBuilder instance)

From the first view, I would assume it has to do with aliasing the column in the ->select() call, as there is a difference between winrate and orders columns.

[WildEgo]

You were right just tested it it was indeed column aliasing, I for some reason forgot I could call toSql on the query builder to test it, I already fixed it I can post the snippet of the fixed version if its needed for anyone else.

Thanks a lot Dominik!

[SDIjeremy]

@WildEgo Thank you thank you thank you!

I had exactly the same situation with WinRate and this helped sooooo much!