How to pass a value returned from inner query to outer query

Question

I am using a nested query for fetching records within a certain radius. I want to order the response by distance and also pass the distance as one of the parameter in response. here is the query I a using for same.

select ofr.offerId,ofr.outlet_id,ofr.offer_title,ofr.offer_icon,ofr.offer_description,ofr.CategoryId,ofr.offer_terms,
    ofr.price_description,ofr.rating,ofr.isdeleted,ofr.minpoint_required,otl.shop_name,otl.shop_address,otl.shop_city,otl.shop_phone,otl.shop_icon,
    otl.shop_latitude,otl.shop_longitude,otl.shop_country,otl.shop_zip,distance
from pp.offers as ofr 
join pp.outlets as otl 
where ofr.outlet_id = otl.shop_id and   
MBRContains(GeomFromText(CONCAT('Polygon((',x1,' ', y1,',', x2,' ', y2,',', x3,' ', y3,',',x4,' ', y4,',', x1,' ', y1,'))')),otl.g)
        and match(offer_title,offer_description) against(searchText) 
order by (
    SELECT  glength(LineStringFromWKB(LineString(GeomFromText(astext(PointFromWKB(POINT(latitude,longitude)))), GeomFromText(astext(PointFromWKB(POINT(otl.shop_latitude,otl.shop_longitude)))))))*100 
    AS distance)
    LIMIT 300

But when trying to execute this I am getting back an error

unknown field distance

How can I return the distance calculated in the inner query within the response of the sql query?

Thanks

Answer 1

You cannot define variables in a subquery in the order by and expect to use them anywhere else. If I understand correctly, put the expression in the select and then refer to it in the order by:

select ofr.offerId, ofr.outlet_id, ofr.offer_title, ofr.offer_icon, ofr.offer_description, 
       ofr.CategoryId, ofr.offer_terms,
       ofr.price_description, ofr.rating, ofr.isdeleted, ofr.minpoint_required, otl.shop_name,
       otl.shop_address, otl.shop_city, otl.shop_phone, otl.shop_icon,
       otl.shop_latitude, otl.shop_longitude, otl.shop_country, otl.shop_zip,
       (glength(LineStringFromWKB(LineString(GeomFromText(astext(PointFromWKB(POINT(latitude,longitude)))),
        GeomFromText(astext(PointFromWKB(POINT(otl.shop_latitude,otl.shop_longitude)))))))*100 
        AS distance
from pp.offers as ofr join
     pp.outlets as otl
     on ofr.outlet_id = otl.shop_id
where MBRContains(GeomFromText(CONCAT('Polygon((',x1,' ', y1,',', x2,' ', y2,',', x3,' ', y3,',',x4,' ', y4,',', x1,' ', y1,'))')),otl.g)
        and match(offer_title,offer_description) against(searchText) 

order by distance
LIMIT 300

Answer 2

Put your order by inner query in select statement itself, and refer it within orderby. Order by column name must match select query column name.

As well as check from which table you are refering distance filed and put alias name of the table in front of distance.

select ofr.offerId, ofr.outlet_id, ofr.offer_title, ofr.offer_icon, ofr.offer_description, 
       ofr.CategoryId, ofr.offer_terms,ofr.price_description, ofr.rating, ofr.isdeleted, ofr.minpoint_required, otl.shop_name,otl.shop_address, otl.shop_city, otl.shop_phone, otl.shop_icon,
       otl.shop_latitude, otl.shop_longitude, otl.shop_country, otl.shop_zip,
       (glength(LineStringFromWKB(LineString(GeomFromText(astext(PointFromWKB(POINT(latitude,longitude)))),GeomFromText(astext(PointFromWKB(POINT(otl.shop_latitude,otl.shop_longitude)))))))*100 
        AS distance
from pp.offers as ofr join
     pp.outlets as otl
     on ofr.outlet_id = otl.shop_id
where MBRContains(GeomFromText(CONCAT('Polygon((',x1,' ', y1,',', x2,' ', y2,',', x3,' ', y3,',',x4,' ', y4,',', x1,' ', y1,'))')),otl.g)
        and match(offer_title,offer_description) against(searchText) order by distance LIMIT 300