I have a real estate website where people can search property based on Location, Property Type and Builder.
I have a Table like below.
CREATE TABLE Project(ProjectId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Location VARCHAR(255),
PropertyType VARCHAR(255),
Builder VARCHAR(255),
ProjectName VARCHAR(255),
Status TINYINT)
INSERT INTO Project(Location, PropertyType, Builder, ProjectName)
VALUES('Location A', 'Flats', 'Builder A', 'Project A', 1),
('Location A', 'Villas', 'Builder B', 'Project B', 1),
('Location B', 'Flats', 'Builder A', 'Project C', 1),
('Location C', 'Villas', 'Builder C', 'Project D', 1),
('Location B', 'Plots', 'Builder B', 'Project E', 1),
('Location A', 'Row House', 'Builder C', 'Project F', 1),
('Location A', 'Plots', 'Builder A', 'Project G', 1),
('Location C', 'Plots', 'Builder C', 'Project H', 1),
('Location C', 'Flats', 'Builder B', 'Project I', 1),
('Location C', 'Villas', 'Builder B', 'Project J', 1),
('Location A', 'Villas', 'Builder A', 'Project K', 1),
('Location C', 'Flats', 'Builder B', 'Project L', 1);
The search procedure which I use is in such a way that It brings back search result as per the parameters they selected i.e Property Type, Location, Builder Name
Now there may be projects for some search parameters as below
Flats by Builder A at Location C
I want to change procedure in such a way the parameters in filters in where clause should change to bring result all the time by considering other possibilities like for the above
Flats by Builder A at Location C
There is no Flats by Builder A at Location C so it should display Flats at Location C by Other Builders
Location C Flats Builder B Project I
The Filter should Consider PropertyType, Location and Builder Name
Villas at Location B
There is No Villas at Location B So
Location A Villas Builder B Project B
Location C Villas Builder B Project J
Location A Villas Builder A Project K
The search should happen by
PropertyType -> Location -> Builder
If there is No project for a particular Builder in that Location then
PropertyType -> Location
If there is No project for a particular Location then
PropertyType
The parameters in the filters should be eliminated in such a way it brings similar result to search param.
PropertyType -> Location -> Builder No Records
PropertyType -> Location No Records
PropertyType Records Found
Solution
DROP PROCEDURE IF EXISTS getProjectResult;
CREATE PROCEDURE getProjectResult(IN PropertyType VARCHAR(255), IN Location VARCHAR(255), IN BuilderName VARCHAR(255))
BEGIN
DROP TABLE IF EXISTS tblTempProjIds;
CREATE TEMPORARY TABLE tblTempProjIds(ProjId INT, Points INT);
SET @PropertyTypePoints = 0;
SET @LocationPoints = 0;
SET @BuilderName = 0;
IF PropertyType '' THEN
SET @PropertyTypePoints = 5;
END IF;
IF Location '' THEN
SET @LocationPoints = 3;
END IF;
IF BuilderName '' THEN
SET @BuilderName = 1;
END IF;
SET @BestScore = @PropertyTypePoints + @LocationPoints + @BuilderName;
SET @strSQL ='INSERT INTO tblTempProjIds(ProjId, Points) SELECT ProjectId,
CASE WHEN PropertyType= ? then 5 else 0 end +
CASE WHEN Location = ? then 3 else 0 end +
CASE WHEN Builder = ? then 1 else 0 end as score
FROM project
ORDER BY score DESC';
SET @PropertyType = PropertyType;
SET @Location = Location;
SET @BuilderName = BuilderName;
PREPARE stmt FROM @strSQL;
EXECUTE stmt USING @PropertyType, @Location, @BuilderName;
DEALLOCATE PREPARE stmt;
#SELECT * FROM tblTempProjIds;
SET @strSQLCount ='SELECT @Num := MAX(Points)
FROM tblTempProjIds';
PREPARE stmt FROM @strSQLCount;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
CASE @Num
WHEN 9 THEN SET @Where = CONCAT(' AND PropertyType="', PropertyType, '" AND Location="', Location, '" AND Builder="', BuilderName, '"');
WHEN 8 THEN SET @Where = CONCAT(' AND PropertyType="', PropertyType, '" AND Location="', Location, '"');
WHEN 6 THEN SET @Where = CONCAT(' AND PropertyType="', PropertyType, '" AND Builder ="', BuilderName, '"');
WHEN 5 THEN SET @Where = CONCAT(' AND PropertyType="', PropertyType, '"');
WHEN 4 THEN SET @Where = CONCAT(' AND Location="', Location, '" AND Builder ="', BuilderName, '"');
WHEN 3 THEN SET @Where = CONCAT(' AND Location="', Location, '"');
WHEN 1 THEN SET @Where = CONCAT(' AND Builder="', BuilderName, '"');
WHEN 0 THEN SET @Where = '';
END CASE;
SET @FinalSQL = CONCAT('SELECT *
FROM project
WHERE status = 1 ', @Where);
PREPARE stmt FROM @FinalSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;