Mysql: Order by boolean value mixed into the results 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
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.