Untitled

SELECT
    "ads_configs"."id",
    "ads_configs"."ads_id",
    "ads_configs"."office_id",
    "ads_configs"."type",
    "ads_configs"."project_id",
    "ads_configs"."agent_id",
    "ads_configs"."start_date",
    "ads_configs"."end_date",
    "ads_configs"."status",
    "ads_configs"."createdAt",
    "ads_configs"."updatedAt",
    "advertise"."id" AS "advertise.id",
    "advertise"."ads_id" AS "advertise.ads_id",
    "advertise"."office_id" AS "advertise.office_id",
    "advertise"."rental_type" AS "advertise.rental_type",
    "advertise"."project_id" AS "advertise.project_id",
    "advertise"."location_search" AS "advertise.location_search",
    "advertise"."analytic_views" AS "advertise.analytic_views",
    "advertise"."analytic_leads" AS "advertise.analytic_leads",
    "advertise"."analytic_favorite" AS "advertise.analytic_favorite",
    "advertise"."analytic_share" AS "advertise.analytic_share",
    "advertise"."agent_id" AS "advertise.agent_id",
    "advertise"."is_publish" AS "advertise.is_publish",
    "advertise"."province_id" AS "advertise.province_id",
    "advertise"."city_id" AS "advertise.city_id",
    "advertise"."district_id" AS "advertise.district_id",
    "advertise"."unit" AS "advertise.unit",
    "advertise"."location" AS "advertise.location",
    "advertise"."lat" AS "advertise.lat",
    "advertise"."long" AS "advertise.long",
    "advertise"."zip" AS "advertise.zip",
    "advertise"."regency" AS "advertise.regency",
    "advertise"."subdistrict" AS "advertise.subdistrict",
    "advertise"."province" AS "advertise.province",
    "advertise"."property_type" AS "advertise.property_type",
    "advertise"."ownership_certificate" AS "advertise.ownership_certificate",
    "advertise"."listing_type" AS "advertise.listing_type",
    "advertise"."name" AS "advertise.name",
    "advertise"."desc" AS "advertise.desc",
    "advertise"."price" AS "advertise.price",
    "advertise"."building_type_id" AS "advertise.building_type_id",
    "advertise"."size_land" AS "advertise.size_land",
    "advertise"."size_building" AS "advertise.size_building",
    "advertise"."rooms" AS "advertise.rooms",
    "advertise"."bathroom" AS "advertise.bathroom",
    "advertise"."carport" AS "advertise.carport",
    "advertise"."garage" AS "advertise.garage",
    "advertise"."electricity" AS "advertise.electricity",
    "advertise"."highlight_id" AS "advertise.highlight_id",
    "advertise"."report_desc" AS "advertise.report_desc",
    "advertise"."user_report" AS "advertise.user_report",
    "advertise"."is_highlights" AS "advertise.is_highlights",
    "advertise"."reason" AS "advertise.reason",
    "advertise"."score" AS "advertise.score",
    "advertise"."logs" AS "advertise.logs",
    "advertise"."deletedAt" AS "advertise.deletedAt",
    "advertise"."exp_heighlight" AS "advertise.exp_heighlight",
    "advertise"."youtube" AS "advertise.youtube",
    "advertise"."matterport" AS "advertise.matterport",
    "advertise"."slug" AS "advertise.slug",
    "advertise"."meta_desc" AS "advertise.meta_desc",
    "advertise"."canonical" AS "advertise.canonical",
    "advertise"."createdAt" AS "advertise.createdAt",
    "advertise"."updatedAt" AS "advertise.updatedAt",
    "agent"."id" AS "agent.id",
    "agent"."office_id" AS "agent.office_id",
    "agent"."building_type_id" AS "agent.building_type_id",
    "agent"."office" AS "agent.office",
    "agent"."duration_qouta" AS "agent.duration_qouta",
    "agent"."pic_name" AS "agent.pic_name",
    "agent"."image" AS "agent.image",
    "agent"."phone" AS "agent.phone",
    "agent"."email" AS "agent.email",
    "agent"."city" AS "agent.city",
    "agent"."province" AS "agent.province",
    "agent"."district" AS "agent.district",
    "agent"."is_priority" AS "agent.is_priority",
    "agent"."desc" AS "agent.desc",
    "agent"."type_agent" AS "agent.type_agent",
    "agent"."quota" AS "agent.quota",
    "agent"."order_quota" AS "agent.order_quota",
    "agent"."user_id" AS "agent.user_id",
    "agent"."is_active" AS "agent.is_active",
    "agent"."is_trusted" AS "agent.is_trusted",
    "agent"."is_spesialis" AS "agent.is_spesialis",
    "agent"."is_gold_seller" AS "agent.is_gold_seller",
    "agent"."reason" AS "agent.reason",
    "agent"."link" AS "agent.link",
    "agent"."twitter" AS "agent.twitter",
    "agent"."ig" AS "agent.ig",
    "agent"."nib" AS "agent.nib",
    "agent"."yt" AS "agent.yt",
    "agent"."fb" AS "agent.fb",
    "agent"."tot_listing" AS "agent.tot_listing",
    "agent"."tot_sell" AS "agent.tot_sell",
    "agent"."tot_rent" AS "agent.tot_rent",
    "agent"."price_min" AS "agent.price_min",
    "agent"."price_end" AS "agent.price_end",
    "agent"."price_avg" AS "agent.price_avg",
    "agent"."logs" AS "agent.logs",
    "agent"."createdAt" AS "agent.createdAt",
    "agent"."updatedAt" AS "agent.updatedAt",
    (SELECT CASE WHEN COUNT(*) > 0 THEN true ELSE false END
     FROM favorites
     WHERE agent_id = "agent"."id" AND user_id = undefined
    ) AS "agent.is_favorite",
    "project"."id" AS "project.id",
    "project"."park" AS "project.park",
    "project"."office_id" AS "project.office_id",
    "project"."agent_id" AS "project.agent_id",
    "project"."swimming_pool" AS "project.swimming_pool",
    "project"."gym" AS "project.gym",
    "project"."banner" AS "project.banner",
    "project"."security" AS "project.security",
    "project"."image_park" AS "project.image_park",
    "project"."image_gym" AS "project.image_gym",
    "project"."image_swimming_pool" AS "project.image_swimming_pool",
    "project"."image_security" AS "project.image_security",
    "project"."lat" AS "project.lat",
    "project"."long" AS "project.long",
    "project"."price" AS "project.price",
    "project"."name" AS "project.name",
    "project"."subdistrict" AS "project.subdistrict",
    "project"."province" AS "project.province",
    "project"."city" AS "project.city",
    "project"."desc" AS "project.desc",
    "project"."building_type_id" AS "project.building_type_id",
    "project"."certificate" AS "project.certificate",
    "project"."unit_available" AS "project.unit_available",
    "project"."land" AS "project.land",
    "project"."size_building" AS "project.size_building",
    "project"."building_date" AS "project.building_date",
    "project"."file" AS "project.file",
    "project"."is_active" AS "project.is_active",
    "project"."video" AS "project.video",
    "project"."deletedAt" AS "project.deletedAt",
    "project"."slug" AS "project.slug",
    "project"."meta_desc" AS "project.meta_desc",
    "project"."canonical" AS "project.canonical",
    "project"."province_id" AS "project.province_id",
    "project"."city_id" AS "project.city_id",
    "project"."district_id" AS "project.district_id",
    "project"."createdAt" AS "project.createdAt",
    "project"."updatedAt" AS "project.updatedAt",
    (SELECT image
     FROM advertises_media
     WHERE advertises_media.project_id = "project"."id"
       AND advertises_media.is_cover = true
     LIMIT 1
    ) AS "project.image",
    "project->office_agent"."id" AS "project.office_agent.id",
    "project->office_agent"."name" AS "project.office_agent.name",
    "project->office_agent"."nib" AS "project.office_agent.nib",
    "project->office_agent"."phone" AS "project.office_agent.phone",
    "project->office_agent"."address" AS "project.office_agent.address",
    "project->office_agent"."is_active" AS "project.office_agent.is_active",
    "project->office_agent"."province" AS "project.office_agent.province",
    "project->office_agent"."city" AS "project.office_agent.city",
    "project->office_agent"."district" AS "project.office_agent.district",
    "project->office_agent"."subdistrict" AS "project.office_agent.subdistrict",
    "project->office_agent"."user_id" AS "project.office_agent.user_id",
    "project->office_agent"."createdAt" AS "project.office_agent.createdAt",
    "project->office_agent"."updatedAt" AS "project.office_agent.updatedAt"
FROM
    ads_configs
JOIN advertise ON "advertise"."ads_id" = "ads_configs"."ads_id"
JOIN agent ON "advertise"."agent_id" = "agent"."id"
JOIN project ON "advertise"."project_id" = "project"."id"
LEFT JOIN office AS "project->office_agent" ON "project->office_agent"."id" = "project"."office_id"
WHERE "ads_configs"."id" = $1;