0

Imagine there is a job portal where people can post job offers

Some job offers are sponsored or premium offers that people pay for. These premium offers I want to display randomly in between search results.

Lets say a user searches for programmer

so I do

select * 
from jobs 
where description like '%programmer%'
order by created_at

is there a way in mysql to add in random job records that have is_premium = 1?

lets say, each 3 results, I want to ignore the search term, and add a random record with is_premium

2

To do so, the actual search results and the sponsored ads are sorted respectively.
Then, room for the sponsored ads needs to be made in the sequence of the actual search results.
Finally, they are put together, and displayed in defined order.

You asked for every third job offer to be a sponsored one:

WITH
SearchTerm AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY created_at) AS search_term_order
    , jobs.*
  FROM jobs
  WHERE description LIKE '%programmer%'
),
PremiumAdd AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY created_at DESC) AS premium_order
    , jobs.*
  FROM jobs
  WHERE is_premium = 1
),
FinalOrder AS (
  SELECT
    id
    , description
    , created_at
    , is_premium
    , MOD(search_term_order + 1, 2) + FLOOR((search_term_order + 1) / 2) * 3 - 2 AS position
  --  , MOD(search_term_order + 2, 3) + FLOOR((search_term_order + 2) / 3) * 4 - 3 AS position
  FROM SearchTerm
  UNION ALL
  SELECT
    id
    , description
    , created_at
    , is_premium
    , premium_order * 3
  --  , premium_order * 4
  FROM PremiumAdd
  ORDER BY position
)
SELECT
  id, description, created_at, is_premium FROM FinalOrder
;

The comments show the adjustments for having an ad in every fourth row…

N.B.:

  • Ads holding the search term would be listed twice. This could be handled by adjusting the WHERE clauses.
  • This requires MySQL 8.

Please comment, if and as this requires adjustment / further detail.

  • to be completely honest with you, I have never seen a notation like this before... I guess its a mysql 8 thing? whats the name of this notion? what else is cool in mysql 8? and/or what makes this require mysql 8? – Toskan Jan 11 at 2:48
  • It is possible that some records will be printed twice (when they are both premium and programmer-relative). If it is not safe you must check that the records which are selected by second CTE (PremiumAdd in above code) are absent in the first CTE (SearchTerm) output. And the order of first two CTEs is dependent by those records must be treated as premium or programmer-relative - if premium CTEs must be swapped. – Akina Jan 11 at 4:27
  • And I recommend to perform all calculations in first 2 CTEs - in above code it can be, for example, 2 * (ROW_NUMBER() OVER (ORDER BY created_at)) AS search_term_order for first CTE and 1 + 6 * (ROW_NUMBER() OVER (ORDER BY created_at DESC)) AS premium_order for the second one. – Akina Jan 11 at 4:33
  • @Toskan It's called Common Table Expression - and AFAIK became available with version 8. dev.mysql.com/doc/refman/8.0/en/with.html The same is true for ROW_NUMBER(). – Abecee Jan 11 at 8:36
  • @Akina You are right about potential double listings. But I did mention them under N.B. As for your second remark: In my CTEs, I tend to start with data collection, and do derive/calculate stuff later on. This is sure a matter of style/choice. (However, your suggested calculation doesn't seem to deliver the same result. Unfortunately, I didn't keep the test data - so I can't verify right away.) – Abecee Jan 11 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.