What we have now:
- The content page in admin is used to reorder all scheduled offers (regardless of which scheduled country)
test-le-svc-offer-syd::DATABASE=> \d offer_brand
Table "public.offer_brand"
Column | Type | Collation | Nullable | Default
------------------------+-----------------------+-----------+----------+------------
fk_offer_salesforce_id | character varying(18) | | not null |
brand | text | | not null |
list_order | integer | | | 2147483647
SELECT DISTINCT
o.id_salesforce_external,
o.name,
b.list_order
FROM combined_offers_view o
LEFT OUTER JOIN offer_brand b ON
o.id_salesforce_external = b.fk_offer_salesforce_id AND
b.brand = :brand
INNER JOIN schedule s ON
o.id_salesforce_external = s.fk_offer_salesforce_id AND
s.brand = :brand AND
s.type = 'list_visibility' AND
s.start <= NOW() AND
s.end >= NOW()
INNER JOIN combined_packages_view p ON
o.id_salesforce_external = p.offer_id_salesforce_external AND
p.status = 'content-approved'
INNER JOIN packages_prices_view pp ON
p.id_salesforce_external = pp.fk_package_salesforce_external
WHERE
o.salesforce_record_type IN ('Escapes Approved', 'Tours Approved', 'Cruises Approved')
ORDER BY b.list_order NULLS LAST
LIMIT :limit
What we want:
- Reorder the content list per country
Suggested approach:
- Add a 'region' field to offer_brand
- Default this to 'world'
- Present 'world' as a country in terms of ordering (and it is the default display)
- Tweak the query to merge the world and non-world results - doing something like: Query from world, query from relevant country, merge results