DELIMITER $$

CREATE PROCEDURE GetProjectsWithVisitors()
BEGIN
SELECT 
`projects`.`id`, 
`projects`.`uuid`, 
`projects`.`refno`, 
`projects`.`defaultimg`, 
`projects`.`buildername`, 
`projects`.`city`, 
`projects`.`descriptproject`, 
`projects`.`cities_id`, 
`projects`.`landarea`, 
`projects`.`landunits_id`, 
`projects`.`latitude`, 
`projects`.`longitude`, 
`projects`.`pname`, 
`projects`.`postedby`, 
`projects`.`totalrating`, 
`projects`.`states_id`, 
`projects`.`districts_id`, 
`projects`.`users_id`, 
`projects`.`rera`, 
`projects`.`contactdetails`, 
`projects`.`lunchingdate`, 
`projects`.`officeaddress`, 
`projects`.`address_type`, 
`projects`.`videourl`, 
`projects`.`websiteurl`, 
`projects`.`telephonicno`, 
`projects`.`isverified`, 
`projects`.`status`, 
`projects`.`promoted`, 
`projects`.`created_at`, 
`projects`.`modified_at`, 
(3956 * 2 * ASIN(SQRT( POWER(SIN(( 0 - projects.latitude) *  pi()/180 / 2), 
2) + COS( 0 * pi()/180) * COS(projects.latitude * pi()/180) * POWER(SIN(( 0 - projects.longitude) * pi()/180 / 2), 
2) ))) AS `distance`, 
`like`.`id` AS `like.id`, 
`like`.`refno` AS `like.refno`, 
`like`.`refname` AS `like.refname`, 
`like`.`name` AS `like.name`, 
`like`.`email` AS `like.email`, 
`like`.`mobile` AS `like.mobile`, 
`like`.`loginas` AS `like.loginas`, 
`like`.`status` AS `like.status`, 
`like`.`created_at` AS `like.created_at`, 
`like`.`modified_at` AS `like.modified_at`, 
`like->projects_users`.`createdAt` AS `like.projects_users.createdAt`, 
`like->projects_users`.`updatedAt` AS `like.projects_users.updatedAt`, 
`like->projects_users`.`projects_id` AS `like.projects_users.projects_id`, 
`like->projects_users`.`users_id` AS `like.projects_users.users_id`, 
`visitor`.`id` AS `visitor.id`, 
`visitor`.`refno` AS `visitor.refno`, 
`visitor`.`refname` AS `visitor.refname`, 
`visitor`.`name` AS `visitor.name`, 
`visitor`.`email` AS `visitor.email`, 
`visitor`.`mobile` AS `visitor.mobile`, 
`visitor`.`loginas` AS `visitor.loginas`, 
`visitor`.`status` AS `visitor.status`, 
`visitor`.`created_at` AS `visitor.created_at`, 
`visitor`.`modified_at` AS `visitor.modified_at`, 
`visitor->userhistories`.`id` AS `visitor.userhistories.id`, 
`visitor->userhistories`.`users_id` AS `visitor.userhistories.users_id`, 
`visitor->userhistories`.`userhistorable_id` AS `visitor.userhistories.userhistorable_id`, 
`visitor->userhistories`.`userhistorable_type` AS `visitor.userhistories.userhistorable_type`, 
`visitor->userhistories`.`status` AS `visitor.userhistories.status`, 
`visitor->userhistories`.`created_at` AS `visitor.userhistories.created_at`, 
`visitor->userhistories`.`modified_at` AS `visitor.userhistories.modified_at`, 
`visitors`.`id` AS `visitors.id`, 
`visitors`.`refno` AS `visitors.refno`, 
`visitors`.`refname` AS `visitors.refname`, 
`visitors`.`name` AS `visitors.name`, 
`visitors`.`email` AS `visitors.email`, 
`visitors`.`mobile` AS `visitors.mobile`, 
`visitors`.`loginas` AS `visitors.loginas`, 
`visitors`.`status` AS `visitors.status`, 
`visitors`.`created_at` AS `visitors.created_at`, 
`visitors`.`modified_at` AS `visitors.modified_at`, 
`visitors->userhistories`.`id` AS `visitors.userhistories.id`, 
`visitors->userhistories`.`users_id` AS `visitors.userhistories.users_id`, 
`visitors->userhistories`.`userhistorable_id` AS `visitors.userhistories.userhistorable_id`, 
`visitors->userhistories`.`userhistorable_type` AS `visitors.userhistories.userhistorable_type`, 
`visitors->userhistories`.`status` AS `visitors.userhistories.status`, 
`visitors->userhistories`.`created_at` AS `visitors.userhistories.created_at`, 
`visitors->userhistories`.`modified_at` AS `visitors.userhistories.modified_at`, 
`reviews`.`id` AS `reviews.id`, 
`reviews`.`users_id` AS `reviews.users_id`, 
`reviews`.`reviewable_id` AS `reviews.reviewable_id`, 
`reviews`.`reviewable_type` AS `reviews.reviewable_type`, 
`reviews`.`rate` AS `reviews.rate`, 
`reviews`.`review` AS `reviews.review`, 
`reviews`.`created_at` AS `reviews.created_at`, 
`reviews`.`modified_at` AS `reviews.modified_at`, 
`users`.`id` AS `users.id`, 
`users`.`refno` AS `users.refno`, 
`users`.`refname` AS `users.refname`, 
`users`.`name` AS `users.name`, 
`users`.`email` AS `users.email`, 
`users`.`mobile` AS `users.mobile`, 
`users`.`loginas` AS `users.loginas`, 
`users`.`status` AS `users.status`, 
`users`.`created_at` AS `users.created_at`, 
`users`.`modified_at` AS `users.modified_at`, 
`users->projects_users`.`createdAt` AS `users.projects_users.createdAt`, 
`users->projects_users`.`updatedAt` AS `users.projects_users.updatedAt`, 
`users->projects_users`.`projects_id` AS `users.projects_users.projects_id`, 
`users->projects_users`.`users_id` AS `users.projects_users.users_id`, 
`states`.`id` AS `states.id`, 
`states`.`name` AS `states.name`, 
`states`.`status` AS `states.status`, 
`states`.`created_at` AS `states.created_at`, 
`states`.`modified_at` AS `states.modified_at`, 
`districts`.`id` AS `districts.id`, 
`districts`.`name` AS `districts.name`, 
`districts`.`states_id` AS `districts.states_id`, 
`districts`.`latitude` AS `districts.latitude`, 
`districts`.`longitude` AS `districts.longitude`, 
`districts`.`status` AS `districts.status`, 
`districts`.`created_at` AS `districts.created_at`, 
`districts`.`modified_at` AS `districts.modified_at`, 
`photos`.`id` AS `photos.id`, 
`photos`.`uuid` AS `photos.uuid`, 
`photos`.`assetable_id` AS `photos.assetable_id`, 
`photos`.`assetable_type` AS `photos.assetable_type`, 
`photos`.`location` AS `photos.location`, 
`photos`.`ext` AS `photos.ext`, 
`photos`.`mimetype` AS `photos.mimetype`, 
`photos`.`label` AS `photos.label`, 
`photos`.`type` AS `photos.type`, 
`photos`.`status` AS `photos.status`, 
`photos`.`created_at` AS `photos.created_at`, 
`photos`.`modified_at` AS `photos.modified_at`, 
`brochures`.`id` AS `brochures.id`, 
`brochures`.`uuid` AS `brochures.uuid`, 
`brochures`.`assetable_id` AS `brochures.assetable_id`, 
`brochures`.`assetable_type` AS `brochures.assetable_type`, 
`brochures`.`location` AS `brochures.location`, 
`brochures`.`ext` AS `brochures.ext`, 
`brochures`.`mimetype` AS `brochures.mimetype`, 
`brochures`.`label` AS `brochures.label`, 
`brochures`.`type` AS `brochures.type`, 
`brochures`.`status` AS `brochures.status`, 
`brochures`.`created_at` AS `brochures.created_at`, 
`brochures`.`modified_at` AS `brochures.modified_at`, 
`phones`.`id` AS `phones.id`, 
`phones`.`phonable_id` AS `phones.phonable_id`, 
`phones`.`phonable_type` AS `phones.phonable_type`, 
`phones`.`phone` AS `phones.phone`, 
`phones`.`name` AS `phones.name`, 
`phones`.`created_at` AS `phones.created_at`, 
`phones`.`modified_at` AS `phones.modified_at`, 
`documents`.`id` AS `documents.id`, 
`documents`.`uuid` AS `documents.uuid`, 
`documents`.`assetable_id` AS `documents.assetable_id`, 
`documents`.`assetable_type` AS `documents.assetable_type`, 
`documents`.`location` AS `documents.location`, 
`documents`.`ext` AS `documents.ext`, 
`documents`.`mimetype` AS `documents.mimetype`, 
`documents`.`label` AS `documents.label`, 
`documents`.`type` AS `documents.type`, 
`documents`.`status` AS `documents.status`, 
`documents`.`created_at` AS `documents.created_at`, 
`documents`.`modified_at` AS `documents.modified_at`, 
`amenitiesprojectsmasters`.`id` AS `amenitiesprojectsmasters.id`, 
`amenitiesprojectsmasters`.`name` AS `amenitiesprojectsmasters.name`, 
`amenitiesprojectsmasters`.`status` AS `amenitiesprojectsmasters.status`, 
`amenitiesprojectsmasters`.`created_at` AS `amenitiesprojectsmasters.created_at`, 
`amenitiesprojectsmasters`.`modified_at` AS `amenitiesprojectsmasters.modified_at`, 
`amenitiesprojectsmasters->amenitiesprojectsmasters_projects`.`createdAt` AS `amenitiesprojectsmasters.amenitiesprojectsmasters_projects.createdAt`, 
`amenitiesprojectsmasters->amenitiesprojectsmasters_projects`.`updatedAt` AS `amenitiesprojectsmasters.amenitiesprojectsmasters_projects.updatedAt`, 
`amenitiesprojectsmasters->amenitiesprojectsmasters_projects`.`projects_id` AS `amenitiesprojectsmasters.amenitiesprojectsmasters_projects.projects_id`, 
`amenitiesprojectsmasters->amenitiesprojectsmasters_projects`.`amenitiesprojectsmasters_id` AS `amenitiesprojectsmasters.amenitiesprojectsmasters_projects.amenitiesprojectsmasters_id`, 
`cities`.`id` AS `cities.id`, 
`cities`.`name` AS `cities.name`, 
`cities`.`districts_id` AS `cities.districts_id`, 
`cities`.`status` AS `cities.status`, 
`cities`.`created_at` AS `cities.created_at`, 
`cities`.`modified_at` AS `cities.modified_at`, 
`owner`.`id` AS `owner.id`, 
`owner`.`refno` AS `owner.refno`, 
`owner`.`refname` AS `owner.refname`, 
`owner`.`name` AS `owner.name`, 
`owner`.`email` AS `owner.email`, 
`owner`.`mobile` AS `owner.mobile`, 
`owner`.`loginas` AS `owner.loginas`, 
`owner`.`status` AS `owner.status`, 
`owner`.`created_at` AS `owner.created_at`, 
`owner`.`modified_at` AS `owner.modified_at`, 
`landunits`.`id` AS `landunits.id`, 
`landunits`.`name` AS `landunits.name`, 
`landunits`.`slug` AS `landunits.slug`, 
`landunits`.`status` AS `landunits.status`, 
`landunits`.`created_at` AS `landunits.created_at`, 
`landunits`.`modified_at` AS `landunits.modified_at`, 
`buildings`.`id` AS `buildings.id`, 
`buildings`.`projects_id` AS `buildings.projects_id`, 
`buildings`.`projecttypes_id` AS `buildings.projecttypes_id`, 
`buildings`.`descriptphase` AS `buildings.descriptphase`, 
`buildings`.`buildingname` AS `buildings.buildingname`, 
`buildings`.`expecteddate` AS `buildings.expecteddate`, 
`buildings`.`possession` AS `buildings.possession`, 
`buildings`.`landarea` AS `buildings.landarea`, 
`buildings`.`landunits_id` AS `buildings.landunits_id`, 
`buildings`.`totalfloors` AS `buildings.totalfloors`, 
`buildings`.`created_at` AS `buildings.created_at`, 
`buildings`.`modified_at` AS `buildings.modified_at`, 
`buildings->flats`.`id` AS `buildings.flats.id`, 
`buildings->flats`.`buildings_id` AS `buildings.flats.buildings_id`, 
`buildings->flats`.`balance` AS `buildings.flats.balance`, 
`buildings->flats`.`balanceunit` AS `buildings.flats.balanceunit`, 
`buildings->flats`.`buildup` AS `buildings.flats.buildup`, 
`buildings->flats`.`builduparea` AS `buildings.flats.builduparea`, 
`buildings->flats`.`buildupunits_id` AS `buildings.flats.buildupunits_id`, 
`buildings->flats`.`carpet` AS `buildings.flats.carpet`, 
`buildings->flats`.`carpetarea` AS `buildings.flats.carpetarea`, 
`buildings->flats`.`carpetunits_id` AS `buildings.flats.carpetunits_id`, 
`buildings->flats`.`descriptphase` AS `buildings.flats.descriptphase`, 
`buildings->flats`.`forsell` AS `buildings.flats.forsell`, 
`buildings->flats`.`forsellunit` AS `buildings.flats.forsellunit`, 
`buildings->flats`.`per_price` AS `buildings.flats.per_price`, 
`buildings->flats`.`plot` AS `buildings.flats.plot`, 
`buildings->flats`.`plotarea` AS `buildings.flats.plotarea`, 
`buildings->flats`.`plotunits_id` AS `buildings.flats.plotunits_id`, 
`buildings->flats`.`price` AS `buildings.flats.price`, 
`buildings->flats`.`pricecurrencies_id` AS `buildings.flats.pricecurrencies_id`, 
`buildings->flats`.`rateprunits_id` AS `buildings.flats.rateprunits_id`, 
`buildings->flats`.`kindofprojecttypes_id` AS `buildings.flats.kindofprojecttypes_id`, 
`buildings->flats`.`supperup` AS `buildings.flats.supperup`, 
`buildings->flats`.`supperuparea` AS `buildings.flats.supperuparea`, 
`buildings->flats`.`supperupunits_id` AS `buildings.flats.supperupunits_id`, 
`buildings->flats`.`totalrooms` AS `buildings.flats.totalrooms`, 
`buildings->flats`.`created_at` AS `buildings.flats.created_at`, 
`buildings->flats`.`modified_at` AS `buildings.flats.modified_at`, 
(CASE
                                    WHEN plot = 1 and plotarea IS NOT NULL and plotunits_id = 2 THEN CAST(plotarea AS UNSIGNED)
                                    WHEN plot = 1 and plotarea IS NOT NULL and plotunits_id = 3 THEN CAST(plotarea AS UNSIGNED) * 10.7639
                                    WHEN plot = 1 and plotarea IS NOT NULL and plotunits_id = 4 THEN CAST(plotarea AS UNSIGNED) * 43560
                                    WHEN plot = 1 and plotarea IS NOT NULL and plotunits_id = 5 THEN CAST(plotarea AS UNSIGNED) * 107639
                                    WHEN supperup = 1 and supperuparea IS NOT NULL and supperupunits_id = 2 THEN CAST(supperuparea AS UNSIGNED)
                                    WHEN supperup = 1 and supperuparea IS NOT NULL and supperupunits_id = 3 THEN CAST(supperuparea AS UNSIGNED) * 10.7639
                                    WHEN supperup = 1 and supperuparea IS NOT NULL and supperupunits_id = 4 THEN CAST(supperuparea AS UNSIGNED) * 43560
                                    WHEN supperup = 1 and supperuparea IS NOT NULL and supperupunits_id = 5 THEN CAST(supperuparea AS UNSIGNED) * 107639
                                    WHEN buildup = 1 and builduparea IS NOT NULL and buildupunits_id = 2 THEN CAST(builduparea AS UNSIGNED)
                                    WHEN buildup = 1 and builduparea IS NOT NULL and buildupunits_id = 3 THEN CAST(builduparea AS UNSIGNED) * 10.7639
                                    WHEN buildup = 1 and builduparea IS NOT NULL and buildupunits_id = 4 THEN CAST(builduparea AS UNSIGNED) * 43560
                                    WHEN buildup = 1 and builduparea IS NOT NULL and buildupunits_id = 5 THEN CAST(builduparea AS UNSIGNED) * 107639
                                    WHEN carpet = 1 and carpetarea IS NOT NULL and carpetunits_id = 2 THEN CAST(carpetarea AS UNSIGNED)
                                    WHEN carpet = 1 and carpetarea IS NOT NULL and carpetunits_id = 3 THEN CAST(carpetarea AS UNSIGNED) * 10.7639
                                    WHEN carpet = 1 and carpetarea IS NOT NULL and carpetunits_id = 4 THEN CAST(carpetarea AS UNSIGNED) * 43560
                                    WHEN carpet = 1 and carpetarea IS NOT NULL and carpetunits_id = 5 THEN CAST(carpetarea AS UNSIGNED) * 107639
                                    ELSE 0
                                    END) AS `buildings.flats.convertedarea`, 
                                    `buildings->flats->buildupunits`.`id` AS `buildings.flats.buildupunits.id`, 
                                    `buildings->flats->buildupunits`.`name` AS `buildings.flats.buildupunits.name`, 
                                    `buildings->flats->buildupunits`.`slug` AS `buildings.flats.buildupunits.slug`, 
                                    `buildings->flats->buildupunits`.`status` AS `buildings.flats.buildupunits.status`, 
                                    `buildings->flats->buildupunits`.`created_at` AS `buildings.flats.buildupunits.created_at`, 
                                    `buildings->flats->buildupunits`.`modified_at` AS `buildings.flats.buildupunits.modified_at`, 
                                    `buildings->flats->carpetunits`.`id` AS `buildings.flats.carpetunits.id`, 
                                    `buildings->flats->carpetunits`.`name` AS `buildings.flats.carpetunits.name`, 
                                    `buildings->flats->carpetunits`.`slug` AS `buildings.flats.carpetunits.slug`, 
                                    `buildings->flats->carpetunits`.`status` AS `buildings.flats.carpetunits.status`, 
                                    `buildings->flats->carpetunits`.`created_at` AS `buildings.flats.carpetunits.created_at`, 
                                    `buildings->flats->carpetunits`.`modified_at` AS `buildings.flats.carpetunits.modified_at`, 
                                    `buildings->flats->supperupunits`.`id` AS `buildings.flats.supperupunits.id`, 
                                    `buildings->flats->supperupunits`.`name` AS `buildings.flats.supperupunits.name`, 
                                    `buildings->flats->supperupunits`.`slug` AS `buildings.flats.supperupunits.slug`, 
                                    `buildings->flats->supperupunits`.`status` AS `buildings.flats.supperupunits.status`, 
                                    `buildings->flats->supperupunits`.`created_at` AS `buildings.flats.supperupunits.created_at`, 
                                    `buildings->flats->supperupunits`.`modified_at` AS `buildings.flats.supperupunits.modified_at`, 
                                    `buildings->flats->plotunits`.`id` AS `buildings.flats.plotunits.id`, 
                                    `buildings->flats->plotunits`.`name` AS `buildings.flats.plotunits.name`, 
                                    `buildings->flats->plotunits`.`slug` AS `buildings.flats.plotunits.slug`, 
                                    `buildings->flats->plotunits`.`status` AS `buildings.flats.plotunits.status`, 
                                    `buildings->flats->plotunits`.`created_at` AS `buildings.flats.plotunits.created_at`, 
                                    `buildings->flats->plotunits`.`modified_at` AS `buildings.flats.plotunits.modified_at`, 
                                    `buildings->flats->rateprunits`.`id` AS `buildings.flats.rateprunits.id`, 
                                    `buildings->flats->rateprunits`.`name` AS `buildings.flats.rateprunits.name`, 
                                    `buildings->flats->rateprunits`.`slug` AS `buildings.flats.rateprunits.slug`, 
                                    `buildings->flats->rateprunits`.`status` AS `buildings.flats.rateprunits.status`, 
                                    `buildings->flats->rateprunits`.`created_at` AS `buildings.flats.rateprunits.created_at`, 
                                    `buildings->flats->rateprunits`.`modified_at` AS `buildings.flats.rateprunits.modified_at`, 
                                    `buildings->flats->kindofprojecttypes`.`id` AS `buildings.flats.kindofprojecttypes.id`, 
                                    `buildings->flats->kindofprojecttypes`.`projecttypes_id` AS `buildings.flats.kindofprojecttypes.projecttypes_id`, 
                                    `buildings->flats->kindofprojecttypes`.`name` AS `buildings.flats.kindofprojecttypes.name`, 
                                    `buildings->flats->kindofprojecttypes`.`created_at` AS `buildings.flats.kindofprojecttypes.created_at`, 
                                    `buildings->flats->kindofprojecttypes`.`modified_at` AS `buildings.flats.kindofprojecttypes.modified_at`, 
                                    `buildings->projects`.`id` AS `buildings.projects.id`, 
                                    `buildings->projects`.`uuid` AS `buildings.projects.uuid`, 
                                    `buildings->projects`.`refno` AS `buildings.projects.refno`, 
                                    `buildings->projects`.`defaultimg` AS `buildings.projects.defaultimg`, 
                                    `buildings->projects`.`buildername` AS `buildings.projects.buildername`, 
                                    `buildings->projects`.`city` AS `buildings.projects.city`, 
                                    `buildings->projects`.`descriptproject` AS `buildings.projects.descriptproject`, 
                                    `buildings->projects`.`cities_id` AS `buildings.projects.cities_id`, 
                                    `buildings->projects`.`landarea` AS `buildings.projects.landarea`, 
                                    `buildings->projects`.`landunits_id` AS `buildings.projects.landunits_id`, 
                                    `buildings->projects`.`latitude` AS `buildings.projects.latitude`, 
                                    `buildings->projects`.`longitude` AS `buildings.projects.longitude`, 
                                    `buildings->projects`.`pname` AS `buildings.projects.pname`, 
                                    `buildings->projects`.`postedby` AS `buildings.projects.postedby`, 
                                    `buildings->projects`.`totalrating` AS `buildings.projects.totalrating`, 
                                    `buildings->projects`.`states_id` AS `buildings.projects.states_id`, 
                                    `buildings->projects`.`districts_id` AS `buildings.projects.districts_id`, 
                                    `buildings->projects`.`users_id` AS `buildings.projects.users_id`, 
                                    `buildings->projects`.`rera` AS `buildings.projects.rera`, 
                                    `buildings->projects`.`contactdetails` AS `buildings.projects.contactdetails`, 
                                    `buildings->projects`.`lunchingdate` AS `buildings.projects.lunchingdate`, 
                                    `buildings->projects`.`officeaddress` AS `buildings.projects.officeaddress`, 
                                    `buildings->projects`.`address_type` AS `buildings.projects.address_type`, 
                                    `buildings->projects`.`videourl` AS `buildings.projects.videourl`, 
                                    `buildings->projects`.`websiteurl` AS `buildings.projects.websiteurl`, 
                                    `buildings->projects`.`telephonicno` AS `buildings.projects.telephonicno`, 
                                    `buildings->projects`.`isverified` AS `buildings.projects.isverified`, 
                                    `buildings->projects`.`status` AS `buildings.projects.status`, 
                                    `buildings->projects`.`promoted` AS `buildings.projects.promoted`, 
                                    `buildings->projects`.`created_at` AS `buildings.projects.created_at`, 
                                    `buildings->projects`.`modified_at` AS `buildings.projects.modified_at`, 
                                    `buildings->landunits`.`id` AS `buildings.landunits.id`, 
                                    `buildings->landunits`.`name` AS `buildings.landunits.name`, 
                                    `buildings->landunits`.`slug` AS `buildings.landunits.slug`, 
                                    `buildings->landunits`.`status` AS `buildings.landunits.status`, 
                                    `buildings->landunits`.`created_at` AS `buildings.landunits.created_at`, 
                                    `buildings->landunits`.`modified_at` AS `buildings.landunits.modified_at` FROM `projects` AS `projects` LEFT OUTER JOIN ( `projects_users` AS `like->projects_users` INNER JOIN `users` AS `like` ON `like`.`id` = `like->projects_users`.`users_id`) ON `projects`.`id` = `like->projects_users`.`projects_id` AND `like`.`id` = 0 LEFT OUTER JOIN ( `userhistories` AS `visitor->userhistories` INNER JOIN `users` AS `visitor` ON `visitor`.`id` = `visitor->userhistories`.`users_id` AND `visitor->userhistories`.`userhistorable_type` = 'projects') ON `projects`.`id` = `visitor->userhistories`.`userhistorable_id` AND `visitor`.`id` = 0 LEFT OUTER JOIN ( `userhistories` AS `visitors->userhistories` INNER JOIN `users` AS `visitors` ON `visitors`.`id` = `visitors->userhistories`.`users_id` AND `visitors->userhistories`.`userhistorable_type` = 'projects' AND `visitors->userhistories`.`status` = 'active') ON `projects`.`id` = `visitors->userhistories`.`userhistorable_id` LEFT OUTER JOIN `reviews` AS `reviews` ON `projects`.`id` = `reviews`.`reviewable_id` AND `reviews`.`reviewable_type` = 'projects' LEFT OUTER JOIN ( `projects_users` AS `users->projects_users` INNER JOIN `users` AS `users` ON `users`.`id` = `users->projects_users`.`users_id`) ON `projects`.`id` = `users->projects_users`.`projects_id` LEFT OUTER JOIN `states` AS `states` ON `projects`.`states_id` = `states`.`id` LEFT OUTER JOIN `districts` AS `districts` ON `projects`.`districts_id` = `districts`.`id` LEFT OUTER JOIN `assets` AS `photos` ON `projects`.`id` = `photos`.`assetable_id` AND ((`photos`.`status` = 'active' OR (`photos`.`status` IN ('active', 
                                    'inactive') AND projects.users_id = 0)) AND (`photos`.`assetable_type` = 'projects' AND `photos`.`type` = 'photos')) LEFT OUTER JOIN `assets` AS `brochures` ON `projects`.`id` = `brochures`.`assetable_id` AND `brochures`.`assetable_type` = 'projects' AND `brochures`.`type` = 'brochures' LEFT OUTER JOIN `phones` AS `phones` ON `projects`.`id` = `phones`.`phonable_id` AND `phones`.`phonable_type` = 'projects' LEFT OUTER JOIN `assets` AS `documents` ON `projects`.`id` = `documents`.`assetable_id` AND `documents`.`assetable_type` = 'projects' AND `documents`.`type` = 'documents' LEFT OUTER JOIN ( `amenitiesprojectsmasters_projects` AS `amenitiesprojectsmasters->amenitiesprojectsmasters_projects` INNER JOIN `amenitiesprojectsmasters` AS `amenitiesprojectsmasters` ON `amenitiesprojectsmasters`.`id` = `amenitiesprojectsmasters->amenitiesprojectsmasters_projects`.`amenitiesprojectsmasters_id`) ON `projects`.`id` = `amenitiesprojectsmasters->amenitiesprojectsmasters_projects`.`projects_id` LEFT OUTER JOIN `cities` AS `cities` ON `projects`.`cities_id` = `cities`.`id` INNER JOIN `users` AS `owner` ON `projects`.`users_id` = `owner`.`id` AND `owner`.`status` = 'active' LEFT OUTER JOIN `areaunits` AS `landunits` ON `projects`.`landunits_id` = `landunits`.`id` LEFT OUTER JOIN `buildings` AS `buildings` ON `projects`.`id` = `buildings`.`projects_id` LEFT OUTER JOIN `flats` AS `buildings->flats` ON `buildings`.`id` = `buildings->flats`.`buildings_id` LEFT OUTER JOIN `areaunits` AS `buildings->flats->buildupunits` ON `buildings->flats`.`buildupunits_id` = `buildings->flats->buildupunits`.`id` LEFT OUTER JOIN `areaunits` AS `buildings->flats->carpetunits` ON `buildings->flats`.`carpetunits_id` = `buildings->flats->carpetunits`.`id` LEFT OUTER JOIN `areaunits` AS `buildings->flats->supperupunits` ON `buildings->flats`.`supperupunits_id` = `buildings->flats->supperupunits`.`id` LEFT OUTER JOIN `areaunits` AS `buildings->flats->plotunits` ON `buildings->flats`.`plotunits_id` = `buildings->flats->plotunits`.`id` LEFT OUTER JOIN `areaunits` AS `buildings->flats->rateprunits` ON `buildings->flats`.`rateprunits_id` = `buildings->flats->rateprunits`.`id` LEFT OUTER JOIN `kindofprojecttypes` AS `buildings->flats->kindofprojecttypes` ON `buildings->flats`.`kindofprojecttypes_id` = `buildings->flats->kindofprojecttypes`.`id` LEFT OUTER JOIN `projects` AS `buildings->projects` ON `buildings`.`projects_id` = `buildings->projects`.`id` LEFT OUTER JOIN `areaunits` AS `buildings->landunits` ON `buildings`.`landunits_id` = `buildings->landunits`.`id`;

END$$

DELIMITER ;
