Untitled
mysql
16 days ago
5.2 kB
1
Indexable
Never
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