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 (11-30-2021, 08:37 AM)php_rocs Wrote: @Wiloooo , 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 (11-30-2021, 05:25 AM)Wiloooo Wrote: mysqli_sql_exception # 1054 What the whole query that CI4 sends to MySQL? (12-03-2021, 12:58 PM)php_rocs Wrote: @Wiloooo , False, if I want to sort by distance with a given distance, php math is useless (12-04-2021, 07:41 PM)kenjis Wrote:(11-30-2021, 05:25 AM)Wiloooo Wrote: mysqli_sql_exception # 1054 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); |
Welcome Guest, Not a member yet? Register Sign In |