Untitled

SELECT
  datediff(NOW(), a.ListingDate) AS DaysPosting,
  IFNULL( (
      3959 * acos(
        cos(radians(-6.2087634)) * cos(radians(a.Latitude)) * cos(
          radians(a.Longitude) - radians(106.845599)
        ) + sin(radians(-6.2087634)) * sin(radians(a.Latitude))
      )
    ),
    0
  ) AS distance,
  a.Bidlot,
  a.BidlotId,
  a.id,
  a.Latitude,
  a.Longitude,
  a.PropertyType,
  a.YearBuilt,
  '' AS Facility,
  '' AS PropertyName,
  '' AS CollageDistance,
  '' AS HospitalDistance,
  '' AS MallDistance,
  '' AS AirportDistance,
  '' AS UnitForSales,
  '' AS UnitSold,
  '' AS UnitForRent,
  '' AS UnitRented,
  a.Status,
  a.FullStreetAddress AS FullStreetAddress,
  b.name AS City,
  c.name AS State,
  d.name AS Country,
  m.name AS District,
  n.name AS SubDistrict,
  a.ZipCode,
  a.BidStatus,
  IF(
      a.Status = 'Rent',
      'RENT',
      UPPER(a.BidStatus)
  ) AS BidStatusUpper,
  IF(
      a.BidStatus = 'OpenBid' || a.BidStatus = 'TimeLapBid',
      a.BidMinimum,
      a.ListPrice
  ) AS ListPrice,
  a.BedroomsTotal,
  a.BathsTotal,
  a.ServiceBedroom,
  FLOOR(a.BuildingSize) AS BuildingSize,
  FLOOR(a.LotSize) AS LotSize,
  a.Floor,
  a.RoomNumber,
  i.currency_code AS Currency,
  i.lotsize AS SizeUnit,
  '' AS EstateTotal,
  a.Views, (
    IF( (
        SELECT count(id)
        FROM favorites
        WHERE
          UserId = ''
          AND EstateId = a.id
      ) > 0,
      'Yes',
      'No'
    )
  ) as isFavourite,
  IF(k.id, true, false) as isProject,
  a.Negotiable,
  a.MarketPrice,
  a.PrivateListing,
  a.Title,
  a.PublishDate,
  a.ListingStatus,
  a.Tenure,
  a.IsPintuitive,
  IF(
      a.Status = 'Rent',
      a.RentType,
      ''
  ) AS RentType,
  IF(
      a.Status = 'Rent',
      a.RentLength,
      ''
  ) AS RentLength,
  a.StreetNumber
FROM estates AS a
  LEFT JOIN cities AS b ON a.City = b.id
  LEFT JOIN states AS c ON b.state_id = c.id
  LEFT JOIN countries AS d ON c.country_id = d.id
  LEFT JOIN country_unit AS i on a.Country = i.id_country
  LEFT JOIN project_estate AS k ON k.EstateId = a.id
  LEFT JOIN project AS l ON k.ProjectId = l.id
  LEFT JOIN districts AS m ON a.District = m.id
  LEFT JOIN subdistricts AS n ON a.SubDistrict = n.id
WHERE
  a.Bidlot = 0
  AND IF(k.id, l.Status = 'Active', true)
  AND a.isDeleted = 0
  AND a.ListingStatus = 'Active'
  AND a.PrivateListing = 'No'

HAVING distance < 15 

UNION 

SELECT
  '' AS DaysPosting,
  IFNULL( (
      3959 * acos(
        cos(radians(-6.2087634)) * cos(radians(e.Latitude)) * cos(
          radians(e.Longitude) - radians(106.845599)
        ) + sin(radians(-6.2087634)) * sin(radians(e.Latitude))
      )
    ),
    0
  ) AS distance,
  1 AS Bidlot,
  e.BidlotId,
  '' AS id,
  e.Latitude,
  e.Longitude,
  '' AS PropertyType,
  '' AS YearBuilt,
  e.Facility,
  e.PropertyName,
  e.CollageDistance,
  e.HospitalDistance,
  e.MallDistance,
  e.AirportDistance,
  e.UnitForSales,
  e.UnitSold,
  e.UnitForRent,
  e.UnitRented,
  '' AS Status,
  e.FullAddress AS FullStreetAddress,
  f.name AS City,
  g.name AS State,
  h.name AS Country,
  o.name AS District,
  p.name AS SubDistrict,
  e.ZipCode,
  '' AS BidStatus,
  '' AS BidStatusUpper, (
    SELECT
      MIN(
        IF(
          BidStatus = 'OpenBid' || BidStatus = 'TimeLapBid',
          BidMinimum,
          ListPrice
        )
      )
    FROM estates
    WHERE
      Bidlot = '1'
      AND BidlotId = e.BidlotId
      AND isDeleted = 0
      AND ListingStatus = 'Active'
      AND PrivateListing = 'No'
  ) AS ListPrice,
  '' AS BedroomsTotal,
  '' AS BathsTotal,
  '' AS ServiceBedroom,
  '' AS BuildingSize,
  '' AS LotSize,
  '' AS Floor,
  '' AS RoomNumber,
  j.currency_code AS Currency,
  j.lotsize AS SizeUnit, (
    SELECT count(id)
    FROM estates
    WHERE
      Bidlot = '1'
      AND BidlotId = e.BidlotId
      AND isDeleted = 0
      AND ListingStatus = 'Active'
      AND PrivateListing = 'No'
  ) AS EstateTotal,
  '' AS Views,
  'No' AS isFavourite,
  IF(k.id, true, false) as isProject,
  '' AS Negotiable,
  '' AS MarketPrice,
  '' AS PrivateListing,
  '' AS Title,
  a.PublishDate,
  '' AS ListingStatus,
  '' AS Tenure,
  '' AS RentType,
  '' AS RentLength,
  e.StreetNumber,
  a.IsPintuitive
FROM estatelot AS e
  LEFT JOIN estates AS a ON e.BidlotId = a.BidlotId
  LEFT JOIN cities AS f ON e.City = f.id
  LEFT JOIN states AS g ON f.state_id = g.id
  LEFT JOIN countries AS h ON g.country_id = h.id
  LEFT JOIN country_unit AS j ON e.Country = j.id_country
  LEFT JOIN project_estate AS k ON k.EstateId = a.id
  LEFT JOIN project AS l ON k.ProjectId = l.id
  LEFT JOIN districts AS o ON e.District = o.id
  LEFT JOIN subdistricts AS p ON e.SubDistrict = p.id
WHERE
  a.Bidlot = 1
  AND IF(k.id, l.Status = 'Active', true)
  AND a.isDeleted = 0
  AND a.ListingStatus = 'Active'
  AND a.PrivateListing = 'No'

GROUP BY e.BidlotId
HAVING
  distance < 15
  AND EstateTotal > 0
ORDER BY
  isPintuitive ASC,
  distance ASC
LIMIT 10
OFFSET 0