Paginate problem with aggregate column

Hello,
Sorry for my english i'm french guy.

I would like to use an aggregated column (distance with cos and tan) to filter on the distance between two users with a having count, problem: I still get this error, can you help me?

mysqli_sql_exception # 1054
Unknown column 'distance' in 'where clause'

My select request is : $this->select('( 6371 * acos( cos( radians('.$this->escapeString($array['lat']).') ) * cos( radians( user_lat ) ) * cos( radians( user_lon ) - radians('.$this->escapeString($array['lon']).') ) + sin( radians('.$this->escapeString($array['lat']).') ) * sin(radians(user_lat)) ) ) as distance');

thank you for your help

@Wiloooo ,

Have you tried your SQL statement directly in MySQL? Also, can we see the code where the SQL statement is set up and submitted for execution? This will help us determine if your code and SQL statement is correctly set up/configured.

@Wiloooo ,

Have you tried your SQL statement directly in MySQL?  Also, can we see the code where the SQL statement is set up and submitted for execution?  This will help us determine if your code and SQL statement is correctly set up/configured.

Thank you for your answers,
yes my SQL request is good on my phpmyadmin

The problem comes from the autogenerated count for the pagination with "select *", my distance column is not in the table because it is an aggregated column

==> $this->select('( 6371 * acos( cos( radians('.$this->escapeString($array['lat']).') ) * cos( radians( user_lat ) ) * cos( radians( user_lon ) - radians('.$this->escapeString($array['lon']).') ) + sin( radians('.$this->escapeString($array['lat']).') ) * sin(radians(user_lat)) ) ) as distance');

I hope you understood me

@Wiloooo ,

Question...are you trying to get one value from your select statement? If that is the case, why even use Mysql. You could accomplish the same thing with PHP math.

mysqli_sql_exception # 1054
Unknown column 'distance' in 'where clause'

My select request is :  $this->select('( 6371 * acos( cos( radians('.$this->escapeString($array['lat']).') ) * cos( radians( user_lat ) ) * cos( radians( user_lon ) - radians('.$this->escapeString($array['lon']).') ) + sin( radians('.$this->escapeString($array['lat']).') ) * sin(radians(user_lat)) ) )  as distance');

What the whole query that CI4 sends to MySQL?

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

@Wiloooo ,

Question...are you trying to get one value from your select statement?  If that is the case, why even use Mysql.  You could accomplish the same thing with PHP math.

False, if I want to sort by distance with a given distance, php math is useless

mysqli_sql_exception # 1054
Unknown column 'distance' in 'where clause'

My select request is :  $this->select('( 6371 * acos( cos( radians('.$this->escapeString($array['lat']).') ) * cos( radians( user_lat ) ) * cos( radians( user_lon ) - radians('.$this->escapeString($array['lon']).') ) + sin( radians('.$this->escapeString($array['lat']).') ) * sin(radians(user_lat)) ) )  as distance');

What the whole query that CI4 sends to MySQL?

sorry i didnt understand

Try:

PHP Code:

$this->select('( 6371 * acos( cos( radians('.$this->escapeString($array['lat']).') ) * cos( radians( user_lat ) ) * cos( radians( user_lon ) - radians('.$this->escapeString($array['lon']).') ) + sin( radians('.$this->escapeString($array['lat']).') ) * sin(radians(user_lat)) ) ) as distance', false); 


ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper